Skip to content

Quick T-SQL Performance Comparison

September 3, 2012

I’m not a T-SQL guru. When I have something that will run often, or I have performance concerns, I’ll ask someone like Jeff Moden or Wayne Sheffield to help me write a solution.

However I have a few tricks to check things out quickly and determine what’s a better solution. Recently I ran across a thread asking for a solution to a problem that needed to sum data, but also pick values from a certain row. I posted a quick solution, and a few minutes later there were two others.

I didn’t think mine was great, using a CTE and a subquery felt slightly inefficient, but was it really inefficient? I grabbed the third solution, which was similar to mine, and put both in SSMS. I then ran both pieces of code together, after clicking CTRL+M (include Actual Execution Plan).

; WITH MyCTE (acc_no, c_name, cnt)
AS
( SELECT acc_no
       , c_name
       , COUNT(c_name)
   FROM #testing a
   GROUP BY acc_no
          , c_name  
)
SELECT 
  t.acc_no
, c.c_name
, number_sum = SUM( t.number) 
, r_value_sum = SUM( t.R_Value) 
 FROM #TESTING t
   INNER JOIN mycte c
     ON t.acc_no = c.acc_no
 WHERE c.cnt = (SELECT MAX(d.cnt)
                 FROM MyCTE d
                 WHERE d.acc_no = c.acc_no
               )
 GROUP BY t.acc_no
        , c.c_name
;

with cte1 as (
select acc_no,number,c_name,
       sum(R_Value) over(partition by acc_no) as R_Value,
       sum(time_spent) over(partition by acc_no) as time_spent,
       count(*) over(partition by acc_no,c_name) as cn
from #TESTING),
cte2 as (
select acc_no,number,c_name,R_Value,time_spent,
       row_number() over(partition by acc_no order by cn desc,number desc) as rn
from cte1)
select acc_no,number,c_name,R_Value,time_spent
from cte2
where rn=1
;

With all this code, I ran it and got this in the execution plan window (the results were the same and correct).

comapretsql

If you look at the top of each section, where it says “Query 1” and “Query 2”, and then look to the right, you’ll see the relative percentage of cost of the batch. With two queries in this batch, but solution was only slightly worse than the other solution (52% to 48%). That quickly tells me these are similar solutions.

Now this isn’t an end-all, be-all way to look at queries. This is limited data, and unindexed tables. You’d want to test this with a few loads, and examine the details more closely if you are trying to tune these queries, but as a quick check, this helps to decide if you should think about abandoning one solution quickly.

When I ran all three solutions (mine first, the 48% one above last), I got this:

comapretsql2

The second solution is much worse, almost twice as bad here, so I’d give that up and look at both of the other solutions in more detail if I wanted the optimum solution.

And probably ask Jeff or Wayne for their opinion in the SSC forums. Winking smile

About these ads

From → Blog

4 Comments
  1. Query cost relative to batch is just wrong, it should be Estimated query cost relative to batch. Big difference .You simply cant compare one query to another with the %. UDF for example have 0 cost, make them real efficient right :)

    How did the io/cpu/reads/writes/duration look in profiler ? Thats what i would use to decide.

    • Thanks, I probably have been using this too long without looking for something better.

  2. Kevin Boles permalink

    Query Cost is one of the least important things for me when I am tuning. Usually it is CPU, Duration or Reads (or some combination of those) that you really need to worry about minimizing for a particular query/workload.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,612 other followers

%d bloggers like this: