Query optimization via table properties?

[Ed Murphy]

Query #1:

select <list of fields>
from C
join B on C.b_key = B.b_key
join A on B.a_key = A.a_key
where A.o_key = <some value>

Query #2:

select <list of fields>
from C
where b_key in (
select b_key
from B
where a_key in (
select a_key
from A
where o_key = <some value>
)
)

#1 (and other things with the same general pattern) are used in
literally thousands of places in this one client's system, and is
much nicer to write, but seems to be rather slower than #2. Is
there any way to tweak the tables to tell the system something
like "hey, B, whenever you're joined to A, you should seriously
consider waiting for A to be filtered down to a manageable level
first"? And similarly for C/B.

MS SQL 2000, SP3, 6.5 compat mode. These are set in stone until
we upgrade the accounting software (highly non-trivial).

[Erland Sommarskog]

Ed Murphy (em*******@socal.rr.com) writes:

Query #1:

select <list of fields>
from C
join B on C.b_key = B.b_key
join A on B.a_key = A.a_key
where A.o_key = <some value>

Query #2:

select <list of fields>
from C
where b_key in (
select b_key
from B
where a_key in (
select a_key
from A
where o_key = <some value>
)
)

#1 (and other things with the same general pattern) are used in
literally thousands of places in this one client's system, and is
much nicer to write, but seems to be rather slower than #2. Is
there any way to tweak the tables to tell the system something
like "hey, B, whenever you're joined to A, you should seriously
consider waiting for A to be filtered down to a manageable level
first"? And similarly for C/B.

MS SQL 2000, SP3, 6.5 compat mode. These are set in stone until
we upgrade the accounting software (highly non-trivial).

No, in SQL 2000 there is no such thing. In SQL 2005 there is something
called plan guides which permits you force the exact query plan for a
query - without having to modify the query itself.
--
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