Which is more efficient, join or straight select

#1

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)

#2

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.

<jw56578 (AT) gmail (DOT) com> wrote

Quote:

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)

#3

(jw56578 (AT) gmail (DOT) com) writes:

Quote:

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, esquel (AT) sommarskog (DOT) se

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

#4

On 14 May 2005 16:37:58 -0700, jw56578 (AT) gmail (DOT) com wrote:

Quote:

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)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Which is more efficient, join or straight select

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Which is more efficient, join or straight select - 05-14-2005 , 06:37 PM

Re: Which is more efficient, join or straight select - 05-14-2005 , 08:38 PM

Re: Which is more efficient, join or straight select - 05-15-2005 , 02:59 AM

Re: Which is more efficient, join or straight select - 05-15-2005 , 04:16 PM