Prev: QA Consultant , Richmond, VA
Next: SAS Data Analyst , Richmond, VA
From: Emin on 7 May 2010 14:05
Dear Experts,

I would like to explicitly prevent SQL Server 2005 from rewriting a
query in my WHERE clause because the rewrite slows things down by many
orders of magnitude.

I have a query like

SELECT * FROM A WHERE b in (31, 78)

which takes forever (times out actually).

If I instead do SELECT * FROM A WHERE b = 31 or if I do SELECT * FROM
A WHERE b = 78, each query runs very quickly but the combination is
incredibly slow despite the fact that I have indexes on appropriate
things.

After some investigation with the query optimizer, I determined that
SQL Server is rewriting the predicate "b in (31, 78)" as (b>=31) AND
(b<=78) as an intermediate step. I suspect this makes the query take a
long time because there are *LOTS* of records with b>=31 and b<=78.

So is there a way I can prevent the query optimizer from rewriting
this predicate? I just want the query optimizer to basically do the
first query and then do the second query and combine them without
being so "clever".

Any suggestions?

Thanks,
-Emin
From: Plamen Ratchev on 7 May 2010 17:29
What do you get when you use the separate queries with UNION:

SELECT <columns> FROM A WHERE b = 31
UNION ALL
SELECT <columns> FROM A WHERE b = 78;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 7 May 2010 17:38
Emin (emin.shopper(a)gmail.com) writes:
> I would like to explicitly prevent SQL Server 2005 from rewriting a
> query in my WHERE clause because the rewrite slows things down by many
> orders of magnitude.
>
> I have a query like
>
> SELECT * FROM A WHERE b in (31, 78)
>
> which takes forever (times out actually).
>
> If I instead do SELECT * FROM A WHERE b = 31 or if I do SELECT * FROM
> A WHERE b = 78, each query runs very quickly but the combination is
> incredibly slow despite the fact that I have indexes on appropriate
> things.
>
> After some investigation with the query optimizer, I determined that
> SQL Server is rewriting the predicate "b in (31, 78)" as (b>=31) AND
> (b<=78) as an intermediate step. I suspect this makes the query take a
> long time because there are *LOTS* of records with b>=31 and b<=78.

If that is happening, I would suspect that statistics are out
of date. What happens if you run UPDATE STATISTICS WITH FULLSCAN
on the table?

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: --CELKO-- on 8 May 2010 00:47
The usual re-write is:

SELECT *
FROM A
WHERE b = 31
OR b = 78;

then fancy optimizers do other things when the IN() list is longer.
Run fresh stats and see what happens. It looks like the data on b is
wrong,
From: Erland Sommarskog on 8 May 2010 04:49
--CELKO-- (jcelko212(a)earthlink.net) writes:
> The usual re-write is:
>
> SELECT *
> FROM A
> WHERE b = 31
> OR b = 78;

In SQL Server, this rewrite is performed in the parsing layer, and what
the optimizer sees is the above which thus above is identifical with the
original query.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: QA Consultant , Richmond, VA
Next: SAS Data Analyst , Richmond, VA