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)

(jw56578@gmail.com) writes:
[quoted text, click to view]

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@sommarskog.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

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
--

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.


[quoted text, click to view]