4

I have an aggregate query that has a lot of columns and generate a huge data set:

SELECT      column1,
            column2,
            ...
            column20,
            sum(column21),
            ...
            sum(column40)
INTO        #Output
FROM        #Ledger
GROUP BY    column1,
            ...
            column20

The input table (#Ledger) has 18m rows and the result table (#Output) has 600k rows. The query took 9 minutes. Is there anything I can do to make it faster? Here's the execution plan:

execution plan

There's no index on the #Ledger table and the exclamation point in Sort gives the following warning:

Operator used tempdb to spill data during execution with spill level 1 and 4 spilled thread(s), Sort wrote 1541592 pages to and read 1541592 pages from tempdb with granted memory 3752160KB and used memory 3681824KB

  • 2
    Have you tried adding an appropriate index to #Ledger ? What have you tried? Also, please add the create table scripts for the tables in question. See how to create an MCVE for details on how to create a good question. – Max Vernon Apr 20 '18 at 14:25
  • 2
    I'm not suggesting creating an index with 20 columns. I just want to see if you tried anything, and if so, what impact it had. Adding a clustered index to a good candidate leading column might be very helpful, just for instance. – Max Vernon Apr 20 '18 at 14:31
  • 1
    @MikeHenderson - you can anonymize your plan with Sentry One's Plan Explorer – Max Vernon Apr 20 '18 at 14:32
  • 2
    Is the plan better with a OPTION(HASH GROUP); hint? – Erik Darling Apr 20 '18 at 14:35
  • 1
    @sp_BlitzErik yes, a lot better. Down to 1.5 minutes with OPTION (HASH GROUP). If you turn that into an answer, I'll accept that. – Mike Henderson Apr 20 '18 at 15:45
11

Looking at your query plan, it seems that the optimizer has chosen a Stream Aggregate operator to handle your GROUP BY. The downside of these is that they require ordered data, which a HEAP is not.

David's suggestion is perfectly valid. Adding a clustered index may remove the Sort operator, so performance testing that vs. my suggestion is up to you.

If indexing the temp table doesn't give you the performance you need, applying an OPTION(HASH GROUP); hint to the query will influence the optimizer to use a Hash Match Aggregate rather than a Stream Aggregate.

Hash Match Aggregates don't require sorted data, but may also spill to disk the way the Sort spilled in the Stream Aggregate plan.

6

Remove the unnecessary GROUP BY columns and create clustered index on the remaining ones.

What subset of those GROUP BY columns are necessary to uniquely identify a row in #OUTPUT? Keep those, and replace the others. Something like:

SELECT      column1,
            column2,
            column3,
            column4,
            max(column5) column5,
            ...
            max(column20) column20,
            sum(column21),
            ...
            sum(column40)
INTO        #Output
FROM        #Ledger
GROUP BY    column1,
            column2,
            column3,
            column4
  • An interesting idea. I will try this later today – Mike Henderson Apr 20 '18 at 15:46
  • 1
    And the - chosen 4 (or n) columns - don't necessarily have to be UNIQUE. If all combination have small number of rows - and you use the original GROUP BY with the 20 columns - the clustered index will still be used effectively, with only the low overhead of sorting/separating each small group into subgroups. – ypercubeᵀᴹ Apr 20 '18 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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