November 24, 2011 at 2:42 am
Hi,
I'm trying to understand how to write good optimized SQL queries and if you guys have a few minutes to spare I would greatly appriciate your help, hints and tips.
Right now I'm trying to understand if the following query could be optimized, and what measures i could take.
I'm using SQL Server 2008 and I have added all indexes suggested by the execution plan.
SELECT
CASE WHEN tr.TransactionType_FK = 1 THEN 'Act' ELSE 'Bud' END AS Scenario,
calendar.Year,
calendar.Month,
CASE WHEN f.TransactionVersion_FK = 191 THEN 'BegBalance' ELSE calendar.MonthNameShort END AS Period,
pEntHier.Prefix + d.FullID AS Entity,
CASE WHEN a.SourceSystem_FK = 13 THEN 'i' + pAccHier.Prefix ELSE pAccHier.Prefix + CASE WHEN
LEFT(a.AccountNumber, 1) IN('3','4') THEN c.ID + '_' ELSE '' END END + a.AccountNumber AS Account,
pCountPartHier.Prefix + cp.ID AS CounterPart,
pProjHier.Prefix + REPLACE(c.ID, 'NOR_019', 'NOR_018') + '_' + p.ID as Project,
cu.ID AS Currency,
f.Currency_FK,
CASE WHEN tr.TransactionType_FK = 2 THEN a.ExchangeRateTypeBudget_FK ELSE a.ExchangeRateType_FK END AS ExchangeRateType_FK,
f.Account_FK,
tr.TransactionType_FK,
f.Department_FK,
f.CounterpartCompany_FK,
f.Project_FK,
f.Company_FK,
SUM(ISNULL([as].Sign, 1) * f.Amount) AS YTDAmount,
GETDATE()
FROM
FactBase.Posting AS f
INNER JOINDimensionBase.Department AS d
ONf.Department_FK = d.Department_FK
INNER JOINDimensionBase.Account AS a
ONf.Account_FK = a.Account_FK
INNER JOINDimensionBase.Company AS c
ONf.Company_FK = c.Company_FK
INNER JOINDimensionBase.[Transaction] AS tr
ONf.TransactionVersion_FK = tr.TransactionVersion_FK
INNER JOINDimensionBase.Currency AS cu
ONf.Currency_FK = cu.Currency_FK
INNER JOIN (SELECT DISTINCT YEAR, MONTH, MonthNameShort
FROM DimensionBase.Calendar AS Calendar_1) AS calendar
ONcalendar.Month >= MONTH(f.PostingDate)
ANDcalendar.Year = f.Year
LEFT OUTER JOINDimensionBase.Project AS p
ON f.Project_FK = p.Project_FK
LEFT OUTER JOINDimensionBase.Company AS cp
ON f.CounterpartCompany_FK = cp.CounterpartCompany_FK
LEFT OUTER JOINHierarchy2011Base.AccountSign AS [as]
ONf.Account_FK = [as].Account_FK
AND [as].HierarchySuper_FK = 14
CROSS JOINMasterdata.GetHierarchyPrefixRow(9,1) AS pProjHier
CROSS JOINMasterdata.GetHierarchyPrefixRow(10,2) AS pCountPartHier
CROSS JOINMasterdata.GetHierarchyPrefixRow(1,3) AS pEntHier
CROSS JOINMasterdata.GetHierarchyPrefixRow(3,2) AS pAccHier
WHERE
f.Year >= 2010
ANDf.TransactionVersion_FK = 192
AND f.SourceSystem_FK > 0
AND tr.TransactionType_FK IN (1,2,5)
AND c.Country_FK = 31
AND f.Amount <> 0
OR
f.Year >= 2010
ANDf.TransactionVersion_FK = 192
AND f.SourceSystem_FK > 0
AND tr.TransactionType_FK IN (1,2,5)
AND c.Country_FK = 31
AND f.Amount <> 0
AND NOT (LEFT(a.AccountNumer, 1) IN ('1','2'))
GROUP BY
CASE WHEN tr.TransactionType_FK = 1 THEN 'Act' ELSE 'Bud' END,
calendar.Year,
calendar.Month,
CASE WHEN f.TransactionVersion_FK = 191 THEN 'BegBalance' ELSE calendar.MonthNameShort END,
pEntHier.Prefix + d.FullID,
CASE WHEN a.SourceSystem_FK = 13 THEN 'i' + pAccHier.Prefix ELSE pAccHier.Prefix + CASE WHEN
LEFT(a.AccountNumber, 1) IN('3','4') THEN c.ID + '_' ELSE '' END END + a.AccountNumber,
pCountPartHier.Prefix + cp.ID,
pProjHier.Prefix + REPLACE(c.ID, 'NOR_019', 'NOR_018') + '_' + p.ID,
cu.ID AS Currency,
f.Currency_FK,
CASE WHEN tr.TransactionType_FK = 2 THEN a.ExchangeRateTypeBudget_FK ELSE a.ExchangeRateType_FK END,
f.Account_FK,
tr.TransactionType_FK,
f.Department_FK,
f.CounterpartCompany_FK,
f.Project_FK,
f.Company_FK,
As you can probably see this is a star-scheme type tables. With FactBase.Posting as the fact table containing millions of rows. DimensionTables have only a few rows each (10-10000 typically).
Masterdata.GetHierarchyPrefixRow() is a function that will return one row of varchar type.
Thank you for any helpful input at all. This statement is pretty slow so any help on speeding it up would help greatly!
November 24, 2011 at 4:16 am
Hello and welcome to SSC!
The main reason that you are not getting any replies for your question, it that we don't have enough information from you to answer!
For starters, it seems that your readily consumable sample data and actual execution plans have fallen off your post. Or perhaps you were unaware of the benefits of providing these things?
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts.
Also, as this is a performance issue, you could do with reading through this article[/url] in addition to the previous one I mentioned. This will explain all of the information that we need from you to help tune your query.
These items will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks.
November 24, 2011 at 4:28 am
Thanks for your reply!
Sorry for not providing the necessary information. I will post back with this soon!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply