[dba-SQLServer] Covering Indexes - Q2

jwcolby jwcolby at colbyconsulting.com
Wed Nov 28 20:42:02 CST 2007


OK, so I have done a little reading on covering indexes.  Now, I have 7
"presence of adults AA to BB" fields where AA and BB are ages - 18-24, 25-34
etc.

I have many cases where the client asks for ("PresenceOfAdults_18_24 OR
PresenceOfAdults_25_34") AND NarrowIncomeBand IN('A','B','C','D') AND XXXX.
These are essentially two different select queries.

Possible strategies: 

1) TWO subqueries - 
	SELECT PKID FROM tblXXX WHERE (PresenceOfAdults_18_24 = 'Y' OR
PresenceOfAdults_25_34='Y')
	SELECT PKID FROM tblXXX WHERE NarrowIncomeBand IN('A','B','C','D')

Then JOIN the two queries on the PKID

2) One big query with the where clause handles both "subqueries".

If I want to use strategy 1, should I 

1) Create "cover queries" with the PKID and all of the PresenceOfAdults
fields in one cover query?  If so should the PKID be first or last field in
the index?
2) Create 7 different "cover queries" where the PKID and one
PresenceOfAdults field is combined.  If so should the PKID be first or last
field in the index?
3) Create 7 non cover queries containing just a single PresenceOfAdults
field.

To this point I have simply indexed each field that I want to do searches
on.  However I have situations where the client just asks for a single one
of these PresenceOfAdults fields and then other completely non-related
fields (NarrowIncomeBand, Gender, Waterski).  This stuff is taking a long
time to complete (30 minutes or even more).

I am having a tough time figuring out how to even test this stuff.  I have
not figured out the query analyzer though I am headed there.  Can I have two
different indexes using different strategies and look at the execution plan
to see whether one or the other or neither is used?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 




More information about the dba-SQLServer mailing list