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

TheGunners 2017-10-12 18:48:01

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

SDyckes2 2017-10-16 21:38:44
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.

TheGunners 2017-10-17 14:12:30
Thank you for your explanation is there a better way to rewrite it again
SDyckes2 2017-10-17 14:59:12
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.
TheGunners 2017-10-18 15:44:52
I tried the same way the same result