2

Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method? Between this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        INNER join products P ON p.productID = q.productID
        LEFT JOIN colors C ON C.colorID = q.colorID
        LEFT JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID 

and this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID 
    UNION 
    SELECT p.productID, p.product, NULL, NULL, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
    WHERE /* q.productID = @productID AND */ q.sizeID IS NOT NULL AND q.colorID IS NULL
    UNION
    SELECT p.productID, p.product, C.color, C.colorID, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
    WHERE /* q.productID = @productID AND */ q.colorID IS NOT NULL AND q.sizeID IS NULL
    UNION
    SELECT p.productID, p.product, NULL, NULL, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
    WHERE /* q.productID = @productID AND */ q.colorID IS NULL AND q.sizeID IS NULL

EDIT:

SQL Server parse and compile time: CPU time = 32 ms, elapsed time = 65 ms.

(10 row(s) affected) Table 'sizes'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'products'. Scan count 0, logical reads 20, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'quantities'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 12 ms.

(10 row(s) affected) Table 'products'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'quantities'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sizes'. Scan count 0, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

It seems to me that it's the second query that is best for performance, the bigger query with unions.

Do anyone of you got a clue why this happends or do I have to provide with more information (table info and such things) ?

Execution plan:

With left joins With unions

Thanks!

9
  • 1
    Execution Plans will help us out. Also you need to check SET STATSTICS IO, TIME ON and check for logical reads, cpu time, etc That will show you which one is better in terms of Disk I/O and CPU.
    – Kin Shah
    Nov 1, 2013 at 16:54
  • ive added he execution with IO and time ON
    – Kilise
    Nov 1, 2013 at 17:00
  • actual Execution plan as well. From the stats IO and time, the second query with UNION is better, but if you want to understand why it is better, then execution plans will point that out.
    – Kin Shah
    Nov 1, 2013 at 17:18
  • i will upload a picture of the execution plan in a second, if you please you can explain for me why it is better. :)
    – Kilise
    Nov 1, 2013 at 17:29
  • 3
    I think you should run your queries a couple of more times. You have some physical reads in the first query that is not there in the second one that might explain the difference in elapsed times. Apart from elapsed time I would say that the first is the better query. Nov 1, 2013 at 18:02

4 Answers 4

5

Sometimes I wonder if SHORT scripts really is the best thing to focus on.

The size of a script has little to do with how efficiently the query will execute. A more compact statement will likely consume fewer resources in terms of compilation, but (re)compilation is usually a rare occurrence in a live system.

Fewer table accesses is usually desirable, though, and this does lead to more compact code.

Very generally speaking, a smaller execution plan will yield better results, and a lower estimated cost will yield better results. Again, though, it's highly situational. Cost estimates in particular can be way off in some cases. It's important to measure the actual execution time, because at the end of the day, that's what matters.

With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method?

First of all, we need to know how much data will be in these tables in a real system. Right now there's so little it will be difficult to use the STATISTICS TIME performance metrics to figure out a winner -- the results that come back will be dominated by factors other than the query execution. With more data, it's likely the plans will change, thus rendering the comparison here moot.

Having said that, by looking at the query plans as they are now from a logical point of view, the first one is the winner.

You can see that the Clustered Index Scan of quantities appears once in the first plan, while it appears four times in the second one. The second plan also contains an expensive Distinct Sort as a result of using UNIONs (this operator could be eliminated by using UNION ALLs instead, which won't change the results).

The first query could also probably be improved, by getting index seeks on the colors and sizes tables, instead of table scans. It might be worth trying a hash match plan as well (which is what you'll probably see when quantities and products are larger), but for tables this small, the startup cost may be too much overhead to be of benefit.

What I would suggest you do is run each of the statements you want to test 10,000+ times in a loop, figure out the average execution time, and then compare.

1
  • i love this answer. thanks a lot! exactly what i wanted to know
    – Kilise
    Nov 2, 2013 at 17:36
1

With out your entire database this is a hard question to answer. What I can say is this: you can test it your self.

If you use either SET STATISTICS TIME ON or use the patern of

DECLARE @StartTime DATETIME = GETDATE()

/Some Query Here/

SELECT 'Total Run Time Query 1: ' + CAST(DATEDIFF(MS,@StartTime,GETDATE())

SET @StartTime = GETDATE()

/Some Other Query Here/

SELECT 'Total Run Time Query 2: ' + CAST(DATEDIFF(MS,@StartTime,GETDATE())

you can get the query execution time.

For I/O you can use SET STATISTICS IO ON and run your queries.

Another way is to start up profiler and capture the query trace, this will give you details as well.

With those methods you can test any thing you need to test and are the methods I use when I am tuning a query. Once you get your results you can decide which one is faster based on what is important, overall run time, disk IO, or cpu time. In different systems I have found that faster does not always mean time.

2
  • ive provided the statics info in my post, check it out and tell me which query seems to be best for performance?
    – Kilise
    Nov 1, 2013 at 17:04
  • The first has the more concise query plan. however with only 10 rows any tests would be affected by waiting for time on the CPU throwing off the results. I normally test on production sized data or 1 million rows (which ever is larger) where differences can clearly be seen (and a 10 - 15 ms delay in the start of execution does not completely skew the results). Nov 1, 2013 at 19:07
0

Shouldn't you be able to use UNION ALL instead of UNION in the second query?

The result sets look distinct. It might remove one or more operations (possibly the sort) from the second plan.

Regarding the question: The basic rule for me is too keep the dataset you juggle around as small as possible. I would tend to avoid outer joins [this is just a first rule of thumb when starting to write a query, with obviously loads of exceptions].

I would also agree ... the test data you are using is way too small.

1
  • yes an union all instead of an union in the second query is better. and thanks for your answer! i know that the data is too small, i will try to loop through it and see what happends
    – Kilise
    Nov 2, 2013 at 17:49
-4

SQL Server uses the query cost to evaluate which plan is the best for a query. Cost is a ratio of CPU and IO metrics. You can view both cost by selecting the SELECT icon on the left and either look at the yellow box or you can press F4 to get the properties and compare the "Subtree Cost".

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.