Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up like this:

SELECT 
     C.CompanyName
    ,O.ShippedDate
    ,OD.UnitPrice
    ,P.ProductName
FROM 
               Customers       as C
    INNER JOIN Orders          as O  ON C.CustomerID = O.CustomerID
    INNER JOIN [Order Details] as OD ON O.OrderID    = OD.OrderID
    INNER JOIN Products        as P  ON P.ProductID  = OD.ProductID
Where 1=1
--  AND O.ShippedDate Between '4/1/2008' And '4/30/2008'
    And P.productname = 'TOFU'
Order By C.CompanyName

Someone basically said that 1=1 is generally lazy and bad for performance.

Given that I don't want to "prematurely optimize" - I do want to follow good practices. I've looked at the query plans before, but generally only to find out what indexes I can add (or adjust) to make my queries run faster.

The question then really... does Where 1=1 cause bad things to happen? And if so, how can I tell?

Minor Edit: I've always 'assumed' as well that 1=1 would be optimized out, or at worst be negligible. Never hurts to question a mantra, like "Goto's are Evil" or "Premature Optimization..." or other assumed facts. Wasn't sure if 1=1 AND would realistically affect query plans or not. What about in subqueries? CTE's? Procedures?

I'm not one to optimize, unless needed... but if I'm doing something that is actually "bad", I'd like to minimize the effects or change where applicable.

share|improve this question
2  
No, it wouldn't. Apart from a few microseconds for the optimizer to remove the redundant condition. You better focus on your date literals not being ambiguous. –  ypercube Feb 1 '13 at 0:01
    
As @ypercube said, it makes no difference. The query optimiser would have to be a piece of **** for such a thing to make any difference ;) –  FreshPhilOfSO Feb 1 '13 at 0:03
4  
Don't believe everything you read on reddit. Please. –  Aaron Bertrand Feb 1 '13 at 0:10
1  
@AaronBertrand I take everything with a grain of salt, until I've experienced it first hand. I'll still take a question that sounds plausible and see if there is any truth to it, especially when it affects my day to day job. –  WernerCD Feb 1 '13 at 0:16
4  
There's grains of salt, then there's the salt content of an entire ocean dumped on top of your office building :P –  FreshPhilOfSO Feb 1 '13 at 0:20
show 1 more comment

3 Answers

up vote 9 down vote accepted

The SQL Server parser optimizer has a feature called "Constant Folding" that eliminates tautological expressions from the query.
If you look at the execution plan, nowhere in the predicates you will see that expression appear. This implies that constant folding is performed anyway at compile time for this and other reasons and it has no effect on query performance.

See Constant Folding and Expression Evaluation During Cardinality Estimation for more info.

share|improve this answer
    
It's probably compiled away because this is a known pattern to do concatenation of fields. –  jcolebrand Feb 1 '13 at 18:06
    
No, it is compiled away because it's tautological. It would work in the same way with 2736 = 2736, which is not just as usual as 1 = 1. The same applies to contradictions. In that case the feature is called "Contradiction detection". –  spaghettidba Feb 1 '13 at 18:08
    
Which part of "known pattern" meant "has to be 1=1"? –  jcolebrand Feb 1 '13 at 18:18
    
Thanks for correcting and adding your useful comment, Paul. I am completely sure you are right, I'm just wondering where to find some documentation to support your statement. I don't seem to be able to find any. –  spaghettidba Feb 4 '13 at 20:30
add comment

In any modern RDBMS (including Oracle, Microsoft SQL Server and PostgreSQL - I'm sure about these) this will have no effect on performance.

As someone noted, this will impact only query planning phase. Hence difference will be visible only when you run thousands of iterations of a simplistic query which does not return any data, like this one:

SELECT 1 FROM empty_table; -- run this 10 000 times.

SELECT 1 FROM empty_table WHERE 1=1; -- run this 10 000 times and compare.

For me, on PostgreSQL 9.0, this is visible with only 10000 iterations:

filip@srv:~$ pgquerybench.pl -h /var/run/postgresql/ -q "select 1 from never where 1=1" -q "select 1 from never" -i 10000
Iterations: 10000
Query:   select 1 from never where 1=1
Total:   2.952 s
Average: 0.295 ms
Query:   select 1 from never
Total:   2.850 s
Average: 0.285 ms
share|improve this answer
add comment

This can be "problem" for Oracle when you use database parameter cursor_sharing. When this is set to "force" it will modify all SQL statements. All the "constants" in queries will be replaced by bind variables(like 1 => :SYS_0).

This option was introduced to deal with some lazy developers. On the other hand it can also harm other lazy developers. But the risk is not too high. Since 11g it has bind variable peeking feature.

share|improve this answer
    
Can you clarify what "Since 11g it has bind variable peeking feature." means? –  ypercube Feb 1 '13 at 14:26
    
@ypercube "Bind variable peeking" means that the optimizer will observe the actual values of bind variables and use data statistics to re-evaluate and possibly regenerate the query execution plan. I doubt peeking will have any effect on the construct that is being discussed though, as it does not depend on data statistics. –  mustaccio Feb 4 at 22:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.