|
|
Posted by Erland Sommarskog on 07/26/06 21:55
Ed Murphy (emurphy42@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, 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
Navigation:
[Reply to this message]
|