How do you prevent predicate optimization?
in [Using MS SQL Server]
|
From: Emin on 13 May 2010 16:59 On May 7, 5:29 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > 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!
From: Emin on 13 May 2010 17:00 On May 7, 5:38 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Emin (emin.shop...(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, esq...(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 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
From: Emin on 13 May 2010 17:01 On May 8, 4:49 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > --CELKO-- (jcelko...(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, esq...(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 Yes, the rewrite above gets translated to the IN query. Thanks, -Emin
From: Erland Sommarskog on 13 May 2010 17:29
Emin (emin.shopper(a)gmail.com) writes: > 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(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 |