JOIN columns question???

[Don Vaillancourt]

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.

I've already removed the DISTINCT, which in my test case doesn't do
anything, but still took up one second.

[MC]

Well, why dont you check execution plan for both queries? If thats the same,
the performance should be the same. Also, if you need more help, generate
some real sample here. Select * and no table schema/data doesnt help.
MC

"Don Vaillancourt" <do**@webimpact.comwrote in message
news:hY******************@nnrp.ca.mci.com!nnrp1.uu net.ca...

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.

I've already removed the DISTINCT, which in my test case doesn't do
anything, but still took up one second.

[Erland Sommarskog]

Don Vaillancourt (do**@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx