Tune the Stored Procedure

Question

Tune the Stored Procedure

dm_tapas

Hall of Fame

Points: 3515
More actions
November 2, 2011 at 12:18 am

#247040

I want to combine the below mention two Update statements to one statement.
How is it possible. somebody please help.
Thanks,
CREATE TABLE #Temp2(PRID INT,LOCID INT,Rev_CatID INT,MSTTYPE CHAR(1),
AGGTracked INT,AGGDE1 INT,AGGDE2 INT,AGGUpdateEntry INT,AGGPagesReviwed INT,AGGSenttoImageReview INT,
AGGReplaced INT,AGGDuplicate INT,AGGReIndexed INT,AGGDeleted INT)
--Inserting into Temp table PRID,LocID,Rev_CatID,MSTType remaining all with '0'
INSERT INTO #Temp2
SELECT PRID,LOCID,Rev_CatID,MSTTYPE,0,0,0,0,0,0,0,0,0,0
FROM #TEMP T
GROUP BY T.PRID, T.LOCID,T.Rev_CatID, T.MSTTYPE
ORDER BY T.PRID, T.LOCID,T.Rev_CatID
--Updating the Temp table of AGGTracked Column with locID is null
UPDATE #TEMP2 SET AGGTracked=A.AGGTracked FROM
(SELECT COUNT(PRID) as AGGTracked,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='New' AND PEEvent = 'New'
group by PRID,LOCID,Rev_CatID,MSTTYPE) A
INNER JOIN #TEMP2 B ON A.PRID = B.PRID AND A.LOCID is null and b.locid is null AND A.Rev_CatID = B.Rev_CatID AND A.MSTTYPE = B.MSTTYPE
--Updating the Temp table of AGGTracked Column with locID is not null
UPDATE #TEMP2 SET AGGTracked=A.AGGTracked FROM
(SELECT COUNT(PRID) as AGGTracked,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='New' AND PEEvent = 'New'
group by PRID,LOCID,Rev_CatID,MSTTYPE) A
INNER JOIN #TEMP2 B ON A.PRID = B.PRID AND A.LOCID=B.LOCID AND A.Rev_CatID = B.Rev_CatID AND A.MSTTYPE = B.MSTTYPE

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Tried this?:

UPDATE #TEMP2 SET AGGTracked=A.AGGTracked FROM
(SELECT COUNT(PRID) as AGGTracked,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='New' AND PEEvent = 'New'
group by PRID,LOCID,Rev_CatID,MSTTYPE) A
INNER JOIN #TEMP2 B ON A.PRID = B.PRID AND (A.LOCID=B.LOCID OR (A.LOCID is null and b.locid is null)) AND A.Rev_CatID = B.Rev_CatID AND A.MSTTYPE = B.MSTTYPE

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ivanaldana Valued Member Points: 60 More actions · Answer 2

Did you try this?

CREATE TABLE #Temp2(PRID INT,LOCID INT,Rev_CatID INT,MSTTYPE CHAR(1),
AGGTracked INT,AGGDE1 INT,AGGDE2 INT,AGGUpdateEntry INT,AGGPagesReviwed INT,AGGSenttoImageReview INT,
AGGReplaced INT,AGGDuplicate INT,AGGReIndexed INT,AGGDeleted INT)
INSERT INTO #Temp2
SELECT PRID,LOCID,Rev_CatID,MSTTYPE,
SUM(CASE WHEN MCMetric ='New' AND PEEvent = 'New' THEN 1 ELSE 0 END) AS AGGTracked
,0,0,0,0,0,0,0,0,0
FROM #TEMP T
GROUP BY T.PRID, T.LOCID,T.Rev_CatID, T.MSTTYPE
ORDER BY T.PRID, T.LOCID,T.Rev_CatID

dm_tapas Hall of Fame Points: 3515 More actions · Answer 3

Thank you for your reply,

This issue has been solved.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Your issue may have been resolved but there is something that no one has pointed out yet. Do NOT do this, as it is work for no purpose:

--Inserting into Temp table PRID,LocID,Rev_CatID,MSTType remaining all with '0'

INSERT INTO #Temp2

SELECT PRID,LOCID,Rev_CatID,MSTTYPE,0,0,0,0,0,0,0,0,0,0

FROM #TEMP T

GROUP BY T.PRID, T.LOCID,T.Rev_CatID, T.MSTTYPE

ORDER BY T.PRID, T.LOCID,T.Rev_CatID

Inserting into a table with an order by causes a sort but does NOT help anything that happens with that data. If the table doesn't have a clustered index on the order of the order by or an identity column where you need the identity order to be specific you have wasted effort here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

dm_tapas Hall of Fame Points: 3515 More actions · Answer 5

dm_tapas

Hall of Fame

Points: 3515

November 14, 2011 at 12:59 am

#1406949

Thanks for your suggestion.