On 11 Aug 2005 09:24:08 -0700,
cs******@dwr.com wrote:
SQL Server 2000
Howdy All.
Is it going to be faster to join several tables together and then
select what I need from the set or is it more efficient to select only
those columns I need in each of the tables and then join them together
?
The joins are all Integer primary keys and the tables are all about the
same.
I need the fastest most efficient method to extract the data as this
query is one of the most used in the system.
Thanks,
Craig
Hi Craig,
I'm not sure I understand your question. Are you asking about the
performance difference between queries like these two?
SELECT A.something, B.otherthing
FROM TableA AS A
INNER JOIN TableB AS B
ON A.xxx = B.xxx
WHERE A.yyy = y
AND B.zzz = z
or
SELECT A.something, B.otherthing
FROM (SELECT xxx, something
FROM TableA
WHERE A.yyy = y) AS A
INNER JOIN (SELECT xxx, otherthing
FROM TableB
WHERE B.zzz = z) AS B
ON A.xxx = B.xxx
My first guess is that there will be no difference. The optimizer is
free to rearrange the query every way it wants, as long as the end
results are the same. They will probably result in the same execution
plan.
On the other hand, it is very hard to predict what the optimizer will
do. It often does a good job, but there still are situations where it
shows that it's just a program.
If you really want to be sure, then why don't you simply test both
against your system?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)