473,326 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Which is more efficient, join or straight select

Which way of retrieving a record is more effecient?:

Select tbl1.field1, tbl2.field1
from table1 tbl1 inner join table2 tbl2
on tbl1.id = tbl2.id
where someid = somevalue
and someid = somevalue
or
Select
field1 = (Select field1 from tabl1 where someid = somevalue),
field2 = (Select field2 from table2 where someid = somevalue)

Jul 23 '05 #1
3 1670
It's not that I know the answer ... I just know where to get it.

For me to answer that I would need to run them both and view the query plan.

Or do an experiment to see which returns results faster (clearing cache
between runs).

Either way, this is something you can do as well,
or if you'd like just sit and wait to hear an answer someday maybe,
(possibly even correct) from a user of this newsgroup ... go right ahead.

Just pray those who 'answer' your question aren't complete idiots like I
very may well be.

One who guesses that they don't differ much after the optimizer processes
them.
<jw*****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Which way of retrieving a record is more effecient?:

Select tbl1.field1, tbl2.field1
from table1 tbl1 inner join table2 tbl2
on tbl1.id = tbl2.id
where someid = somevalue
and someid = somevalue
or
Select
field1 = (Select field1 from tabl1 where someid = somevalue),
field2 = (Select field2 from table2 where someid = somevalue)

Jul 23 '05 #2
(jw*****@gmail.com) writes:
Which way of retrieving a record is more effecient?:

Select tbl1.field1, tbl2.field1
from table1 tbl1 inner join table2 tbl2
on tbl1.id = tbl2.id
where someid = somevalue
and someid = somevalue

or

Select
field1 = (Select field1 from tabl1 where someid = somevalue),
field2 = (Select field2 from table2 where someid = somevalue)


The only way to find an answer for a particular query, is to benchmark
and run both with production data, or data that resembles production data.
Depening on distribution, indexes etc, one query may be the best in
one case, but for another situation the other query wins.

All that said, my experience is that subselects in the column list -
and this includes SET clauses in UPDATE statements, often comes with
a performance penalty. Thus, the first of the two queries above is likely
to give best performance in the majority of the cases.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
On 14 May 2005 16:37:58 -0700, jw*****@gmail.com wrote:
Which way of retrieving a record is more effecient?:

Select tbl1.field1, tbl2.field1
from table1 tbl1 inner join table2 tbl2
on tbl1.id = tbl2.id
where someid = somevalue
and someid = somevalue
or
Select
field1 = (Select field1 from tabl1 where someid = somevalue),
field2 = (Select field2 from table2 where someid = somevalue)


Hi jw,

Though I agree with both David's and Erland's answer, I'd like to add
that the question in itself is strange, since the two versions are
semantically different. Based on the infromation you supplied, they
might well lead to different results. And if they don't, then I suspect
that you have some redundancy in your design; you should deal with that
first before trying to make minor performance improvements.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: Daniel Tonks | last post by:
First, please excuse the fact that I'm a complete MySQL newbie. My site used forum software that I wrote myself (in Perl) which, up until now, has used flat files. This worked fine, however...
3
by: Sean | last post by:
Hi all I have a bit of a dilema that I am hoping some of you smart dudes might be able to help me with. 1. I have a table with about 50 million records in it and quite a few columns. 2. I...
11
by: news-east.earthlink.net | last post by:
The scenario: two tables CustomerTable --------------- CustomerID OrderID CustomerName CustomerEmail
3
by: Edward | last post by:
ASP.NET / VB.NET SQL Server 7.0 Our client has insisted that we change our established practice of building SQL in-line and move it all to SPROCs. Not a problem for 80% of the app. However,...
1
by: Justin | last post by:
Hello, I have a scenario where I have unique identifiers in about 25 tables, each table has varying fields - eg. Table #1 Table #2 Table #3 Table #4 --------- ...
14
by: Roy | last post by:
Apologies for the cross-post, but this truly is a two-sided question. Given the option of creating Looping statements within a stored proc of sql server or in the code-behind of an .net webpage,...
13
by: gonzlobo | last post by:
Greetings, and happyNewYear to all. I picked up Python a few weeks ago, and have been able to parse large files and process data pretty easily, but I believe my code isn't too efficient. I'm...
4
by: Mark | last post by:
Hey folks, I'm looking at making the following query more efficient potentially using the ranking functions and I'd like some advice from the gurus. The purpose of the following is to have a...
2
theGeek
by: theGeek | last post by:
I always wonder which one of join or subquery should I be using to solve a particuar problem so that I get the correct result set in minimum time. It usually happens that I can write a query quickly...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.