Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL query like this:

SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D)
FROM SomeTable
GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END)

On a huge dataset the actual execution plan attributes most of the time to a node that corresponds to logical operation Aggregate and physical operation HashMatch.

Will changing the order in which the columns and conditions are listed in the GROUP BY list affect performance?

share|improve this question

2 Answers 2

Despite what MSDN documentation says, no, it doesn't matter for GROUP BY queries.

You can test it here, at: SQL-Fiddle test (SQL-Server 2012)

CREATE TABLE test
( id INT IDENTITY(1,1)
, a  INT NOT NULL
, b  INT NOT NULL
, c  INT NOT NULL
, d  INT NOT NULL
, PRIMARY KEY (id)
) ;

CREATE NONCLUSTERED INDEX a_b_c_include_d_index
    ON test (a, b, c)
    INCLUDE (d) ;

INSERT INTO test
(a, b, c, d)
VALUES
 ... some 50K rows

The queries:

SELECT COUNT(*) AS num
     , MIN(cnt) AS min_count
     , MAX(cnt) AS max_count
     , MIN(min_d) AS min_min_d
     , MAX(min_d) AS max_min_d
     , MIN(max_d) AS min_max_d
     , MAX(max_d) AS max_max_d
FROM
  ( SELECT a
         , b
         , c
         , COUNT(d) AS cnt
         , MIN(d) AS min_d
         , MAX(d) AS max_d
    FROM test 
    GROUP BY a, b, c             --- or with:  `GROUP BY b, a, c`
  ) AS grp ;

produce the same execution plan:

execution plan

MICROSOFT SQL SERVER 2005 XML SHOWPLAN
SELECT
Compute Scalar
Cost: 0%
Stream Aggregate
(Aggregate)
Cost: 0%
Compute Scalar
Cost: 10%
Stream Aggregate
(Aggregate)
Cost: 10%
Index Scan
[a_b_c_include_d_index].[test]
Cost: 89%

Now if you change that Group by to:

    GROUP BY a, b, c
      WITH ROLLUP

it does make a difference and it does produce different execution plans (and different result sets of course). It still uses the index though, at least in that SQL-Fiddle test. It the mismatching order case, it gets the data from the index but it has to do an intermediate sort (to calculate the rollup values).

share|improve this answer

Yes, if you have an index covering those columns.

According to the documentation over at MSDN:

In the context of multiple-column indexes, for ORDER-BY or GROUP-BY to consider a particular index, the ORDER-BY or GROUP-BY columns must match the prefix set of index columns with the exact order. For example, the index

CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 

can help optimize the following queries:

... ORDER BY / GROUP BY "Last Name" ...
... ORDER BY / GROUP BY "Last Name", "First Name" ...

It will not help optimize:

... ORDER BY / GROUP BY "First Name" ...
... ORDER BY / GROUP BY "First Name", "Last Name" ...
share|improve this answer
2  
GROUP BY "First Name", "Last Name" is the same as GROUP BY "Last Name", "First Name", no? So as per WHERE and ON, order doesn't matter. For ORDER BY, it does –  gbn Apr 27 '12 at 14:30
    
The documentation you link to is for SQL Server Compact 4.0 –  Martin Smith Apr 27 '12 at 15:25
    
@MartinSmith: In Jason's favour, the documentation for 2008 version has the same thing. –  ypercube Apr 27 '12 at 15:30
    
@ypercube - link? clicking the "other versions" selector doesn't seem to work for me. It still takes me to an article discussing SQL Server Compact specifically. No idea whether it is true for the compact versions or not either but maybe they do have a more primitive optimiser. –  Martin Smith Apr 27 '12 at 15:32
    
@MartinSmith: Oh you are right, yes, missed that line. So, can we assume that it depends on the version of the SQL-Server? If yes, what about the Azure? I can't find documenation. –  ypercube Apr 27 '12 at 15:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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