Using 1 = 1 in SQL Statements

[TPA_Dude]

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

[paross1]

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

[MikerRoo]

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.

[MikerRoo]

Of course don't grab 2 million rows at a time unless you are really and immediately using all of them.

[TPA_Dude]

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

[TPA_Dude]

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

[Newsgroup_User · Accepted solution]

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
>

[paross1]

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

[TPA_Dude]

Can the "WHERE 1 = 1" clause ever be taken to mean.. WHERE "column 1 value" equal to "the value of 1"?

Thanks

[MikerRoo]

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.