Any suggestions on making this query run faster.

This runs in 2 1/2 minutes and is calling a view and joining to other tables. Need to get quicker if possible. I tried adding a non clustered index to the table included in the clustered index scan. Object4.Index1 but that did not help.

avatar image By Garry B 0 asked Sep 10, 2015 at 03:53 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

alt text

When creating the nonclustered index, you need to be sure it includes Column13 in the Output List, and also any columns referenced in ScalarString20. Ideally, the index would also be seekable, and provide a useful order for the following grouping operation, but it's impossible to be more specific about the details from an anonymized plan.

This table has 23,368,800 rows - filtered down to 478,708 by the ScalarString20 predicate, so a suitable nonclustered index should help. You should also look to remove the Key Lookup (again, be sure to check the predicate columns as well as the output list). Running the query directly from Plan Explorer rather than importing from SSMS would collect other useful performance information as well. There are many other issues with this plan, but that should get you started.

sp.png (33.9 kB)
avatar image By SQLkiwi ♦ 6.6k answered Sep 12, 2015 at 03:03 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x46

asked: Sep 10, 2015 at 03:53 PM

Seen: 67 times

Last Updated: Sep 12, 2015 at 03:03 AM