Thursday, January 12, 2006

Universal SQL Performance Improver Discovered

In an AskTom thread this week, the poster wrote:
"...I have been told before by several people, and I have implemented myself on several SQLs that adding the clause "AND 1=1" literally to any SQL statement helps improve the performance of the SQL statement dramatically."

And we've all been wasting our time looking for a FAST=TRUE parameter.

8 comments:

Jeff Hunter said...

imagine my surprise when one candidate exclaimed "We never use joins, always exists. Our DBA worked at Oracle and he's the expert."

Scott Swank said...

I enjoyed Tom's later suggestion that the truly substantial performance gains were to be had with "AND 1=0".

Teko said...

You can use (1=0) only if your table is empty.
If the table is full you will get check constraint violated, since no row will meet that criteria.

William Robertson said...

What check constraint?

Thai Rices said...

I've always wondered how I can check when a table is "full". Now I know I just have to "add 1=0" and see if it violates the "check constraint".

Ta bu shi da yu said...

Uh, sorry fellas, but no that won't happen at all. Using 1=0 forces a full table scan.

William Robertson said...

What won't happen?

Tony Andrews said...

Sometimes the comments are more WTF than the original post...