Hi,

I need to query a very large table based on the gender of one of the
columns. Since that column is not a good candidate for index, the SQL engine
chooses to use table scan instead of index seek. You can imagine it always
took a long time to complete the query. Anyone got a better idea on this
matter ?

Thanks.


George

Report this post to a moderator

If that column has a clustered index, then the index would probably be used.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver


"Someone" <someone@hotmail.com> wrote in message news:umIItpUXCHA.2544@tkmsftngp12...
> Hi,
>
> I need to query a very large table based on the gender of one of the
> columns. Since that column is not a good candidate for index, the SQL engine
> chooses to use table scan instead of index seek. You can imagine it always
> took a long time to complete the query. Anyone got a better idea on this
> matter ?
>
> Thanks.
>
>
> George
>
>
>

Report this post to a moderator

Since the column is, by its very nature, low in selectivity then indexes
will not help much.

However, if your table is very wide (contains lots of columns) and your
query only needs to reference a small number of those columns, then you
may get a performance improvement by creating a covering index where the
first column is the gender and the other columns you reference in the
query are also included. You do not need to include the columns that
you have in the clustered index (if you have one) as these are
implicitly included in every other index.

If your query has an ORDER BY then include the ORDER BY columns in the
index immediately after the gender column in the same order as the ORDER
BY statement as if SQL Server using the index then the results will be
already ordered and no sort will be required.

Try this and examine the execution plan to see if your index is being
used - there is no guarantee unless you use hints.

Regards

Vaughan Powell
MCDBA, MCSD, MCSE

Vice President Professional Services
Information Management Group
<http://www.imgeurope.com/>

This posting is provided "AS IS" with no warranties, and confers no
rights.
You assume all risk for your use. ©2002 Information Management Group.
All rights reserved.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Report this post to a moderator