February 10, 2012 at 8:51 am
estimated group by plan attached. Same problem here. execution with actual plan never finishes..
February 10, 2012 at 10:18 am
The most expensive operation by far is the sort before the aggregation. I'd like to see what happens if you force a HASH MATCH for the aggregation. Can you add OPTION (HASH GROUP) to the end of the query, see if that helps, and at least post the execution plan?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 10, 2012 at 11:07 am
Here's a slightly different approach using a in-line table-valued function (itvf).
I don't know if it will even compile since I have nothing to test against (your sample data require a Masterdata schema...).
The concept in general: instead of using a scalar valued function wrapped by a multi-statement table-valued function I tried to convert it into an itvf. The first step should be to verify if this conversion returns the same results.
The next change is to use a subquery (or cte, in this case) and apply the function to the reduced result.
CREATE FUNCTION [Masterdata].[GetHierarchyPrefixRow_itvf]
(
-- Add the parameters for the function here
@hierarchy_FK int,
@hierarchyLevel smallint
)
RETURNS TABLE
AS RETURN
(
WITH cte AS
(SELECT
CASE WHEN HL.ShowHierarchyPrefix = 1 THEN H.Prefix ELSE '' END
+ CASE WHEN HL.ShowHierarchyPrefix = 1 AND HL.ShowHierarchyLevelPrefix = 1 THEN '.' ELSE '' END
+ CASE WHEN HL.ShowHierarchyLevelPrefix = 1 THEN HL.Prefix ELSE '' END
+ CASE WHEN HL.ShowHierarchyLevelPrefix = 1 AND HL.ShowDimensionPrefix =1 THEN '.' ELSE '' END
+ CASE WHEN HL.ShowDimensionPrefix = 1 THEN D.Prefix ELSE '' END
+ CASE WHEN HL.ShowHierarchyPrefix = 1
OR HL.ShowHierarchyLevelPrefix = 1
OR HL.ShowDimensionPrefix = 1 THEN '.' ELSE '' END
AS resultPrefix
FROM Masterdata.Hierarchy H INNER JOIN
Masterdata.HierarchyLevel HL ON H.Hierarchy_PK = HL.Hierarchy_FK LEFT OUTER JOIN
Masterdata.Dimension D ON HL.Dimension_FK = D.Dimension_PK
WHERE (H.Hierarchy_PK = @hierarchy_FK) AND (HL.[Level] = @hierarchyLevel)
)
SELECT
ISNULL(resultPrefix,'Error Hierarchy level undefined') AS Prefix
FROM cte
)
GO
;
WITH cte AS
(
SELECT
CASE WHEN y.TransactionType_FK IN (1, 5) THEN 'Act' ELSE 'Bud' END AS Scenario,
CASE WHEN y.TransactionType_FK = 5 THEN 'YTD Journals Load' ELSE 'YTD Load' END AS [View],
cp.ID AS cp_id,
Year,
Month,
Period,
d.FullID AS d_FullID,
a.SourceSystem_FK,
a.AccountNumber,
pc.ID AS pc_id,
p.ID AS p_id,
cu.ID AS Currency,
y.Currency_FK,
y.ExchangeRateType_FK,
SUM(Amount_YTD) AS Amount_YTD
FROM
Fact2012Base.PostingYTD AS y
LEFT OUTER JOIN
DimensionBase.Project AS p
ON p.Project_FK = y.Project_FK
INNER JOIN
DimensionBase.Company AS pc
ON p.OwnerCompany_FK = pc.Company_FK
INNER JOIN
DimensionBase.Department AS d
ON y.Department_FK = d.Department_FK
INNER JOIN
DimensionBase.Account AS a
ON y.Account_FK = a.Account_FK
INNER JOIN
DimensionBase.Company AS c
ON y.Company_FK = c.Company_FK
AND y.Currency_FK = c.Currency_FK
INNER JOIN
DimensionBase.Currency AS cu
ON y.Currency_FK = cu.Currency_FK
LEFT OUTER JOIN
DimensionBase.Company AS cp
ON y.CounterpartCompany_FK = cp.Company_FK
WHERE
(y.Year >= 2010)
AND (y.TransactionVersion_FK <> 192)
AND (y.SourceSystem_FK > 0)
AND (y.TransactionType_FK IN (1, 2, 5))
AND (c.Country_FK = 31)
AND (y.Amount_YTD <> 0)
)
SELECT
cte.*,
pCountPartHier.Prefix + cp_id AS CounterPart,
pEntHier.Prefix + d_FullID AS Entity,
CASE WHEN cte.SourceSystem_FK = 13 THEN 'i' + pAccHier.Prefix ELSE pAccHier.Prefix + CASE WHEN LEFT(cte.AccountNumber, 1)
IN ('3', '4') THEN c.ID + '_' ELSE '' END END + cte.AccountNumber AS Account,
pProjHier.Prefix + pc_ID + '_' + p_ID AS Project
FROM cte
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](10, 2) AS pCountPartHier
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](1, 3) AS pEntHier
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](3, 2) AS pAccHier
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](9, 1) AS pProjHier
GROUP BY
CASE WHEN cte.TransactionType_FK IN (1, 5) THEN 'Act' ELSE 'Bud' END,
CASE WHEN cte.TransactionType_FK = 5 THEN 'YTD Journals Load' ELSE 'YTD Load' END,
Year,
Month,
Period,
pEntHier.Prefix + d_FullID,
CASE WHEN SourceSystem_FK = 13 THEN 'i' + pAccHier.Prefix ELSE pAccHier.Prefix + CASE WHEN LEFT(AccountNumber, 1)
IN ('3', '4') THEN c.ID + '_' ELSE '' END END + AccountNumber,
pCountPartHier.Prefix + cp_ID,
pProjHier.Prefix + pc_ID + '_' + p_ID,
cu_ID,
Currency_FK,
ExchangeRateType_FK
February 10, 2012 at 12:06 pm
Have you looked at computed columns to render the contents of the CASE? They all look to be deterministic calculations, so you could even include the "post-calc" values in your indexing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2012 at 4:56 am
LutzM (2/10/2012)
Here's a slightly different approach using a in-line table-valued function (itvf).I don't know if it will even compile since I have nothing to test against (your sample data require a Masterdata schema...).
The concept in general: instead of using a scalar valued function wrapped by a multi-statement table-valued function I tried to convert it into an itvf. The first step should be to verify if this conversion returns the same results.
The next change is to use a subquery (or cte, in this case) and apply the function to the reduced result.
CREATE FUNCTION [Masterdata].[GetHierarchyPrefixRow_itvf](
-- Add the parameters for the function here
@hierarchy_FK int,
@hierarchyLevel smallint
)
RETURNS TABLE
AS RETURN
(
WITH cte AS
(SELECT
CASE WHEN HL.ShowHierarchyPrefix = 1 THEN H.Prefix ELSE '' END
+ CASE WHEN HL.ShowHierarchyPrefix = 1 AND HL.ShowHierarchyLevelPrefix = 1 THEN '.' ELSE '' END
+ CASE WHEN HL.ShowHierarchyLevelPrefix = 1 THEN HL.Prefix ELSE '' END
+ CASE WHEN HL.ShowHierarchyLevelPrefix = 1 AND HL.ShowDimensionPrefix =1 THEN '.' ELSE '' END
+ CASE WHEN HL.ShowDimensionPrefix = 1 THEN D.Prefix ELSE '' END
+ CASE WHEN HL.ShowHierarchyPrefix = 1
OR HL.ShowHierarchyLevelPrefix = 1
OR HL.ShowDimensionPrefix = 1 THEN '.' ELSE '' END
AS resultPrefix
FROM Masterdata.Hierarchy H INNER JOIN
Masterdata.HierarchyLevel HL ON H.Hierarchy_PK = HL.Hierarchy_FK LEFT OUTER JOIN
Masterdata.Dimension D ON HL.Dimension_FK = D.Dimension_PK
WHERE (H.Hierarchy_PK = @hierarchy_FK) AND (HL.[Level] = @hierarchyLevel)
)
SELECT
ISNULL(resultPrefix,'Error Hierarchy level undefined') AS Prefix
FROM cte
)
GO
;
WITH cte AS
(
SELECT
CASE WHEN y.TransactionType_FK IN (1, 5) THEN 'Act' ELSE 'Bud' END AS Scenario,
CASE WHEN y.TransactionType_FK = 5 THEN 'YTD Journals Load' ELSE 'YTD Load' END AS [View],
cp.ID AS cp_id,
Year,
Month,
Period,
d.FullID AS d_FullID,
a.SourceSystem_FK,
a.AccountNumber,
pc.ID AS pc_id,
p.ID AS p_id,
cu.ID AS Currency,
y.Currency_FK,
y.ExchangeRateType_FK,
SUM(Amount_YTD) AS Amount_YTD
FROM
Fact2012Base.PostingYTD AS y
LEFT OUTER JOIN
DimensionBase.Project AS p
ON p.Project_FK = y.Project_FK
INNER JOIN
DimensionBase.Company AS pc
ON p.OwnerCompany_FK = pc.Company_FK
INNER JOIN
DimensionBase.Department AS d
ON y.Department_FK = d.Department_FK
INNER JOIN
DimensionBase.Account AS a
ON y.Account_FK = a.Account_FK
INNER JOIN
DimensionBase.Company AS c
ON y.Company_FK = c.Company_FK
AND y.Currency_FK = c.Currency_FK
INNER JOIN
DimensionBase.Currency AS cu
ON y.Currency_FK = cu.Currency_FK
LEFT OUTER JOIN
DimensionBase.Company AS cp
ON y.CounterpartCompany_FK = cp.Company_FK
WHERE
(y.Year >= 2010)
AND (y.TransactionVersion_FK <> 192)
AND (y.SourceSystem_FK > 0)
AND (y.TransactionType_FK IN (1, 2, 5))
AND (c.Country_FK = 31)
AND (y.Amount_YTD <> 0)
)
SELECT
cte.*,
pCountPartHier.Prefix + cp_id AS CounterPart,
pEntHier.Prefix + d_FullID AS Entity,
CASE WHEN cte.SourceSystem_FK = 13 THEN 'i' + pAccHier.Prefix ELSE pAccHier.Prefix + CASE WHEN LEFT(cte.AccountNumber, 1)
IN ('3', '4') THEN c.ID + '_' ELSE '' END END + cte.AccountNumber AS Account,
pProjHier.Prefix + pc_ID + '_' + p_ID AS Project
FROM cte
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](10, 2) AS pCountPartHier
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](1, 3) AS pEntHier
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](3, 2) AS pAccHier
CROSS APPLY Masterdata.[GetHierarchyPrefixRow_itvf](9, 1) AS pProjHier
GROUP BY
CASE WHEN cte.TransactionType_FK IN (1, 5) THEN 'Act' ELSE 'Bud' END,
CASE WHEN cte.TransactionType_FK = 5 THEN 'YTD Journals Load' ELSE 'YTD Load' END,
Year,
Month,
Period,
pEntHier.Prefix + d_FullID,
CASE WHEN SourceSystem_FK = 13 THEN 'i' + pAccHier.Prefix ELSE pAccHier.Prefix + CASE WHEN LEFT(AccountNumber, 1)
IN ('3', '4') THEN c.ID + '_' ELSE '' END END + AccountNumber,
pCountPartHier.Prefix + cp_ID,
pProjHier.Prefix + pc_ID + '_' + p_ID,
cu_ID,
Currency_FK,
ExchangeRateType_FK
Thank you very much LutzM!
I did not get the time to look into before now. But in the mean time the number of results return by the original query was doubled and the execution-time went up to aprox 5 mins.
I tried your approach and it improved things quite a lot! From 5 mins to 1 min. So thats a great improvment!
Attached is the actual execution-plan after implementing LutzM solution. I think i will accept this one as the final solution for now 🙂
February 28, 2012 at 10:17 am
Looking at the execution plan it seems like the statistics for PostingYTD are "slightly" off (estimated 43k rows vs. actual 2.000k rows).
Is there any index maintenance performed regulary?
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply