Home Forums SQL Server 2008 SQL Server 2008 - General Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help! RE: Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help!

  • Okay, I did two things that marginally improved performance.

    First, I researched the issue online and saw that "Key Lookups" are bad when it comes to execution plans. I guess you need to convert "predicates" (whatever those are) to "seek predicates" (whatever they are) to boost performance.

    Second, I eliminated "AND SVSales.DeleteFlg = 'N'" from my query, since that field is deprecated in the SVSales table, which means I eliminated DeleteFlg as a predicate (a good thing, it seems); I also created a non-unique, non-clustered index for SaleDate and rebuilt the index.

    After I regenerated the execution plan, the evil Key Lookup was gone. So I ran my sproc and noticed a 50% boost in performance. While that's good, it's not great considerning it was taking 8-12 minutes per day before, and now it's taking 2-6 minutes. Remember, we're supposed be down to a few seconds when all is said and done. Attached is the updated execution plan. Someone mentioned that I should create a "Covered Index" to boost performance even more, but I'm not sure which fields to include or whether that would really do anything. Any opinions?