Attack Key Lookups first?

bickiboy 2015-06-04 16:04:55

This is a really bad query – I know. But I'd like to know if anyones sees a better approach – when I look at this the first thing I want to do is eliminate the 24 Key lookups. Occasionally I've found in the past, spending hours on Covering indexes to eliminate those only improves the queries marginally and generally finding a bad or useless join can provide much bigger bang for your time spent.

Anyone have any suggestions after looking at this plan?

BTW – there are two cursors in this procedure that have been hard coded to skip just so I could get a plan. I'm trying to figure out how to eliminate those also.

SQLkiwi 2015-06-04 23:06:56
The query text is truncated (plans have a built-in limit on the length of the statement captured), and the query was run from SSMS rather than Plan Explorer, so no performance information (CPU, reads etc.) is available. That said, even if that information were available, the advice would probably be similar in general terms:

This process runs a massive query plan once for each row of the nested cursors. This is not a recipe for good performance. You are almost certainly correct in thinking that eliminating the lookups will not affect performance much.

If this were my query, I would probably start again, starting from the business requirements. I would write the process without cursors, and with the simplest (and most maintainable) set of SQL statements available. At least to begin with, this would probably involve multiple steps, of a manageable size, with intermediate results (between steps) held in temporary tables. I would also typically simplify the logic to the bare minimum to begin with, adding complexity (exceptions, edge cases etc.) step by step.

During this process, you should be able to identify exactly where the performance issues arise, and improve the schema or indexing appropriately. I would also look to reduce or eliminate the use of non-inline functions where practical. I would definitely look to avoid such large monolithic queries, even in the finished product – they're just too hard to maintain, and too often tend to have inconsistent performance characteristics.

I do apologise if you were looking for more specific advice, but with the time and information I have available, the above is about the best I can offer.