Monday, May 12, 2008

SQL Statements - Always evaluate on the right side of the expression

While trying to tune a SQL Stored procedure, the DBA came across this statement....

select X from where id * -1 = id

That would mean that the optimizer doesn't know what index to use and that simple query took almost 1/2 second with 1000's of reads.

All he did was switch that around
select X from where id = id * -1

It executed in 0 seconds.

The entire stored procedure shed more than 1/2 the time it took to return data!

His simple statement: Always evaluate on the Right Hand Side!

No comments: