Prev: loop
Next: Is there any replacement for SQLXML in sql server 2008 R2?
From: J.B. Moreno on 4 Jun 2010 02:45
Hello,

I was working on a query that had multiple join conditions, and even
though there was an index covering all of the columns used in the join
(none of the columns were the primary key for either table), it was
taking a long time (~20 seconds).

The original query looked like:

select b.*
from a
inner join b
on a.num = b.num or (a.name = b.name and a.date = b.date)

I rewrote it as:

select b.* from a inner join b on a.num = b.num
union
select b.* from a inner join b on a.name = b.name and a.date = b.date

and suddenly it's sub second.

I don't suppose anyone can give me an answer as to why the first one
was taking so long (other than the fact that it was ignoring an index
on one of the tables), but I thought I'd share the curious results.

(It's not an issue for my actual problem as it also includes a where
clause that results in yet a third query plan that also runs acceptable
fast, which is understandable as it restricts the results to a single
row from the larger of the two tables).

--
J.B. Moreno
From: Erland Sommarskog on 4 Jun 2010 03:15
J.B. Moreno (planB(a)newsreaders.com) writes:
> I was working on a query that had multiple join conditions, and even
> though there was an index covering all of the columns used in the join
> (none of the columns were the primary key for either table), it was
> taking a long time (~20 seconds).
>
> The original query looked like:
>
> select b.*
> from a
> inner join b
> on a.num = b.num or (a.name = b.name and a.date = b.date)
>
> I rewrote it as:
>
> select b.* from a inner join b on a.num = b.num
> union
> select b.* from a inner join b on a.name = b.name and a.date = b.date
>
> and suddenly it's sub second.
>
> I don't suppose anyone can give me an answer as to why the first one
> was taking so long (other than the fact that it was ignoring an index
> on one of the tables), but I thought I'd share the curious results.

Yes, it is not uncommon that queries with OR conditions runs better if
rewritten with UNION. I was actually quite surprised the other day when
I worked with a client's query. In this particular case, the optimizer
was actually to find a good plan based on index concatenation, and a
rewrite with UNION did not yield any effect.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: J.B. Moreno on 5 Jun 2010 13:47
Erland Sommarskog <esquel(a)sommarskog.se> wrote:

> J.B. Moreno (planB(a)newsreaders.com) writes:
> > I was working on a query that had multiple join conditions, and even
> > though there was an index covering all of the columns used in the join
> > (none of the columns were the primary key for either table), it was
> > taking a long time (~20 seconds).
-snip-
> > I don't suppose anyone can give me an answer as to why the first one
> > was taking so long (other than the fact that it was ignoring an index
> > on one of the tables), but I thought I'd share the curious results.
>
> Yes, it is not uncommon that queries with OR conditions runs better if
> rewritten with UNION. I was actually quite surprised the other day when
> I worked with a client's query. In this particular case, the optimizer
> was actually to find a good plan based on index concatenation, and a
> rewrite with UNION did not yield any effect.

So it's the OR that is making all of the difference, and this is a know
condition. I guess I can see that.

Thanks, I'll keep that in mind for the future (in this particular case
it doesn't really make a difference as the slow query was my actual
query minus a WHERE clause that used the primary key for one of the
tables, so it was fast enough).

--
J.B. Moreno
 | 
Pages: 1
Prev: loop
Next: Is there any replacement for SQLXML in sql server 2008 R2?