473,573 Members | 3,211 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1865
On 11 Aug 2005 09:24:08 -0700, cs******@dwr.co m 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.c om) 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****@sommarsk og.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2055
by: Emanuele D'Arrigo | last post by:
Hi everybody! I hope you can gimme a hand with this: I just found out that the server I'm writing some code for only has MySQL 3.23.55 which unfortunately doesn't support subqueries (and dumb me I spent so much time trying to make them work thinking it was a syntax problem). So, I gotta find another way around. I think temporary tables...
6
2338
by: J Belly | last post by:
Hi, all: This is probably a simple problem, but, as an SQL newbie, I'm having a little trouble understanding multi-joins and subqueries. I have the following tables and columns: MemberTable -----
1
2744
by: E Arredondo | last post by:
Hi, I am about migrate from an old program database to MySql (Running under RH LINUX)and I'm wondering which is the best option to do : I currently have one file for each of my modules, (I'm using filepro plus) , so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with their own fields. 1) Should I create multiple...
2
1681
by: Phil Townsend | last post by:
I have seen a lot on ADO.NET data relations, but my jury is still out on the benefits of coding a data relation vs. using a join in a stored procedure. I know there is more overhead when using the join, since there is more data in the result set, but how big of an issue is this, especially when working with smaller databases? *** Sent...
2
3247
by: CSN | last post by:
Is there much difference between using subqueries and separating out them into separate queries? __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is...
4
4992
by: Dirk Olbertz | last post by:
Hi there, I'm currently about to redesign a database which you could compare with a database for managing a library. Now this solution will not only manage one library, but 100 to 500 of them. Currently, eg. all the data about the inventory (books) is held in one table for all the libraries. Is it useful to spread this to one table for...
1
1559
by: RoseM | last post by:
Hi again, I could use some help and have not been able to come to grips with what I need to do to make this work. I have a table of theories and votes and I want to return the percent to total of 'yes' votes. Here's what I have so far, which doesn't work: SELECT Count(tentries.vote) AS CountOfvote,count(tentries.theory) as countoftheory...
1
2821
by: yasinirshad | last post by:
Hi.. how to use joins for 4 tables.. in my query i have CALLS,STATUS,USERS,MESSAGES (4 tables). Query: "SELECT CALLS.CALL_ID, REQUESTOR, USERS.USER_NAME REQUESTOR_NAME, DESCRIPTION, TYPE, SCOPE, SEVERITY, STATUS.STATUS_LABEL STATUS,FLAG MESSAGE_FLAG FROM CALLS,STATUS,USERS,MESSAGES WHERE REQUESTOR='" & Session("USER_ID") & "' AND...
1
2871
by: lizandra | last post by:
Greetings, I am a newbie, I have been working to extract data from a basic sales db and trying to decide when I should use joins and when I should use subqueries. Much of what I read online says use subqueries only as a last resort, especially correlated ones as they do a record by record data evaluation and are very resource intensive. Are...
0
7741
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7661
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7977
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8165
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8026
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5550
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3692
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3686
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2163
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.