Asger Blond
ab-mi at post3.tele.dk
Tue Dec 15 13:34:00 CST 2009
Happy to know! Keep looking out though for table scans which might appear if the selectivity of your queries drops. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 15. december 2009 17:01 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] SPAM-LOW: Re: How to construct queries Asger, YOU DA MAN!!! > If you really want the optimizer to use index *searches* then you have to > build *a separate index for each field*. The optimizer can use these indexes > for searches and then join the results. This kind of execution is also > "covering" since SQL Server can find all values needed in the indexes and > don't have to go to the actual data pages. But in this case it certainly > matters how selective your condition is. I built a stored procedure to go build individual indexes on each field of the table from hell (~550 fields). On a query which returns a count of 13 million+ records using an or on 20 different fields, the execution plan shows that the query is in fact using index seeks now instead of index scans, and the time dropped from 1:58 to 0:29, from almost two minutes to under 1/2 minute!!! Awesome. Of course I have a total data space of about 40 gbytes and an index space of about 114 gigs, and that is for about 200 indexes, I still have about 300+ indexes to go. So this puppy is going to be large!!! However the upside is that I will have every single field indexed when I am done and not have to worry about "do I need to build an index for this order". The process of building the indexes is averaging somewhere around 7-8 minutes / index, which obviously changes depending on how many data elements are in the indexed field. Thanks again for your very detailed explanations of what the indexes actually do, which allowed me to figure out that this might actually help. John W. Colby www.ColbyConsulting.com Asger Blond wrote: > John > I just reread my previous posting and - stupid me - I totally forgot that > you have OR operators in your queries... > When you have OR operators the query optimizer will never use a composite > index for searches no matter how selective the where condition is - it will > only use the composite index for fetches in an index scan. Why? The query > optimizer could use the condition referencing the first field defined in the > index for a search operation, but then it has to do an index scan anyhow for > the rest of the condition referencing the other fields - and of cause it > decides to just make an index scan. > In this situation you could as well use an index with includes columns. > > If you really want the optimizer to use index *searches* then you have to > build *a separate index for each field*. The optimizer can use these indexes > for searches and then join the results. This kind of execution is also > "covering" since SQL Server can find all values needed in the indexes and > don't have to go to the actual data pages. But in this case it certainly > matters how selective your condition is. > I just made a test on a table with 4 fields and 1 million records having > lots of duplicate values which gives a poor selectivity. If the where clause > only contained two conditions with an OR then the optimizer used a join of > two seeks. But if it contained tree or more conditions the optimizer > reverted to a table scan. > I then changed the values to have much less duplicates (giving better > selectivity) and now the optimizer would use several index seeks and join > the results. > Finally I tested performance for this set of records (having less > duplicates) on 1) separate indexes on each field and 2) a composite index of > all fields. There was a substantial difference: 1) had a much faster > execution time than 2): 35 ms versus 1193 ms... > If you want to test this on your own tables you can use a SSMS-query window > and use Query | Include client statistics. This will give a result tab with > statistical information after running a query - look especially for the > value of "Total execution time". > > Asger _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com