Help with query optimization

Is there anything I can do with this. It's a pretty big query. I'm concerned about the 21.6% Index Seek (Table23.Index54)

avatar image By Aircan 0 asked Oct 10, 2013 at 11:42 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

It runs in under a second. How much faster do you need it to be?

The index seek looks bad because the estimates are way off on that node. It was expecting 26k rows and actually had 349. The costs on the node are the estimates, even on an actual plan.

There are places where table2 is being scanned multiple times in the same branch but on different indexes. Perhaps on index that combines index20 and index46 by adding column45 would help. You find be able to do the same with table7's 53 and 43 indexes. Without the DDL i can't say how much that would help the read count, which is already low in my opinion.

avatar image By Dan Holmes 725 answered Oct 11, 2013 at 12:34 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Just a quick observation:

You have a whole bunch of clustered index scans on various tables, indicating that you have no useful index available on those tables that the query can make use of.

Regarding your concern, the index seek cost of 4.288 is likely much higher than its real cost (as it's pulling 349 rows, vs the 26k estimated). As Dan pointed out, it's an estimate only, it's not the actual cost. Perhaps updating statistics on this table might help with bringing the cost estimation closer to reality?

avatar image By Autochartist 16 answered Oct 11, 2013 at 01:27 PM
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
x109
x19

asked: Oct 10, 2013 at 11:42 PM

Seen: 316 times

Last Updated: Oct 11, 2013 at 01:27 PM