Skip to main content
T
TPA_Dude
Participating Frequently
June 12, 2006
Answered

Using 1 = 1 in SQL Statements

  • June 12, 2006
  • 5 replies
  • 710 views
I have just been told by our DBA that we have to go through our code and remove the "WHERE 1 = 1" in our SQL statements on a SQL 2000 database. He said it is a performance issue when nothing else follows it (no AND's).

For example (the wrong way):
SELECT this_ID FROM thistable WHERE 1 = 1

Versus...
SELECT this_ID FROM thistable WHERE 1 = 1 AND this_ID = 1001

He said Microsoft said that this is causing a performance hit when there is no AND clauses after the WHERE.

Some of these SQL statements are pretty big and there is no practical way to do a CFIF beforehand.

Has anyone heard of a performance hit like this? If so, how much of a hit? The main table in the DB has about 2 million records with full text indexes.

Thanks,
Rob in Tampa



This topic has been closed for replies.
Correct answer Newsgroup_User
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" <webforumsuser@macromedia.com> wrote in message
news:e6khe4$s0a$1@forums.macromedia.com...
> MikerRoo,
> 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,
> Rob
>


5 replies

N
Newsgroup_UserCorrect answer
Inspiring
June 12, 2006
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" <webforumsuser@macromedia.com> wrote in message
news:e6khe4$s0a$1@forums.macromedia.com...
> MikerRoo,
> 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,
> Rob
>


P
paross1
Participating Frequently
June 13, 2006
DBAs.... gotta love 'em. Of course, they would prefer to maintain an "ideal" database that nobody uses and which isn't corrupted by real world use... I think that ALL DBAs should be developers first so they know which end of the database is the most important.

Phil
T
TPA_DudeAuthor
Participating Frequently
June 13, 2006
Can the "WHERE 1 = 1" clause ever be taken to mean.. WHERE "column 1 value" equal to "the value of 1"?

Thanks

T
TPA_DudeAuthor
Participating Frequently
June 12, 2006
MikerRoo,
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,
Rob
T
TPA_DudeAuthor
Participating Frequently
June 12, 2006
Phil,
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.

Thanks,
Rob
A
Anonymous
June 12, 2006
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.
A
Anonymous
June 12, 2006
Of course don't grab 2 million rows at a time unless you are really and immediately using all of them.
P
paross1
Participating Frequently
June 12, 2006
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.

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices