URGENT CTE SUBQUERY ISSUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

URGENT CTE SUBQUERY ISSUE

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2011-01-05 : 16:48:13

Hi, I have been having major performance issues with a datawarehouse query taking around 30 minutes on to complete. I want to rewrite the query to use CTE Syntax and not quite sure what i am trying to will work at all. The Query I am trying to rewrite looks lsomething like ike this.

SELECT Fact_Inward_Line_Actual_Premium.DWH_Fact_Key ,

( SELECT MIN(Cancel.Cancel_Date)
FROM Fact_Inward_Line_Actual_Premium Fact_cd ,
Dim_Inward_Policy Policy_cd_outer ,
( SELECT Dim_Inward_Policy.Inward_Policy_Natural_Key ,
MIN(CASE WHEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date > Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date
THEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date
ELSE Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date
END) AS Cancel_Date
FROM Fact_Inward_Line_Actual_Premium Min_Fact
INNER JOIN Dim_Inward_Policy ON Min_Fact.Inward_Policy_Key = Dim_Inward_Policy.Inward_Policy_Key
INNER JOIN Dim_Transaction_ConceptOne ON min_fact.Transaction_Key = Dim_Transaction_ConceptOne.Transaction_Key
WHERE Dim_Transaction_ConceptOne.ConceptOne_Procedure_Object_Code = 'Cancel'
AND Min_Fact.Transaction_Effective_Date_Key >= Fact_Inward_Line_Actual_Premium.Transaction_Effective_Date_Key
GROUP BY Dim_Inward_Policy.Inward_Policy_Natural_Key
) Cancel
WHERE Fact_cd.Inward_policy_key = Policy_cd_outer.inward_policy_key
AND Cancel.inward_policy_natural_key = Policy_cd_outer.inward_policy_natural_key
AND Fact_cd.Inward_Participation_Line_Key = Fact_Inward_Line_Actual_Premium.Inward_Participation_Line_Key
) AS cancel_date
from Fact_Inward_Line_Actual_Premium

How I think This should be implemented is by using multiple CTES with a final select ??? However, my sub query won't allow me to contain another sub query references the outer CTE. ANY help would be appreciate

WITH Fact_Inward_Line ( DWH_Fact_Key )
( SELECT Fact_Inward_Line_Actual_Premium.DWH_Fact_Key ,

( SELECT MIN(Cancel.Cancel_Date)
FROM Fact_Inward_Line F ,
Fact_Inward_Line_Actual_Premium Fact_cd ,
Dim_Inward_Policy Policy_cd_outer ,
( SELECT Dim_Inward_Policy.Inward_Policy_Natural_Key ,
MIN(CASE WHEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date > Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date
THEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date
ELSE Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date
END) AS Cancel_Date
FROM Fact_Inward_Line_Actual_Premium Min_Fact
INNER JOIN Dim_Inward_Policy ON Min_Fact.Inward_Policy_Key = Dim_Inward_Policy.Inward_Policy_Key
INNER JOIN Dim_Transaction_ConceptOne ON min_fact.Transaction_Key = Dim_Transaction_ConceptOne.Transaction_Key
WHERE Dim_Transaction_ConceptOne.ConceptOne_Procedure_Object_Code = 'Cancel'
--AND Min_Fact.Transaction_Effective_Date_Key >= dbo.Fact_Inward_Line_Actual_Premium.Transaction_Key
GROUP BY Dim_Inward_Policy.Inward_Policy_Natural_Key
) Cancel
WHERE F.DWH_Fact_Key = Fact_cd.DWH_Fact_Key
AND Fact_cd.Inward_policy_key = Policy_cd_outer.inward_policy_key
AND Cancel.inward_policy_natural_key = Policy_cd_outer.inward_policy_natural_key
AND Fact_cd.Inward_Participation_Line_Key = F.Inward_Participation_Line_Key

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-01-05 : 19:47:38

Your performance issue is not because you need a CTE, it's because that query is written as if someone wanted to make sure they had the worst query performance possible for the results you wanted

If I get some time I'll try to take a look at it, but you have a lot wrong here!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-05 : 20:13:26

Haven't looked at it carefully as it's not very well formatted(I think you'd understand more about the issue if it was better laid out) but I suspect it's running multiple correlated subqueries whoch is never going to perform well.

Try splitting things up using temp tables. This will enforce something that has a possibilitu of being efficient and highlight where the issues are. Once you have done that you can decide whether or not to combine into less queries, materialise some temp table structures or whatever.

Without knowing data sizes and distribution it's difficult to give much advice.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-01-05 : 20:45:31

Without knowing the table structure, I needed to guess on a lot of things, but let me know if this returns what you are looking for.

If it does not, please post some sample data and tables and expected results (NO QUERIES) and it will be much easier to accomidate.

Thanks


SELECT 
a.DWH_Fact_Key 
,b.CancelDate
from 
	Fact_Inward_Line_Actual_Premium a
 Outer Apply 
	(	Select 
		MIN
		(
			CASE 
			WHEN aa.ConceptOne_Resolved_Date > aa.ConceptOne_Revision_Effective_Date THEN aa.ConceptOne_Resolved_Date
			ELSE aa.ConceptOne_Revision_Effective_Date
			END
		) as CancelDate
		From
			Dim_Transaction_ConceptOne aa
			inner join 
			Fact_Inward_Line_Actual_Premium bb
			on aa.Transaction_Key = bb.Transaction_Key
		where 
			bb.ConceptOne_Procedure_Object_Code = 'Cancel'
		and bb.Transaction_Effective_Date_Key >= a.Transaction_Effective_Date_Key
	 ) b

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources