469,110 Members | 1,897 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

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,

Craig

Aug 11 '05 #1
3 1725
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)
Aug 11 '05 #2
(cs******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 11 '05 #3
>> 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.

Aug 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Emanuele D'Arrigo | last post: by
1 post views Thread by E Arredondo | last post: by
2 posts views Thread by Phil Townsend | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.