JOINS to Sub-Queries -vs- JOINS to Tables

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,

CraigOn 11 Aug 2005 09:24:08 -0700, csomberg@.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)|||(csomberg@.dwr.com) writes:
> 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.

Your query is open to several interpretations, so the answers you get
may not address your real issue.

If your idea is to first join two tables, get those columns into
a temp table, join that with the next table, then this is generally
not a good idea. Although, when it comes to performance there a few
definitive answers. For a certain query, this could actually be a
good strategy. But as a general approach, it's better to throw in
all tables into one query.

And you should not use SELECT * - only list the columns you actually
need.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I need the fastest most efficient method to extract the data as this query is one of the most used in the system. <<

Then test them. But my guess is that the optimizer will do them the
same way. Putting all the tables in the FROM clause will be much easier
to read and maintain, however.sql