question

vparvath avatar image
vparvath asked

Poor performing query-need sugesstions for improvement

Hi , I am loading incremental data into aggregate fact table from detailed fact table. I need to tune the following query.I need your suggestions on this ass to where&how could this be tuned.The query fetches data from detailed table,aggregates it and merges into aggregate fact table.I need help with tweaking the query. Actual Query: Merge dbo.SER_FCT_TradePrograms_Agg AS FTPA USING ( Select NPAggs.SalesCycleGUID, NPAggs.TradeProgramContractGUiD, NPAggs.HierarchyGUID, '00000000-0000-0000-0000-000000000001' AS NonPerformanceGUId, COUNT(DISTINCT NPAggs.RetailStoreGUID) AS TotalNumOfStores , COUNT(DISTINCT NPAggs.InStoreVisited) AS TotalStoresWithInStoreVisits, COUNT(DISTINCT NPAggs.MgmtVisited) AS TotalStoresAudited, COUNT(DISTINCT NPAggs.MgmtMerchValComplete) AS TotalAuditMerchValComplete, COUNT(DISTINCT NPAggs.InStoreMerchValComplete)AS TotalInStoreMerchValComplete, COUNT(DISTINCT NPAggs.MgmtNegativeResponse)AS TotalAuditAnyNegResponse, SUM(NPAggs.SysAllViol) AS NumOfStoresSysAllViolation, SUM(NPAggs.SysAllWarn) AS NumOfStoresSysAllWarning, SUM(NPAggs.SysAllWarnOrViol)AS NumOfStoresSysAllViolOrWarn, SUM(NPAggs.AuditAllViol)As NumOfStoresAuditAllViolation, SUM(NPAggs.AuditAllWarn) AS NumOfStoresAuditAllWarning, SUM(NPAggs.AuditAllWarnOrViol) AS NumOfStoresAuditAllViolOrWarn, SUM(NPAggs.InStoreAllViol) AS NumOfStoresInStoreAllViolation, SUM(NPAggs.InStoreAllWarn) AS NumOfStoresInStoreAllWarning, SUM(NPAggs.InStoreAllWarnOrViol) AS NumOfStoresInStoreAllViolOrWarn FROM ( select FTP.SalesCycleGUID, FTP.TradeProgramContractGUiD, RSHH.HierarchyGUID, FTP.RetailStoreGUID, CASE When MAX(FTP.InStoreResponseDate) IS NOT NULL -- Max is needed to satisfy the group condition Then FTP.RetailStoreGUID Else NULL END AS InStoreVisited, CASE When MAX(FTP.AuditResponseDate) IS NOT NULL Then FTP.RetailStoreGUID Else NULL END AS MgmtVisited, CASE When MAX(CAST(FTP.AuditMerchandisingValuesComplete AS INT)) = 1 Then FTP.RetailStoreGUID Else NULL END AS MgmtMerchValComplete, CASE When MAX(CAST(FTP.InStoreMerchandisingValuesComplete AS INT)) = 1 Then FTP.RetailStoreGUID Else NULL END AS InStoreMerchValComplete, CASE When MAX(CAST(FTP.AuditAnyNegativeResponseRecorded AS INT)) = 1 Then FTP.RetailStoreGUID Else NULL END AS MgmtNegativeResponse, Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.SystematicResponseType in('V') then 1 else 0 end) then 1 else 0 End AS SysAllViol, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.SystematicResponseType in('W') then 1 else 0 end) then 1 else 0 End AS SysAllWarn, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.SystematicResponseType in('V','W') then 1 else 0 end) then 1 else 0 End AS SysAllWarnOrViol, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.AuditResponseType in('V') then 1 else 0 end) then 1 else 0 End AS AuditAllViol, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.AuditResponseType in('W') then 1 else 0 end) then 1 else 0 End AS AuditAllWarn, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.AuditResponseType in('V','W') then 1 else 0 end) then 1 else 0 End AS AuditAllWarnOrViol, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.InStoreResponseType in('V') then 1 else 0 end) then 1 else 0 End AS InStoreAllViol, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.InStoreResponseType in('W') then 1 else 0 end) then 1 else 0 End AS InStoreAllWarn, --if the two counts match then store had all systematic violations Case when COUNT(distinct FTP.NonPerformanceGUID) = SUM(case when ftp.InStoreResponseType in('V','W') then 1 else 0 end) then 1 else 0 End AS InStoreAllWarnOrViol --if the two counts match then store had all systematic violations FROM dbo.SER_FCT_TradePrograms FTP, dbo.SER_DIM_RetailStore RS, dbo.SER_DIM_RetailStore_HierarchyHistory RSHH, dbo.SER_DIM_NonPerformance NP WHERE 1=1 --Retail Store dimension and filters AND FTP.RetailStoreGUID = RS.RetailStoreGUID AND RS.RetailStoreGUID = RSHH.RetailStoreGUID AND NP.NonPerformanceGUID = FTP.NonPerformanceGUID AND EXISTS ( SELECT 'Y' FROM SER_FCT_TradePrograms FTP2 WHERE FTP2.SalesCycleGUID = FTP.SalesCycleGUID AND FTP2.TradeProgramContractGUiD = FTP.TradeProgramContractGUiD AND ( (FTP2.CreateDate> @v_in_RS_Time) OR (FTP2.UpdateDate>@v_in_RS_Time) ) ) GROUP BY FTP.SalesCycleGUID, FTP.TradeProgramContractGUiD, RSHH.HierarchyGUID, FTP.RetailStoreGUID ) AS NPAggs GROUP BY NPAggs.SalesCycleGUID, NPAggs.TradeProgramContractGUiD, NPAggs.HierarchyGUID ) AS SourceData ON ( FTPA.SalesCycleGUID = SourceData.SalesCycleGUID and FTPA.TradeProgramContracGUID = SourceData.TradeProgramContractGUID and FTPA.HierarchyGUID = SourceData.HierarchyGUID and FTPA.NonPerformanceGUID = SourceData.NonPerformanceGUID ) When Matched then Update set FTPA.TotalNoOfStores = SourceData.TotalNumOfStores, FTPA.TotalStoresAudited = SourceData.TotalStoresAudited, FTPA.TotalStoreswithInStoreVisits = SourceData.TotalStoresWithInStoreVisits, FTPA.NumOfStoresWithAuditMerchValComplete = SourceData.TotalAuditMerchValComplete, FTPA.NumOfStoresWithInStoreMerchValComplete = SourceData.TotalInStoreMerchValComplete, FTPA.NumOfStoresWithAnyNegativeResponse = SourceData.TotalAuditAnyNegResponse, FTPA.NumOfStoresWtihSysViolation = SourceData.NumOfStoresSysAllViolation, FTPA.NumOfStoresWithSysWarning = SourceData.NumOfStoresSysAllWarning, FTPA.NumOfStoresWithSysWarnOrViol = SourceData.NumOfStoresSysAllViolorwarn, FTPA.NumOfStoresWithAuditViolation = SourceData.NumOfStoresAuditAllViolation, FTPA.NumOfStoresWithAuditWarning = SourceData.NumOfStoresAuditAllWarning, FTPA.NumOfStoresWithAuditWarnOrViol = SourceData.NumOfStoresAuditAllViolorwarn, FTPA.NumOfStoresWithInStoreViolation = SourceData.NumOfStoresInStoreAllViolation, FTPA.NumOfStoresWithInStoreWarning = SourceData.NumOfStoresInStoreAllWarning, FTPA.NumOfStoresWithInStoreWarnOrViol = SourceData.NumOfStoresInStoreAllViolorwarn, FTPA.UpdateUser = (current_user), FTPA.UpdateDate = (CURRENT_TIMESTAMP), FTPA.Active = 1 When Not Matched then Insert ( SalesCycleGUID, TradeProgramContracGUID, HierarchyGUID, NonPerformanceGUID, TotalNoOfStores, TotalStoresAudited, TotalStoreswithInStoreVisits, NumOfStoresWithAuditMerchValComplete, NumOfStoresWithInStoreMerchValComplete, NumOfStoresWithAnyNegativeResponse, NumOfStoresWtihSysViolation, NumOfStoresWithSysWarning, NumOfStoresWithSysWarnOrViol, NumOfStoresWithAuditViolation, NumOfStoresWithAuditWarning, NumOfStoresWithAuditWarnOrViol, NumOfStoresWithInStoreViolation, NumOfStoresWithInStoreWarning, NumOfStoresWithInStoreWarnOrViol, reateUser, CreateDate, UpdateUser, UpdateDate, Active ) Values ( SourceData.SalesCycleGUID, SourceData.TradeProgramContractGUID, SourceData.HierarchyGUID, SourceData.NonPerformanceGUID, SourceData.TotalNumOfStores, SourceData.TotalStoresAudited, SourceData.TotalStoresWithInStoreVisits, SourceData.TotalAuditMerchValComplete, SourceData.TotalInStoreMerchValComplete, Sourcedata.TotalAuditAnyNegResponse, SourceData.NumOfStoresSysAllViolation, SourceData.NumOfStoresSysAllWarning, SourceData.NumOfStoresSysAllViolorwarn, SourceData.NumOfStoresAuditAllViolation, SourceData.NumOfStoresAuditAllWarning, SourceData.NumOfStoresAuditAllViolorwarn, SourceData.NumOfStoresInStoreAllViolation, SourceData.NumOfStoresInStoreAllWarning, SourceData.NumOfStoresInStoreAllViolorwarn, (select current_user), (SELECT CURRENT_TIMESTAMP), (select current_user), (SELECT CURRENT_TIMESTAMP), 1 );
performancetuning
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image aRookieBIdev commented ·
The question needs to be formatted.Its quite difficult to see the query in there.
0 Likes 0 ·
Kevin Feasel avatar image Kevin Feasel commented ·
I'm on the job... This is a pretty long query...
0 Likes 0 ·
bopeavy avatar image
bopeavy answered
Since not sure what the results do? I recommend since a merge generally updates, deletes, inserts based on the results. You could try replacing the with cte with a temp table or a variable table or etc then do your update/delete/insert based on that temp table.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
A few quick points which might be helpful. When optimizing a query this large, going through the plan is a pain, but it's the best way to do it. I don't have access to your query plans, so any optimization tips will be necessarily limited. I can give you general guidelines, but won't be able to say something like "Well, this accounts for 60% of the query cost, so focus on that portion." It also doesn't help that we don't know which of the main resources (I/O, CPU, or RAM), if any, is binding on this constraint. If running the query causes your CPUs to spike to 100%, then the answer might be a bit different than if it's causing SQL Server to page files in and out constantly because of a lack of RAM. Also, I'm not sure what you mean by "too slow." This is a relative question--if you're loading 100 million records, it's not going to take seconds. And if you have old or inadequate hardware, good queries will only get you so far. With those caveats out of the way, on to how I personally would try to solve this... First, and **least** important, you could potentially drop the merge. I would only do this if, after optimizing the query, you still aren't getting good enough performance. The merge statement is relatively slower than other T-SQL or SSIS alternatives, so its ease of use does come at a cost. But that's more of a last-grasp alternative. The most important thing here is probably to make sure that you have good indexes. Your RetailStore dimension should have an index with RetailStoreGUID. RetailStore_HierarchyHistory should have one on RetailStoreGUID and HierarchyGUID. The NonPerformance dimension should have one on NonPerformanceGUID. Those are the easy ones... The important table here is the TradePrograms fact table. To handle your EXISTS clause, the OR clause makes things a bit tricky. Is it possible to use just the UpdateDate? In general, for a well-functioning system, the UpdateDate should always be at least the same as CreateDate. If so, you could have an index on SalesCycleGUID, TradeProgramContractGUID, and UpdateDate. Otherwise, you may have some difficulty getting an index to work there, due to the OR clause. Once those are done, you're really using one more set of columns with the inner query on TradePrograms: NonPerformanceGUID, InStoreResponseType, AuditResponseType, SystematicResponseType, RetailStoreGUID, AuditAnyNegativeResponseRecorded, InStoreMerchandisingValuesComplete, AuditMerchandisingValuesComplete, AuditResponseDate, InStoreResponseDate, TradeProgramContractGUID, SalesCycleGUID. But handling this will probably be a little trickier than the easier examples above. Given that you are getting a count of distinct NonPerformaceGUIDs so much, it might help to have an index on (SalesCycleGUID, TradeProgramContractGUID, RetailStoreGUID, NonPerformanceGUID). That will make your count distinct no longer require a table scan (assuming there wasn't a good index on it before). From there, perhaps try adding indexes on (SalesCycleGUID, TradeProgramContractGUID, RetailStoreGUID, AuditResponseType), (SalesCycleGUID, TradeProgramContractGUID, RetailStoreGUID, SystematicResponseType), and (SalesCycleGUID, TradeProgramContractGUID, RetailStoreGUID, InStoreResponseType). These are other aggregates that might be causing table scans, and these would turn them into index scans that could be merge joined together. Those would seem to be the biggest pain points on the select query. After that, if there are still performance problems, you might look at including InStoreResponseDate, AuditResponseDate, and the three bit columns on some or all of those indexes. If that's still not enough, it might help to break out the select into a temporary table and do some of the aggregation there first, before going to the merge statement. Over on the TradePrograms_Agg aggregate table, it might help to have an index on SalesCycleGUID, TradeProgramContractGUID, HierarchyGUID, and NonPerformanceGUID. I can't guarantee that this would be a gain, but there's at least a decent chance of it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.