[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