(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