[dba-SQLServer] SPAM-LOW: Re: How to construct queries

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





More information about the dba-SQLServer mailing list