This content has been marked as final. Show 9 replies
If you have nothing but WHERE 1=1 in your query, then you will be returning ALL rows from your table, which could be a big performance hit with a large table. The only reason for the 1=1 is to "protect" your query from throwing a syntax error if all of your <cfif> statements in your <cfquery> are false. You might change this by ensuring that at least one of your parameters exist before even executing the <cfquery> in the first place.
Run your query in query analyzer. Turn on show plan and show trace and see for yourself.
Note that you usually need to average 10 or so runs to get bettter numbers.
Anyway, on one of our SQL 2000 systems there is absolutely no difference between where 1=1 and no where clause.
WHERE 1=1 and where 0=1 are valid techniques and I don't know a DBA who doesn't use them.
Thanks for the speedy response. I forgot to mention.. there are TOP 200 clauses in all the big SQL statements.
And, like I said before... some of the queries are too big to test to see if at least 1 of the condidtions exsist. More often than not, the WHERE 1 = 1 clause will not be alone. I personally think it is a pet peeve of the DBA where he doesn't see any use of the 1 = 1. He said.. it's always going to be true. Kinda the point of having it.
This was taught to me years ago.. but, this is the biggest application I have worked on where SQL performance could be an issue. It was mentioned that the hit comes when SQL Server is building its execution plan and has to include the WHERE 1 = 1 clause. How much of a hit can it cause? Most of the empty clauses with no AND's are most likely in a JOIN clause or something.
The WHERE placeholder is in almost all of our queries in one way or another. Not a small project.
Of course don't grab 2 million rows at a time unless you are really and immediately using all of them.
Thank you also for your speedy response. I have debugging turned on for my IP, so I will start writing down execution times for individual queries. I didn't know I could view the plan, so thanks. I am sure that will help alot.
Thanks again to everyone,
tell him to do his g**dam job and tune the database like a real dba should
and quit harping about stuff that affects performance about as much as you
farting close to the server.
"TPA_Dude" <email@example.com> wrote in message
> Thank you also for your speedy response. I have debugging turned on for
> IP, so I will start writing down execution times for individual queries.
> didn't know I could view the plan, so thanks. I am sure that will help
> Thanks again to everyone,
Can the "WHERE 1 = 1" clause ever be taken to mean.. WHERE "column 1 value" equal to "the value of 1"?
No, not in SQL server 2000.
SS2K allows columns to be referenced by number in only a few places, notably the order by clause.
Anyway, use query analyzer to show the execution plan and the execution trace. You will see no difference between where 1=1 and no where clause at all.
I must second "Marc E". Tell the DBA to show you some REAL numbers or (and) shut up.