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'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

share|improve this question
    
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 '13 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 –  safeer ahmad Aug 5 '13 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 :( –  safeer ahmad Aug 6 '13 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). –  Mark Storey-Smith Aug 6 '13 at 11:54
    
Can you post the plan and the hardware spec? –  mrdenny Sep 4 '13 at 21:31

2 Answers 2

up vote 3 down vote accepted

(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.

share|improve this answer
    
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. –  safeer ahmad Sep 6 '13 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 '13 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

share|improve this answer

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.