SQL Server Performance

Inner Join or single queries?

Discussion in 'T-SQL Performance Tuning for Developers' started by trifunk, Nov 20, 2002.

  1. trifunk New Member

    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
  2. bradmcgehee New Member

    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
  3. trifunk New Member

    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
  4. bradmcgehee New Member

    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

Share This Page