Inner Join or single queries? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Inner Join or single queries?

Hi all, I was wondering if any of you could tell me if there is a major performance improvement in choosing one of the below methods over the other? The below statment will only ever return 1 row. —————————————–
select a.fieldname, b.fieldname, c.fieldname, d.fieldname
from
a_table a with(nolock) inner join b_table b with(nolock)
on a.id = b.id
inner join c_table c with(nolock)
on a.id = c.id
inner join d_table d with(nolock)
on a.id = d.id
where a.id = @my_param
————————————————- I was thinking that I could declare a number of variables and set them to the individual fields I want in individual select statements in the procedure and return them, would this provide an improvment in performance? eg .. declare @a varchar(10)
declare @b varchar(10)
declare @c varchar(10)
declare @d varchar(10) select @a = fieldname from a_table where …
select @b = fieldname from b_table where …
select @c = fieldname from c_table where …
select @d = fieldname from d_table where … and then return the variables in a select statememnt? Thanks in advance
Shaun
Without testing this myself, I best bet is that the JOIN will work best, of course, this assumes there are appropriate keys on the joined columns. Whenever you don’t know for sure if one method is better than another, testing is your best bet. Often, many factors affect performance, and one method will work great in one case, but not another, and testing is the only way to know for sure.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks Brad, I have been looking at the execution plan, I’m a little confused as to what the CPU Cost and Cost represent.. are they milli seconds of processing time or some other measurment? Thanks
Shaun World Domination Through Superior Software
CPU cost is an arbitrary figure (it doesn’t have an exact meaning), but it is consistent so you can compare the cost of one CPU to another, and the one with the lowest cost will use less server resources. Also, you want to check logical reads when comparing different versions of the queries you rest. You always want to select the query that takes the least number of logical reads. In some cases, this is a better test of performance than CPU cost, as disk I/O is generally a bigger resource hog than CPU use.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>