Query plan interpretation - help needed 1

[takaoki]

Hi,

Just wanting some opinions... is it better to eliminate an index SCAN (cost is 98%) with an [index SEEK (cost 15%) + key lookup (cost 15%)]? I accomplished this by adding a DESC index on one of the key lookup date fields.

Logical reads also went from 30000+ to 3 by adding the sorted index on the date field.

Estimated I/O cost went from 25 to .003 as well.

Seems like it definitely improved things, but performance-wise there was no difference (not enough rows I guess to tell).

Side note:

It's confusing to me b/c the WHERE clause looks like:
WHERE EndDate = '2009-01-01'
AND StartDate = '2009-03-01'

but I only put the sorted index on "EndDate" not "StartDate". Putting an index on "StartDate" didn't seem to have any effect.

Thank you

 

[hmckillop]

In answer to your first question , Yes its better to eliminate the index scan.
You could potentially remove your Key lookup by adding the fields you have in the select statement to the index - this is a different discussion

Its likely why the second index on startdate didnt effect things was due to the amount of data being found on first index e.g. enddate.
If you have thousands of enddates the same then maybe the startdate index could be used.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[takaoki]

I actually improved the query further by simply rearranging the order of the cols in the PK, additional index wasn't needed... also got rid of the lookup cuz the PK is a covering index