February 10, 2012 at 2:22 am
Hi,
I've been asked to adjust my query by using CTE instead of #temp tables as it is slowing down the entire network.
The reason I used #temp tables was to break down my query into simple an understandable parts for anyone reading my query.
I create the first #temp table and then select into the second one. I then drop the first one. I will then Select everything in the second one as well as additional columns into a third #temp table and then drop the second one and so on. I have about 30 #temp tables.
Here is an example of 3 of them I have. I'm hoping for a simple way to use CTE to join these 4 and then the remaining 27 or so.
Thanks!
Select
al.ProductLine
,al.PaymentFrequency
,al.PolicyVersionID
,al.PolicyID
,al.FirstStartDate
,al.NameID
,(1-((1-(ISNULL(al.DiscountPercent,0)/100))*(1-(ISNULL(al.AdditionalDiscountForRenewalAdjustments,0)/100)))) Discount
,ccx.NameID
,ccx.ClaCaseID
,ccx.ClaEventID
,ObjectVersionID
,ccx.PolicyLineVersionID
,ccx.PolicyLineID
into #InitialTable_1
from Staging.ClaCases ccx
LEFT JOIN Staging.PolicyLines al
ON ccx.PolicyLineVersionID = al.PolicyLineVersionID
where ProductLine = 'WHEEL'
--***************************************************************************************
Select it.*
,ce.IncidentDate
,ce.EventType
,ce.SubPlaceID Incident_SubPlaceID
,ce.DayAddrCode
,ce.RiskAddrCode
into #InitialTable_2
from #InitialTable_1 it
LEFT JOIN Staging.ClaEvents ce
ON it.ClaEventID = ce.ClaEventID
GO
Drop Table #InitialTable_1
GO
--***************************************************************************************
Select it.*
,ISNULL((select z.Previous_claims
from
(
select ca.NameID
,ce.IncidentDate
,ca.ClaCaseID
,(
select count(*)
from Staging.ClaCases ca2
, Staging.ClaEvents ce2
where ca2.ClaEventID = ce2.ClaEventID
and ca2.NameID = ca.NameID
and ce2.IncidentDate < ce.IncidentDate
) as Previous_claims
from Staging.ClaCases ca
, Staging.ClaEvents ce
where ca.ClaEventID = ce.ClaEventID
) z
where z.ClaCaseID = it.ClaCaseID),0) Previous_claims
into #InitialTable_3
from #InitialTable_2 it
GO
Drop Table #InitialTable_2
GO
--***************************************************************************************
;WITH CTE AS (
SELECT ITCScoreDate, NameID, ITCScore,
ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY CASE WHEN ITCScore IS NULL
THEN ITCScoreDate
ELSE GETDATE() END DESC) AS rn
from Staging.ITCChecks)
SELECT it.*, ITCScore
into #InitialTable_4
from #InitialTable_3 it
LEFT JOIN (
SELECT ITCScoreDate, NameID, ITCScore
from CTE
where rn = 1
) cteJoin
ON it.NameID = cteJoin.NameID
GO
Drop Table #InitialTable_3
--***************************************************************************************
Alter Table #InitialTable_4
ADD
Distance_to_work varchar(100)
GO
MERGE into #InitialTable_4 dst --into clause to specify the target table
USING ( --USING clause to specify the source of the data to be updated or inserted
SELECT ff.ObjectVersionID --Subquery
, MIN(ISNULL( (d.CentreX),0)) AS day_x
, MIN(ISNULL( (d.CentreY),0)) AS day_y
, MIN(ISNULL( (n.CentreX),0)) AS night_x
, MIN(ISNULL( (n.CentreY),0)) AS night_y
from #InitialTable_4 ff
LEFT JOIN Geo.GISAreas d ON ff.DayAddrCode = d.SubplaceID --ON clause to specify the condition upon which the MERGE operation either updates or inserts
LEFT JOIN Geo.GISAreas n ON ff.RiskAddrCode = n.SubplaceID
GROUP BY ff.ObjectVersionID
) src
ON (src.ObjectVersionID = dst.ObjectVersionID)
WHEN MATCHED THEN
UPDATE SET dst.Distance_to_work = 100 * SQRT ( POWER (src.day_x - src.night_x, 2)
+ POWER (src.day_y - src.night_y, 2)
);
GO
February 10, 2012 at 2:37 am
I can't work out what your question is here. The way to use multiple CTEs is like this:
WITH CTE1 AS (
SELECT...
FROM...
WHERE...
), CTE2 AS (
SELECT...
FROM...
WHERE...
)
<Now use your CTEs here the same way you would use temp tables>
I notice that you said you'd been asked to change to CTEs. You will want to do comprehensive testing to make sure that doing so actually produces an improvement.
John
February 10, 2012 at 2:47 am
I'd be surprised if it makes an improvement.
Without DDL, sample data and expected results, all I'm going to do is directly convert your temp tables into CTEs. If you want a better answer, read the link in my sig and post DDL with readily consumable sample data and expected results.
WITH InitialTable_1
AS (
SELECT al.ProductLine, al.PaymentFrequency, al.PolicyVersionID, al.PolicyID, al.FirstStartDate, al.NameID, (1 - ((1 - (ISNULL(al.DiscountPercent, 0) / 100)) * (1 - (ISNULL(al.AdditionalDiscountForRenewalAdjustments, 0) / 100)))) Discount, ccx.NameID, ccx.ClaCaseID, ccx.ClaEventID, ObjectVersionID, ccx.PolicyLineVersionID, ccx.PolicyLineID
FROM Staging.ClaCases ccx
LEFT JOIN Staging.PolicyLines al ON ccx.PolicyLineVersionID = al.PolicyLineVersionID
WHERE ProductLine = 'WHEEL'
), InitialTable_2
AS (
SELECT it.*, ce.IncidentDate, ce.EventType, ce.SubPlaceID Incident_SubPlaceID, ce.DayAddrCode, ce.RiskAddrCode
FROM InitialTable_1 it
LEFT JOIN Staging.ClaEvents ce ON it.ClaEventID = ce.ClaEventID
), InitialTable_3
AS (
SELECT it.*, ISNULL((
SELECT z.Previous_claims
FROM (
SELECT ca.NameID, ce.IncidentDate, ca.ClaCaseID, (
SELECT count(*)
FROM Staging.ClaCases ca2, Staging.ClaEvents ce2
WHERE ca2.ClaEventID = ce2.ClaEventID
AND ca2.NameID = ca.NameID
AND ce2.IncidentDate < ce.IncidentDate
) AS Previous_claims
FROM Staging.ClaCases ca, Staging.ClaEvents ce
WHERE ca.ClaEventID = ce.ClaEventID
) z
WHERE z.ClaCaseID = it.ClaCaseID
), 0) Previous_claims
FROM InitialTable_2 it
), CTE
AS (
SELECT ITCScoreDate, NameID, ITCScore, ROW_NUMBER() OVER (
PARTITION BY NameID ORDER BY CASE
WHEN ITCScore IS NULL
THEN ITCScoreDate
ELSE GETDATE()
END DESC
) AS rn
FROM Staging.ITCChecks
)
SELECT it.*, ITCScore
INTO #InitialTable_4
FROM InitialTable_3 it
LEFT JOIN (
SELECT ITCScoreDate, NameID, ITCScore
FROM CTE
WHERE rn = 1
) cteJoin ON it.NameID = cteJoin.NameID
GO
ALTER TABLE #InitialTable_4 ADD Distance_to_work VARCHAR(100)
GO
MERGE INTO #InitialTable_4 dst --into clause to specify the target table
USING (
--USING clause to specify the source of the data to be updated or inserted
SELECT ff.ObjectVersionID --Subquery
, MIN(ISNULL((d.CentreX), 0)) AS day_x, MIN(ISNULL((d.CentreY), 0)) AS day_y, MIN(ISNULL((n.CentreX), 0)) AS night_x, MIN(ISNULL((n.CentreY), 0)) AS night_y
FROM #InitialTable_4 ff
LEFT JOIN Geo.GISAreas d ON ff.DayAddrCode = d.SubplaceID --ON clause to specify the condition upon which the MERGE operation either updates or inserts
LEFT JOIN Geo.GISAreas n ON ff.RiskAddrCode = n.SubplaceID
GROUP BY ff.ObjectVersionID
) src
ON (src.ObjectVersionID = dst.ObjectVersionID)
WHEN MATCHED
THEN
UPDATE
SET dst.Distance_to_work = 100 * SQRT(POWER(src.day_x - src.night_x, 2) + POWER(src.day_y - src.night_y, 2));
GO
February 10, 2012 at 3:19 am
mic.con87 (2/10/2012)
Hi,I've been asked to adjust my query by using CTE instead of #temp tables as it is slowing down the entire network.
...
I have a question.
Why are temp tables slowing down the network?
February 10, 2012 at 3:29 am
Thanks for the post Cadavre, that answers my question. I'll read up on what you posted
February 10, 2012 at 3:33 am
Jcb im no expert but I was told that even though I drop the temp tables they sit in a TempDB and the drive that they sit on is almost at full capacity. Something about it being stored in memory or a cache, I'm really no expert.
February 10, 2012 at 4:39 am
When you create temp tables it lies in the tempdb.
When you drop temp tables or closes the connection they are erased from tempdb.
When you query needs to create "behind the scenes" datasets it uses memory/tempdb.
I cannot figures yet temp tables impact in network.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply