Sum on Fact table slowed down by Group By

We have a bunch of queries over a time series which were taking over 1 minute to process. The Fact table contains just over 4m rows. Adding a new index on Date and CompanyKey and including the columns being summed resulted in the slowest query now running in 3s. However, we have some queries left over which group on country (fact table joins to DimCompany on CompanyKey).

If I remove the group by, the query runs in a few seconds. I realise the select distinct also slows things down, but I have ideas for that. I'm more focused on the group by performance at this point.

avatar image By Clive 1 asked Jun 15, 2017 at 12:35 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jun 15, 2017 at 12:42 PM

Without even looking at the plan, I can draw some analogies to illustrate why it is costlier for SQL Server to aggregate over grouped data than total data. Imagine you are on stage in an auditorium, and someone asks you to count all the people in the room. Easy, right? Now, count all the people by eye color. Closer to data: count all the Smiths in the phone book. Now count all the smiths but group by the last digit in their phone number. Without getting all the people with the same eye color to sit in the same row, or sorting the phone book by last name and then the last digit in their phone number, it's a lot more work, isn't it?

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Jun 15, 2017 at 12:35 PM

Seen: 57 times

Last Updated: Jun 15, 2017 at 12:49 PM