SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 07:29 AM
| ||||
| ||||
 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)      |
|
02-29-2008, 07:29 AM
| |||
| |||
| Re: Which is more efficient, join or straight select 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@gmail.com> wrote in message news:1116113878.749160.180560@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) > |
|
02-29-2008, 07:29 AM
| |||
| |||
| Re: Which is more efficient, join or straight select (jw56578@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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 07:29 AM
| ||||
| ||||
| Re: Which is more efficient, join or straight select On 14 May 2005 16:37:58 -0700, jw56578@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) |
Linear Mode
