I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is

SELECT  f.RetailerID
,       TypeID                                AS TypeID
,       c.CalendarMonth                         AS CalendarValue                    
,       SUM(ISNULL([Volume],0)                  ) AS Volume
,       SUM([VolumeBox]                         ) AS VolumeBox
,       SUM([VolumeKG]                          ) AS VolumeKG
,       SUM([VolumeUnit]                        ) AS VolumeUnit
,       SUM(ISNULL([R_Turnover] , 0.0)          ) AS R_Turnover
,       SUM(ISNULL([R_VAT],0.0)                 ) AS R_VAT
,       SUM(ISNULL([R_TurnoverExVAT],0.0)       ) AS R_TurnoverExVAT
,       SUM([SupplierRealisation_Amt]           ) AS [SupplierRealisation_Amt]
,       SUM([SupplierDiscount1_Amt]             ) AS [SupplierDiscount1_Amt]
,       SUM([Supplier_1NetSales_Amt]            ) AS [Supplier_1NetSales_Amt]
,       SUM([SupplierDiscount2_Amt]             ) AS [SupplierDiscount2_Amt]
,       SUM([Supplier_2NetSales_Amt]            ) AS [Supplier_2NetSales_Amt]
,       SUM([SupplierDiscount3_Amt]             ) AS [SupplierDiscount3_Amt]
,       SUM([Supplier_3NetSales_Amt]            ) AS [Supplier_3NetSales_Amt]
,       SUM(ISNULL(S_CostofGoodsSold, 0)        ) AS [S_CostofGoodsSold]
,       SUM(ISNULL(S_Profit, 0)                 ) AS S_Profit
,       SUM(0.0                                 ) AS AdditionalCostofGoodsSold
,       SUM(ISNULL([R_DistributionCost],0.0)    ) AS R_DistributionCost
,       SUM(ISNULL([R_Profit],0.0)              ) AS R_Profit
FROM [dbo].[EventScenarios] es 
JOIN dbo.[Event] e ON es.[EventID] = e.ID  
JOIN dbo.EventProduct ep on es.EventID = ep.EventID 
JOIN [dbo].Product p ON p.ID=ep.ProductID 
JOIN dbo.EventPL f  ON  e.CountryID = f.CountryID AND f.RetailerID = e.RetailerID AND f.EventID = e.ID
AND ep.ProductID = f.ProductID  
INNER JOIN Calendar c   ON  c.DateID = f.DateID   
WHERE  f.CountryID= 14  AND c.CalendarMonth BETWEEN 201301  AND 201312 
GROUP BY f.RetailerID , c.CalendarMonth ,TypeID  

The query plan is showing 88% time on Clustered Index seek on EventPL table but still its taking around 15 seconds to complete. Is there any way I can optimise it to around 1/2 seconds?

Query Plan Image

Query Plan XML Link

  • This will have to scan all the rows for a given country... probably followed by a big, expensive sort. It doesn't surprise me this takes a while. Hmmm... off hand I don't see anything that would prevent implementing an indexed view over this data. Have you considered that?
    – Jon Seigel
    Aug 5, 2013 at 20:21
  • I cannot create indexed views as I've some calculations as well like SUM(Volume * CaseCount) AS SomeColumn which is not allowed in indexed views and also it's a dynamic query and grouping and joins are based on user input. Here is the query plan postimg.org/image/5ltghlku7/39988724 pastebin.com/KDcRsaCz Aug 5, 2013 at 20:43
  • Thanks for your reply, actually I've around 50 reports in application and there is one stored proc which returns data according to inputs. Will table partitioning help, I've enterprise edition with SQL Server 2012? Also I cannot do the columnstoreindex as I've decimals of 19,6 :( Aug 6, 2013 at 9:19
  • Deleted my previous comments as I misread the plan. Can you add DDL for the tables and script out the statistics so we can recreate the plan. An indication of the hardware you're running this on would help determine how realistic your target is (I suspect .5s is not possible for this volume). Aug 6, 2013 at 11:54
  • Can you post the plan and the hardware spec?
    – mrdenny
    Sep 4, 2013 at 21:31

2 Answers 2


(Moved my comment to an answer, and expanded a bit.)

I cannot create indexed views as I've some calculations as well like SUM(Volume * CaseCount) AS SomeColumn which is not allowed in indexed views and also it's a dynamic query and grouping and joins are based on user input.

From the plan you provided, the clustered index scan is 3.5x overestimated, and the Sort is an order of magnitude underestimated which caused a spill to tempdb. Performance may improve after updating statistics.

However, based on your reply, if this schema is servicing "random" queries against that amount of data, it's going to be extremely challenging and/or expensive to get good performance for all the possible combinations.

For this type of workload, I think the only realistic way to meet the performance goal would be to abandon trying to find a database engine solution, and use Analysis Services instead.

  • You are right that's why I have done this using aggregated tables which I do it overnight and the track changes made by users in the application. Then my query checks if there is anything changed since last night's job run and if there is something then pick that data from normal tables and remaining from my aggregated tables. Sep 6, 2013 at 22:23
  • @safeer: Yep, that works, too. Analysis Services offers more flexibility to do slicing-and-dicing if that's what the business needs in the future.
    – Jon Seigel
    Sep 7, 2013 at 2:13

if you can afford the extra space and cpu to maintain, then create a new index on these fields: countryid, retailerid, calendarmonth, type

as part of the index INCLUDE all the other fields that are being used in calculations

this will make the seek more efficient since it will not have to go back to the page to get extra values


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.