Optimization with ORs

Hi- I notice that many times when I use an "or" in a query with an and
select from table where col1=x and (col2 = y or col3=z)
It results in terrible performance. It seems to me that there is no way to
make an index which really covers a statement like this. I can either index
on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
any value. Is this correct? If so, how can I improve performance in this
situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
Thanks,
ChrisYou can use the Show Execution Plan feature of Query Analyzer to see exactly
how your query is utilizing indexes.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
If col2 or col3 is highly selective (for example zipcode or date of birth),
then an additional index here would be useful.
http://www.sql-server-performance.c...s_not_equal.asp
SQL Server's query optimizer may even ignore an index when building the
execution plan if the index is not judged as selective enough for it's case
usage.
http://www.sql-server-performance.com/statistics.asp
http://msdn.microsoft.com/library/d.../>
sz_4fxv.asp
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:63A055ED-F3D7-4D95-B887-5F9C16C03F35@.microsoft.com...
> Hi- I notice that many times when I use an "or" in a query with an and
> select from table where col1=x and (col2 = y or col3=z)
> It results in terrible performance. It seems to me that there is no way to
> make an index which really covers a statement like this. I can either
> index
> on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
> any value. Is this correct? If so, how can I improve performance in this
> situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
> Thanks,
> Chris|||"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:63A055ED-F3D7-4D95-B887-5F9C16C03F35@.microsoft.com...
> Hi- I notice that many times when I use an "or" in a query with an and
> select from table where col1=x and (col2 = y or col3=z)
> It results in terrible performance. It seems to me that there is no way to
> make an index which really covers a statement like this. I can either
> index
> on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
> any value. Is this correct? If so, how can I improve performance in this
> situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
> Thanks,
> Chris
Have you tried a covering index on col1/col2 and another on col1/col3?
Also, try rewritting the query using Union All:
select from table where col1=x and col2 = y
union all
select from table where col1=x and col3 = z|||Chris,
a lot depends on selectivity of the columns. For instance, if col1 is
highly selective, then an index on (col1, col2, col3) might be very
useful.|||Raymond,
a minor correction:
select from table where col1=x and col2 = y
union all
select from table where col1=x and col3 = z
-- do not introduce duplicates
and not(col2 = y)|||Would a Union really be any faster than an or? Isn't it essentially the same
thing?
Chris
"Alexander Kuznetsov" wrote:

> Raymond,
> a minor correction:
> select from table where col1=x and col2 = y
> union all
> select from table where col1=x and col3 = z
> -- do not introduce duplicates
> and not(col2 = y)
>|||You've mentionned queries with terrible performance.
Modify one of them using Union and compare.
I can't say that this will solve all your problems though.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:400D4757-8B2B-4DB2-9A78-92683567A820@.microsoft.com...
> Would a Union really be any faster than an or? Isn't it essentially the
> same
> thing?
> Chris
> "Alexander Kuznetsov" wrote:
>|||Chris,
in some cases a query with OR will be executed as a table/clustered
index scan. Rewriting it as a UNION, you may get the same results
retrieved by 2 index ss, possibly followed by a sort, if it's UNION
not a UNION ALL. I don't think anybody can tell you what's better in
your case without getting more information|||Good catch Alex.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1140796390.674385.23120@.v46g2000cwv.googlegroups.com...
> Raymond,
> a minor correction:
> select from table where col1=x and col2 = y
> union all
> select from table where col1=x and col3 = z
> -- do not introduce duplicates
> and not(col2 = y)
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:587028
If you have many queries like that, then maybe your schema is not
properly normalized. Do you have a real life example?
Gert-Jan
querylous wrote:
> Hi- I notice that many times when I use an "or" in a query with an and
> select from table where col1=x and (col2 = y or col3=z)
> It results in terrible performance. It seems to me that there is no way to
> make an index which really covers a statement like this. I can either inde
x
> on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
> any value. Is this correct? If so, how can I improve performance in this
> situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
> Thanks,
> Chrissql