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