Execution process of SQL statements

[techipa]

Hi All,

The following queries give the same output. They have written differently.

But performance wise I do not know which one would be better or less expensive. How SQL server treats these two queries differently?

Can anyone tell me which one is more efficient?

declare
  @site int,  @study int,  @tg varchar(10),
  @mattype varchar(50)
set @site = 0
set @study = 114
set @tg = 3

declare @seq int

--First 
    SELECT @seq=max(SeqNo)
      FROM Transform t
INNER JOIN MatSite m
        ON t.Site = m.Site
           and t.Study = m.Study
           and t.MatID = m.MatID
     WHERE t.Site = @site
       AND t.Study = @study
       AND m.TG = @tg
       AND t.TranType in (1, 15, 18, 19)

    SELECT top 1 m.MatID, SeqNo
      FROM MatSite m
 LEFT JOIN Transform t
        ON m.Site = t.Site
           and m.Study = t.Study
           and m.MatID = t.MatID
     WHERE m.Site = @site
       AND m.Study = @study
       AND m.TG = @tg
       AND t.MatID is null
       AND m.SeqNo >= ISNULL(@seq,0)
  ORDER BY m.SeqNo, m.MatID

--Or Second
    SELECT top 1 m.MatID, SeqNo
      FROM MatSite m
 LEFT JOIN Transform t
        ON m.Site = t.Site
           and m.Study = t.Study
           and m.MatID = t.MatID
     WHERE m.Site = @site
       AND m.Study = @study
       AND m.TG = @tg
       AND t.MatID is null
       AND m.SeqNo >= ISNULL((SELECT max(SeqNo)
      FROM Transform t
INNER JOIN MatSite m
        ON t.Site = m.Site
           and t.Study = m.Study
           and t.MatID = m.MatID
     WHERE t.Site = @site
       AND t.Study = @study
       AND m.TG = @tg
       AND t.TranType in (1, 15, 18, 19)) , 0)
  ORDER BY m.SeqNo, m.MatID

Thanks,
techiPA

 

[gmmastros]

This is a very difficult question to answer 'just by looking'. Here's what you should do...

Load the first query in to a Query Analyzer window.
Press CTRL-K
Run the query.

At the bottom of the window, you will see a new tab called Execution plan.

Then, in another window, load the second query, press CTRL-K and run that query.

Comparing the execution plans may indicate which is better. You may also want to check how long it takes the query to run. What I usually do is....

Declare @Start DateTime
Set @Start = GetDate()

PUT YOUR QUERY HERE

Select DateDiff(Millisecond, @Start, GetDate())

You will then see how long it takes to run the query.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

Thanks for the reply George.

I tries to track the execution time and the second query takes little more time than the first one.

The difference is really low. The 1st one returns in 30-60 Milliseconds and the secodn one takes 80-110 Milliseconds.

I kept the execution plan ON and ran the queries but I do not understand the execution plan that well.

Do you recommend any book/online help on how to study the execution plan shown by SQL?

Thanks,
-techiPA

 

[gmmastros]

I'm not sure about the online resources for learning execution plans. Sorry.

I do know that if you see 'table scans', this is really bad (for performance). Index scan is next, followed by index seek and clustered index scan. clustered index seek is the best. There are other steps in there that I don't really know about. Hovering your mouse over each step will give you more information about it.

Sad but true story. I was working on a stored procedure last night. I have a table with bit columns. If I do...

Select columns...
From   table
where  BitColumn = 1

One of the steps showed a convert operation on the bit column. I suspect that the bit column was converting to integer to do the comparison. I then changed the code to...

Declare @Bit1 Bit
Set @Bit1 = 1

Select columns...
From   table
where  BitColumn = @Bit1

Lo & behold, the convert step went away and my proc got a couple milliseconds faster.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

I think the issue in my query is there is no index on t.MatID. That might improve the performace by some miliseconds.

I will keep your tips on execution plan in mind. I will have to learn more about it now.

You gave good example of the bit datatype field. I would have not thought that without running the query in execution plan.

Thanks again
-techiPA