Efficient TSQL Query

Question

Is there a more efficient way of writing this SQL query?

It returns about 800 rows from a set of 100,000 rows in about 45 seconds.

I am using Sql Server 2008 R2

Select a.Div as Division
      ,a.Room as RoomLocation
      ,a.Form as Forms
      ,a.Nums as TotalNumberLocations
From AView a
Where a.Id = '1'
And a.Div = 'A'
Group By a.Div, a.Nums, a.Room, a.Form

union

Select b.Div as Division
      ,b.Room as RoomLocation
      ,b.Form as Forms
      ,b.Nums as TotalNumberLocations
From AView b
Where b.Id = '1'
And b.Div = 'G'
Group By b.Div, b.Nums, b.Room, b.Form

union

Select c.Div as Division
      ,c.Room as RoomLocation
      ,c.Form as Forms
      ,c.Nums as TotalNumberLocations
From AView c
Where c.Id = '1'
And c.Div = 'R'
Group By c.Div, c.Nums, c.Room, c.Form
Order By Forms asc, TotalNumberLocations asc

Why are you not just doing `where ID = '1' and Div in ('A', 'G', 'R')`, also I don't understand why you are doing a `Group By`, you may want to be using a `distinct` instead. — Scott Chamberlain, Sep 06 '13 at 19:08
In this case you don't need a UNION at all but, in general, if you know your result sets are not going to overlap (in this case a Div can't be A and G at the same time), and each branch of the union can't produce duplicates (in this case eliminated by your GROUP BY), you should use UNION ALL, not UNION. UNION can be much more expensive as it will have to introduce a sort or similar operation to remove duplicates across all of the sets. — Aaron Bertrand, Sep 06 '13 at 19:12
Is `AView` a table, or is it a view, as the name could suggest? In the latter case, what is it's definition? — FrankPl, Sep 06 '13 at 19:18
I typically won't go to a UNION/UNION ALL scenario with the same table unless you have a lot of NESTED OR statements. That's when UNION typically performs better. That has been my experience. — db_brad, Sep 06 '13 at 20:35

score 7 · Accepted Answer · answered Sep 06 '13 at 19:08

7

Why use a UNION when you can have the values in an IN clause? You were scanning the table three times.

Select Div as Division
      ,Room as RoomLocation
      ,Form as Forms
      ,Nums as TotalNumberLocations
From AView 
Where Id = '1'
And Div IN ('A','G','R')
Group By Div, Nums, Room, Form
Order By Forms asc, TotalNumberLocations asc

answered Sep 06 '13 at 19:08

Giannis Paraskevopoulos

18,261
1
49
69

5

+1 though `DISTINCT` rather than `GROUP BY` might be clearer. – Martin Smith Sep 06 '13 at 19:09
1

@MartinSmith Well, when you just need distinct values, yes use DISTINCT. But i didn't want to completely alter the original code. After all this is not the essense of the question and performance wise both GROUP BY and DISTINCT would produce simmilar execution plans in this case. I agree with cleaner though. – Giannis Paraskevopoulos Sep 06 '13 at 19:13
2

This would need to be tested, I have seen cases where the `UNION` version was faster in SQL Server 2008 than `IN()`. – FrankPl Sep 06 '13 at 19:15
`GROUP BY` without an aggregate is just a more confusing way of getting `DISTINCT` though. They've got the same semantics and will likely give the same plans. Though if ordering by a calculated expression the `GROUP BY` [is more flexible](http://stackoverflow.com/a/15533158/73226) – Martin Smith Sep 06 '13 at 19:15
1

The semantics are the same, but the plans aren't always guaranteed to be identical. I'm pretty sure I've seen cases where that part of the operation took place at a different point (but of course I can't find anything in my repro repo right now). – Aaron Bertrand Sep 06 '13 at 19:16
1

@FrankPI I think that may be true for UNIONs that are doing *very different* things. In this case, I find it hard to believe that three UNIONs against the same table using the same where clause with a different parameter would outperform the IN () equivalent, unless there are other things going on (like three filtered indexes, or partitions based on Div, or a host of other unknowns). – Aaron Bertrand Sep 06 '13 at 19:18
I think it really makes a differense when having sub queries, but i am not sure and have not really tested myself. I would change it to Distinct but there is already an answer with it and i wouldn't like having the exact same here. – Giannis Paraskevopoulos Sep 06 '13 at 19:20

score 4 · Answer 2 · edited May 23 '17 at 10:25

4

Similar to the other answer, however you can also replace the GROUP BY with a distinct

Select distinct Div as Division
      ,Room as RoomLocation
      ,Form as Forms
      ,Nums as TotalNumberLocations
From AView a
Where Id = '1'
And Div in ('A', 'G', 'R')
Order By Forms asc, TotalNumberLocations asc

edited May 23 '17 at 10:25

Community

1
1

answered Sep 06 '13 at 19:10

Scott Chamberlain

124,994
33
282
431

score 2 · Answer 3 · answered Sep 06 '13 at 19:16

2

I would assume that an index on columns Id and Div of table AView would be very beneficial for the performance of this query, probably much more that re-writing the query.

answered Sep 06 '13 at 19:16

FrankPl

13,205
2
14
40

2

Also be sure that your types match, if `Id` is an int pass it `1` instead of `'1'` also if `Div` is a `nvarchar` the strings should be `N'A', N'G', N'R'` or you may not get the benefit of the indexes. – Scott Chamberlain Sep 06 '13 at 19:18
@ScottChamberlain - Though actually both of those examples wouldn't prevent index use. `int` has higher datatype precedence than varchar so `'1'` will get cast to `1` and `nvarchar` has higher datatype precedence than `varchar`. – Martin Smith Sep 06 '13 at 19:23
@MartinSmith I only say it because [I have gotten bitten](http://stackoverflow.com/questions/7637907/query-extremely-slow-in-code-but-fast-in-ssms) with parameters where the parameter type was `varchar` but the column and index was `nvarchar`, – Scott Chamberlain Sep 06 '13 at 19:28
1

@ScottChamberlain - It would have been the other way round. A `varchar` column and `nvarchar` parameters. – Martin Smith Sep 06 '13 at 19:28
@MartinSmith after re-reading my old question you where correct. – Scott Chamberlain Sep 06 '13 at 19:29
Distinct Select Execution Time 1684 ms 1622 original 1605 for the one marked as answer. I added indexes and it dropped way down. Thanks again – DewSql Sep 06 '13 at 19:35
1

Did you add two indexes then? I was suggesting to add one combined index containing both columns, as that is most efficient for this query. – FrankPl Sep 06 '13 at 19:42
Yes, after reading the comments I found Covered Indexes and created one for the Id and Div columns – DewSql Sep 06 '13 at 19:43

Efficient TSQL Query

3 Answers3