Posted by Erland Sommarskog on 01/29/07 23:04

Don Vaillancourt (donv@webimpact.com) writes:
> Here's an oversimplified version of a query that I'm writing and wanted
> to know if there are any performance differences between the two versions.
>
>
> select *
> from table_a a , table_b b
> where a.col_1 = b.col_1
> and a.col_1 = 1000
>
>
> versus
>
>
> select *
> from table_a a , table_b b
> where a.col_1 = 1000
> and b.col_1 = 1000
>
> All the tests show that they run at the same speed. But I have a very
> large query that joins 5 tables together and I'm trying to get as much
> out of it as possible. Currently it runs at 2 seconds which I really
> don't like and would like to get it at under 1 second. So I'm looking
> for every little bit.

The latter query looks problematic to me. I recall that I once resolved
a performance issue which was due to that the programmer had joined to
tables only over a variable. This was in SQL 6.5, and the optimizer gets
better for every version, so this may not be an issue anymore.

But when tweaking queries, just poking around with the syntax at random
is time-consuming. A better strategy is to examine the query plans, and
also see if indexing can be improved.

Also keep in mind that if one certain way of writing the query seems to work
better, it may be different next week when statistics have changed.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]