looking at execution plan the actual number of rows is lot smaller than estimated row and i updated all my statistics and i still see slow performance to select 372 rows any suggestion

looking at execution plan the actual number of rows is lot smaller than estimated row and i updated all my statistics and i still see slow performance to select 372 rows any suggestion

tmpD89F.pesession (12.6 kB)
avatar image By TheGunners 1 asked Oct 12 at 06:48 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

I would recommend you to take a look at the Cardinality of the indexes being used for each of the tables and/or use Plan Explorer, the Indexes Analysis tab, to look at the Histogram of the Index(es) being used. There is an Index Scan on all the indexes except the subquery.

I believe the WHERE Object6.Column3 NOT IN (SELECT Column3 FROM Database2.Schema1.Object5 WHERE Column3 IS NOT NULL) clause is generating the extremely large discrepancy between estimated and actual because the Object6.Column3 is part of a Left Outer Join. Use of the NOT IN with the subquery where a Column IS NOT NULL can skew the estimate, then it is further skewed by the Left Outer Join.

avatar image By SDyckes2 81 answered Oct 16 at 09:38 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Thank you for your explanation is there a better way to rewrite it again

avatar image By TheGunners 1 answered Oct 17 at 02:12 PM
more ▼
(comments are locked)
avatar image SDyckes2 Oct 17 at 02:59 PM

Before I jump in and work on a rewrite, I would look at the potential of adding the correct indexes to better address the requests from the query. If you collect an actual execution plan, you will have a better understanding of how the data is being returned and where the performance bottleneck(s) is/are. Utilize the Index Analysis tool (tab) in Plan Explorer to explore potential indexes.

avatar image TheGunners Oct 18 at 03:44 PM

I tried the same way the same result

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
x5

asked: Oct 12 at 06:48 PM

Seen: 43 times

Last Updated: Oct 18 at 03:44 PM