October 21, 2010 at 1:49 pm
Below is a query that runs about 20 mins and returns about 20k rows. Obviously I'd like for it to run a little faster. I've attached the query execution plan. Any help, I'd appreciate it! Thanks.
WITH PA AS (
SELECT DISTINCT TransactionID, LOC.Location_Idx, Loc.Location_Code, TransactionDate_Idx, Event_Barcode, Version_Code, Version_Name, SubVersion_Code,
NumberMailed
FROM [DW_DatamartDB].[dbo].[FactCampaignOffer] C
INNER JOIN DimCampaign CAMP ON C.[Campaign_Idx] = CAMP.[Campaign_Idx]
INNER JOIN DimCampaignPriceEvent EV ON C.PriceEvent_Idx = EV.PriceEvent_Idx AND
EV.Campaign_Idx = C.Campaign_Idx
INNER JOIN DimCampaignVersion V ON C.Version_Idx = V.Version_Idx AND
C.Campaign_Idx = V.Campaign_Idx
INNER JOIN DimLocation LOC ON C.Location_idx = LOC.Location_Idx
INNER JOIN FactPriceAudit PA ON EV.Event_Barcode = PA.Event_Code AND
TransactionDate_Idx BETWEEN EventStartDate_Idx AND EventEndDate_Idx AND
C.Location_Idx = PA.Location_Idx
WHERE Campaign_Code = '11-01POS')
SELECT Version_Code,Version_Name, NumberMailed AS NumberMailed, SubVersion_Code, Event_Barcode,
SUM(TransactionRetailAmtUSD) AS Retail, SUM(TransactionCostAmtUSD) AS Cost, TransactionReferenceNbr_Code, LOC.Location_Code, Market_Code, Market_Name
FROM FactInventoryTransaction T
INNER JOIN DimTransactionType TT ON T.TransactionType_Idx = TT.TransactionType_Idx AND
TransactionType_Code IN ('11', '12', '15', '16', '21', '22')
INNER JOIN PA ON TransactionID = (CASE WHEN TransactionReferenceNbr_Code NOT LIKE '%NC' AND TransactionReferenceNbr_Code NOT LIKE '%OTID1' AND TransactionReferenceNbr_Code NOT LIKE '%0400}' AND TransactionReferenceNbr_Code NOT LIKE '%OTID2' AND TransactionReferenceNbr_Code NOT LIKE '%ESYNC' AND TransactionReferenceNbr_Code NOT LIKE '%1200}' AND TransactionReferenceNbr_Code NOT LIKE '%0100}' AND TransactionReferenceNbr_Code NOT LIKE '%OTID2}'AND TransactionReferenceNbr_Code NOT LIKE '%0300}' AND TransactionReferenceNbr_Code NOT LIKE '%0200}' AND TransactionReferenceNbr_Code NOT LIKE '%1400}'THEN RIGHT(T.TransactionReferenceNbr_Code, LEN(TransactionID)) END) AND
T.Location_Idx = PA.Location_Idx AND
T.Date_Idx = PA.TransactionDate_Idx
INNER JOIN DimItem ITEM ON T.Item_Idx = ITEM.Item_Idx
INNER JOIN DimLocation LOC ON T.Location_Idx = LOC.Location_Idx
WHERE T.Date_Idx >= '10/1/2010'
GROUP BY Version_Code, Version_Name, NumberMailed, SubVersion_Code, Event_Barcode, TransactionReferenceNbr_Code, LOC.Location_Code, Market_Code, Market_Name
October 21, 2010 at 1:57 pm
The attachment has flown the coup. Please reattach. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 2:06 pm
Weird, I show that it's been uploaded, but I did it again. Let me know if you still can't see mkt.zip.
October 22, 2010 at 6:07 am
Right out of the gate, you've got a DISTINCT and a GROUP BY. That's an aggregate operation on an aggregate operation. I'd try to eliminate the DISTINCT from the query.
Looking at the plan... First thing, it's timing out on the optimization process, which is an issue. The only way to reduce that is to simplify the query, which isn't always possible, but it's worth noting. The estimated number of rows is 1 and you're saying that it returns 20,000. That bit of disparity is an indication of bad or missing statistics (usually). It also could be caused by multi-statement table valued functions, but I don't see any here. Make sure your stats are up to date.
You've got multiple table scans and a key lookup. The key lookup is using the idx_FactPriceAudit_Date and it's only returning two columns. If you added these as INCLUDE objects on that index you could eliminate the lookup. DimCampaign is filtering on CampaignCode, but there's no index. I'd put one there. CampignPriceEvent is filtering on CampaignIdx as a table scan, I'd put an index on that one too. DimCampaignVersion is also filtering on CampaignIdx, another index there to eliminate the table scan, probably a cluster on that column on all these tables that are getting scanned because it seems to be the common access path. And, since it appears that these tables don't have a cluster (hence the table scan, instead of a clustered index scan) I'd strongly recommend getting a cluster on them.
Hopefully that's enough to get you started. If you can, post the actual plan because there might be more information available there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2010 at 10:45 am
Thanks so much for the reply! That is more than enough information to get me started. I'll be sure to post an update on what we've done to improve the query.
Lindsay
October 22, 2010 at 10:58 am
Grant Fritchey (10/22/2010)
Looking at the plan... First thing, it's timing out on the optimization process, which is an issue. The only way to reduce that is to simplify the query, which isn't always possible, but it's worth noting.
I must be blind... where the heck does it say that on the plan?
Are you talking about that in more details in your red-gate optimisation ebook? If so, in what chapiter?
October 22, 2010 at 11:03 am
If you right click on the SELECT operator on the plan and choose "Properties" there's a "Reason for Early Termination of Statement Optimization" that has the value "Time Out". I think this is what Grant is referring to.
October 22, 2010 at 11:16 am
Yep. In the properties. And yes, it needs to go in my book. That's one of the MANY things that are going to get added in the rewrite.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2010 at 11:21 am
winash (10/22/2010)
If you right click on the SELECT operator on the plan and choose "Properties" there's a "Reason for Early Termination of Statement Optimization" that has the value "Time Out". I think this is what Grant is referring to.
Wow, that almost feel like a bug.
Why no big red warning like for missing stats / index or missing join?
That's a huge flag that I'll take into consideration from now on!!!
Any way to track that on production in traces or dmvs to find potentially bad plans?
October 22, 2010 at 11:22 am
Grant Fritchey (10/22/2010)
Yep. In the properties. And yes, it needs to go in my book. That's one of the MANY things that are going to get added in the rewrite.
No offense but that R-G FREE ebook was awesome. No need to feel it was too short :hehe:.
October 22, 2010 at 11:39 am
Any way to track that on production in traces or dmvs to find potentially bad plans?
The DMV sys.dm_exec_query_optimizer_info is supposed to have this information - I remember reading some blog that had information on how to extract the reason for early termination from this...my google-fu seems to have abandoned me and all I can locate is this http://www.sqlskills.com/blogs/conor/post/Query-Optimization-DMV-du-jour-sysdm_exec_query_optimizer_info.aspx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply