Optimize the nested loop join

sqlworldwide 2016-04-27 02:47:42

Looking to optimize the Nested loop join between object9.index1 and Object6.index2.

SQLkiwi 2016-04-27 13:50:35
Answer updated.
SQLkiwi 2016-04-27 09:59:49
The properties of the Key Lookup are:


To eliminate this operator, you would need to add the columns shown in the Output List to the nonclustered index Object6.Index2 either as keys or in the include list.

You would also need the column(s) referenced in the expression ScalarString8.

Note that the cost percentages shown in execution plans are always estimates, and computed using the optimizer's cost model, which may not reflect the actual cost of executing the query on your specific hardware. The costs are useful internally so the optimizer can choose between strategies.

An important point to stress is that if the estimated row counts and sizes are inaccurate, so will the estimated costs be.

Plan fragment

In your case, the index seek on Object6.Index2 is estimated to produce 623 rows, but actually returns 741,178. This results in 741,178 Key Lookups, where only 623 were expected. If the optimizer had known so many lookups were necessary, it might well have chosen a different plan.

Adding just the columns billed and ddispdate (in that order) to the existing nonclustered index (after the existing key) may improve the estimates as well as performance – but the best solution is still to include the remaining columns from the lookup to eliminate it if that is practical.

The predicate on cinsplanname is also written in a way that does not help the optimizer or the execution engine. You should not use substring when testing the first few characters of a string. Simply writing cinsplanname >= @InsPlanFrom AND cinsplanname <= @InsPlanTo will be much better if there is an index on cinsplanname (with the needed includes of course). Though the cardinality estimate from the initial scan is acceptable, using substring likely means the optimizer has to guess at the distribution of values that will be selected, leading to a poor estimate on the Object6.Index2 seek. Get rid of the substring.

Once all that is done, you might like to test the query with the new cardinality estimator if estimates are still not reasonable. Try the query with OPTION (QUERYTRACEON 2312) to test the effect of this on the query in isolation. There is no guarantee it will be better.