How do you prevent predicate optimization?

#1

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

#2

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

#3

Emin (emin.shopper (AT) gmail (DOT) com) writes:

Quote:

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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

#4

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,

#5

--CELKO-- (jcelko212 (AT) earthlink (DOT) net) writes:

Quote:

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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

#6

On May 7, 5:29*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Quote:

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 Ratchevhttp://www.SQLStudio.com

That works. Thanks!

#7

On May 7, 5:38*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Emin (emin.shop... (AT) gmail (DOT) 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, esq... (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

When I try entering that into the sql server management studio express
I get errors about incorrect syntax. I'm using sql server 2005, can
you give me an example of what syntax I should use?

Thanks,
-Emin

#8

On May 8, 4:49*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

--CELKO-- (jcelko... (AT) earthlink (DOT) 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, esq... (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Yes, the rewrite above gets translated to the IN query.

Thanks,
-Emin

#9

Emin (emin.shopper (AT) gmail (DOT) com) writes:

Quote:

When I try entering that into the sql server management studio express
I get errors about incorrect syntax. I'm using sql server 2005, can
you give me an example of what syntax I should use?

You mean for UPDATE STATISTICS WITH FULLSCAN? You need to specify the
name of the table to update statistics for, and you put this after
STATISTICS.

Else, if you don't know a certain syntax, you can look it up in Books
Online.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How do you prevent predicate optimization?

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

How do you prevent predicate optimization? - 05-07-2010 , 02:05 PM

Re: How do you prevent predicate optimization? - 05-07-2010 , 05:29 PM

Re: How do you prevent predicate optimization? - 05-07-2010 , 05:38 PM

Re: How do you prevent predicate optimization? - 05-08-2010 , 12:47 AM

Re: How do you prevent predicate optimization? - 05-08-2010 , 04:49 AM

Re: How do you prevent predicate optimization? - 05-13-2010 , 04:59 PM

Re: How do you prevent predicate optimization? - 05-13-2010 , 05:00 PM

Re: How do you prevent predicate optimization? - 05-13-2010 , 05:01 PM

Re: How do you prevent predicate optimization? - 05-13-2010 , 05:29 PM