Stored procedure performance

Question

Post reply

Stored procedure performance

dm_tapas

Hall of Fame

Points: 3515
More actions
November 1, 2011 at 4:46 am

#246014

The below stored procedure is taking around 1 hours to execute. but it should not take this much time.
Can some body tune this and tell me where is the problem....?...
I don't find any..
I am helpless regarding this. somebody help!!!!!
Thanks in advance.
Please copy it to your SSMS and try to tune it . (SQL SERVER 2005)
====================================================
USE [CRFTracking]
GO
/****** Object: StoredProcedure [dbo].[RPTAggregateDataUpdate] Script Date: 11/01/2011 11:44:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------
-- Purpose
-- To Insert/Delete or Update the "RPTAggregate" table.
--------------------------------------------------------------
CREATE PROCEDURE [dbo].[RPTAggregateDataUpdate]
(
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL
)
AS
Begin
SET NOCOUNT ON
DECLARE @MAXAGGForDate DATETIME
-- If @StartDate is Null taking the last run date as @StartDate from 'RPTAggregate' table.
IF @StartDate IS NULL
BEGIN
SELECT @MAXAGGForDate = isnull(CONVERT(NVARCHAR(12),MAX(AGGForDate),110),'09-19-2008') FROM RPTAggregate
SET @StartDate = CONVERT(NVARCHAR(12),@MAXAGGForDate+1,110) + ' 00:00:00'
END
ELSE
BEGIN
SET @StartDate = CONVERT(NVARCHAR(12),@StartDate,110) + ' 00:00:00'
END
-- If @EndDate is Null taking the today's date as @EndDate.
DECLARE @TodayDate datetime
IF @EndDate IS NULL
BEGIN
SELECT @TodayDate= CONVERT(NVARCHAR(12),CURRENT_TIMESTAMP,110) + ' 00:00:00'
END
ELSE
BEGIN
SELECT @TodayDate= CONVERT(NVARCHAR(12),@EndDate+1,110) + ' 00:00:00'
END
-- Deleteing the records between @StartDate and @EndDate for Re-Inserting records to 'RPTAggregate' table.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
BEGIN
SElECT * FROM RPTAggregate WHERE CONVERT(NVARCHAR(12),AGGForDate,110) BETWEEN CONVERT(NVARCHAR(12),@StartDate,110) AND CONVERT(NVARCHAR(12),@EndDate,110)
END
ELSE IF @StartDate IS NOT NULL
BEGIN
DELETE FROM RPTAggregate WHERE AGGForDate >= @StartDate
END
WHILE(@StartDate < @TodayDate)
BEGIN
CREATE TABLE #Temp
(PAGEID INT,PEEvent nvarchar(100),PRID INT,LOCID INT,Rev_CatID INT,MSTTYPE CHAR(1),
PAGEActive bit,SCID INT,MCID INT,MCMetric nvarchar(50),pedone datetime,pedoneby varchar(32))
INSERT INTO #Temp
SELECT DISTINCT PE.PAGEID ,PE.PEEvent ,SP.PRID,U.LOCID,PMP.CATID AS Rev_CatID,
SC.MSTTYPE,P.PAGEActive,
PE.SCID ,MC.MCID ,MC.MCMetric,pe.pedone,pe.pedoneby
FROM CRFPAGEEVENTS PE
JOIN CRFSUBJECTPAGESTATUS SP ON PE.PageID = SP.PageID
JOIN CRFPages P on P.PageID = SP.PageID
JOIN CRFProjectMasterPages PMP ON SP.MSTID = PMP.MSTID
Left JOIN CRFRevenueCategories RC ON RC.CATID = PMP.CATID
JOIN CRFActiveProjects AP ON AP.PRID = SP.PRID
JOIN CRFStatuscodes SC ON SC.SCID = PE.SCID
JOIN CRFMetricCategory MC ON MC.MCID=SC.MCID
LEFT JOIN DBO.GDRUSERS U ON U.USERNAME = PE.PEDONEBY
WHERE CONVERT(NVARCHAR(12),PE.PEDONE,110) = @StartDate AND SC.CATID = 1
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
--Updating the Temp table of AGGTracked Column with locID is null
--DCF Pagetypes
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.STD'
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.STD'
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
--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.CLIN'
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.CLIN'
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
--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.EXT'
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.EXT'
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
--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.SAE'
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.SAE'
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
--Updating the Temp table of AGGDE1 Column with locID is not null
UPDATE #TEMP2 SET AGGDE1=A.AGGDE1 FROM
(SELECT COUNT(PRID) as AGGDE1,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DE1' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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
--Updating the Temp table of AGGDE1 Column with locID is null
UPDATE #TEMP2 SET AGGDE1=A.AGGDE1 FROM
(SELECT COUNT(PRID) as AGGDE1,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DE1' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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 AGGDE2 Column with locID is not null
UPDATE #TEMP2 SET AGGDE2=A.AGGDE2 FROM
(SELECT COUNT(PRID) as AGGDE2,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DE2' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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
--Updating the Temp table of AGGDE2 Column with locID is null
UPDATE #TEMP2 SET AGGDE2=A.AGGDE2 FROM
(SELECT COUNT(PRID) as AGGDE2,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DE2' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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 AGGUpdateEntry Column with locID is not null
--CRF Page Type
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='EE' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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
--Updating the Temp table of AGGUpdateEntry Column with locID is null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='EE' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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 AGGUpdateEntry Column with locID is not null
--DBCF Page Type
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DE' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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
--Updating the Temp table of AGGUpdateEntry Column with locID is null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DE' AND (PEEvent = 'ImageEntry.Entered' OR PEEvent = 'ImageEntry.Blank')
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 AGGUpdateEntry Column with locID is not null
--DCF Page Type
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DSE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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
--Updating the Temp table of AGGUpdateEntry Column with locID is null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DSE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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 AGGUpdateEntry Column with locID is not null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DCE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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
--Updating the Temp table of AGGUpdateEntry Column with locID is null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DCE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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 AGGUpdateEntry Column with locID is not null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DEE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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
--Updating the Temp table of AGGUpdateEntry Column with locID is null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DEE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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 AGGUpdateEntry Column with locID is not null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DAE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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
--Updating the Temp table of AGGUpdateEntry Column with locID is null
UPDATE #TEMP2 SET AGGUpdateEntry=A.AGGUpdateEntry FROM
(SELECT COUNT(PRID) as AGGUpdateEntry,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DAE' AND (PEEvent = 'DCFDataEntry.Entered' OR PEEvent = 'DCFDataEntry.Blank')
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 AGGPagesReviwed Column with locID is not null
UPDATE #TEMP2 SET AGGPagesReviwed =A.AGGPagesReviwed FROM
(SELECT COUNT(PRID) as AGGPagesReviwed,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DR' AND PEEvent = 'CaseReview.Reviewed'
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
--Updating the Temp table of AGGPagesReviwed Column with locID is null
UPDATE #TEMP2 SET AGGPagesReviwed =A.AGGPagesReviwed FROM
(SELECT COUNT(PRID) as AGGPagesReviwed,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric ='DR' AND PEEvent = 'CaseReview.Reviewed'
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 AGGSenttoImageReview Column with locID is not null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE PEEvent = 'ImageEntry.Review'
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
--Updating the Temp table of AGGSenttoImageReview Column with locID is null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE PEEvent = 'ImageEntry.Review'
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 AGGSenttoImageReview Column with locID is not null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DSE' AND PEEvent = 'DCFDataEntry.Review'
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
--Updating the Temp table of AGGSenttoImageReview Column with locID is null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DSE' AND PEEvent = 'DCFDataEntry.Review'
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 AGGSenttoImageReview Column with locID is not null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DCE' AND PEEvent = 'DCFDataEntry.Review'
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
--Updating the Temp table of AGGSenttoImageReview Column with locID is null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DCE' AND PEEvent = 'DCFDataEntry.Review'
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 AGGSenttoImageReview Column with locID is not null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DEE' AND PEEvent = 'DCFDataEntry.Review'
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
--Updating the Temp table of AGGSenttoImageReview Column with locID is null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DEE' AND PEEvent = 'DCFDataEntry.Review'
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 AGGSenttoImageReview Column with locID is not null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DAE' AND PEEvent = 'DCFDataEntry.Review'
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
--Updating the Temp table of AGGSenttoImageReview Column with locID is null
UPDATE #TEMP2 SET AGGSenttoImageReview =A.AGGSenttoImageReview FROM
(SELECT COUNT(PRID) as AGGSenttoImageReview,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'DAE' AND PEEvent = 'DCFDataEntry.Review'
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 AGGReplaced Column with locID is not null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IR'
AND PEEvent = 'ImageReview.Replacement'
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
--Updating the Temp table of AGGReplaced Column with locID is null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IR'
AND PEEvent = 'ImageReview.Replacement'
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 AGGReplaced Column with locID is not null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ISR'
AND PEEvent = 'ImageReview.Replacement'
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
--Updating the Temp table of AGGReplaced Column with locID is null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ISR'
AND PEEvent = 'ImageReview.Replacement'
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 AGGReplaced Column with locID is not null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ICR'
AND PEEvent = 'ImageReview.Replacement'
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
--Updating the Temp table of AGGReplaced Column with locID is null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ICR'
AND PEEvent = 'ImageReview.Replacement'
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 AGGReplaced Column with locID is not null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IER'
AND PEEvent = 'ImageReview.Replacement'
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
--Updating the Temp table of AGGReplaced Column with locID is null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IER'
AND PEEvent = 'ImageReview.Replacement'
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 AGGReplaced Column with locID is not null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IAR'
AND PEEvent = 'ImageReview.Replacement'
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
--Updating the Temp table of AGGReplaced Column with locID is null
UPDATE #TEMP2 SET AGGReplaced =A.AGGReplaced FROM
(SELECT COUNT(PRID) as AGGReplaced,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IAR'
AND PEEvent = 'ImageReview.Replacement'
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 AGGDuplicate Column with locID is not null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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
--Updating the Temp table of AGGDuplicate Column with locID is null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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 AGGDuplicate Column with locID is not null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ISR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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
--Updating the Temp table of AGGDuplicate Column with locID is null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ISR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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 AGGDuplicate Column with locID is not null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ICR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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
--Updating the Temp table of AGGDuplicate Column with locID is null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'ICR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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 AGGDuplicate Column with locID is not null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IER'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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
--Updating the Temp table of AGGDuplicate Column with locID is null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IER'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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 AGGDuplicate Column with locID is not null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IAR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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
--Updating the Temp table of AGGDuplicate Column with locID is null
UPDATE #TEMP2 SET AGGDuplicate =A.AGGDuplicate FROM
(SELECT COUNT(*) as AGGDuplicate,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE MCMetric = 'IAR'
AND PEEvent = 'ImageReview.Duplicate' and PAGEActive =0
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 AGGReIndexed Column with locID is not null
UPDATE #TEMP2 SET AGGReIndexed =A.AGGReIndexed FROM
(SELECT COUNT(PRID) as AGGReIndexed,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE PEEvent = 'Reindexed'
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
--Updating the Temp table of AGGReIndexed Column with locID is null
UPDATE #TEMP2 SET AGGReIndexed =A.AGGReIndexed FROM
(SELECT COUNT(PRID) as AGGReIndexed,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE PEEvent = 'Reindexed'
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 AGGDeleted Column with locID is not null
UPDATE #TEMP2 SET AGGDeleted =A.AGGDeleted FROM
(SELECT COUNT(PRID) as AGGDeleted,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE PAGEActive =0 and PEEvent = 'ReindexImage.Deleted'
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
--Updating the Temp table of AGGDeleted Column with locID is null
UPDATE #TEMP2 SET AGGDeleted =A.AGGDeleted FROM
(SELECT COUNT(PRID) as AGGDeleted,PRID,LOCID,Rev_CatID,MSTTYPE FROM #TEMP
WHERE PAGEActive =0 and PEEvent = 'ReindexImage.Deleted'
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 AGGTracked - AGGReIndexed
UPDATE #TEMP2 SET AGGTracked = AGGTracked - AGGReIndexed
--Finally Inserting the temp table values into 'RPTAggregate' table for a single day.
BEGIN TRANSACTION
INSERT INTO RPTAggregate
SELECT *,@StartDate AS AGGForDate,CURRENT_TIMESTAMP AS UPDATED,'JOB' AS UPDATEDBY FROM #Temp2
IF(@@ERROR = 0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
DROP TABLE #Temp
DROP TABLE #Temp2
SET @StartDate = @StartDate + 1
END
END
------------

Viewing 15 posts - 1 through 15 (of 15 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I stopped reading 2-3 queries in. This is WAY too long to read and tune on a forum.

this is defenitely wrong =>

WHERE CONVERT(NVARCHAR(12),PE.PEDONE,110) = @StartDate AND SC.CATID = 1

Change that to WHERE PE.PEDONE >= @StarDate AND PE.PEDONE < DATEADD(D, 1, @StarDate)

Please take notice that this is not the equivalent of between.

You can always post the actual execution plan so we may find obvious hot spots but there's no way we'll rewrite this for free over here. Something that long might take 1 week to fully tune and MOST certainly way more than 5 minutes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

dm_tapas (11/1/2011)
Please copy it to your SSMS and try to tune it . (SQL SERVER 2005)

How much are you paying me to tune it? That's easily a day of work for a rough job.

Do yourself and us a favour, work out which of the queries in there is the most time consuming, and post that query, execution plan, index and table designs: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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

WHERE PE.PEDONE >= @StarDate AND PE.PEDONE < DATEADD(D, 1, @StarDate)

What is D here.

WayneS SSC Guru Points: 95460 More actions · Answer 4

dm_tapas (11/1/2011)
The below stored procedure is taking around 1 hours to execute. but it should not take this much time.
Can some body tune this and tell me where is the problem....?...
I don't find any..
I am helpless regarding this. somebody help!!!!!
Thanks in advance.

1. Eliminate the WHILE loop... you're telling SQL how to do it's work, not what you want done (SQL is a declarative language - tell it what you want done and it figures out the best way to accomplish it).

2. Multiple updates to the same column ... get the logic right, and do just one update for the column.

3. Multiple updates to the same table... you can update > 1 column at a time.

I doubt that you'll find any takers to tune it for you... unless you're paying. Then you would probably have a bunch of takers, and guess what... they'd do those three steps I mentioned above.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

dm_tapas (11/1/2011)
WHERE PE.PEDONE >= @StarDate AND PE.PEDONE < DATEADD(D, 1, @StarDate)
What is D here.

F1 broken?

d is for day.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

WayneS (11/1/2011)
dm_tapas (11/1/2011)
The below stored procedure is taking around 1 hours to execute. but it should not take this much time.
Can some body tune this and tell me where is the problem....?...
I don't find any..
I am helpless regarding this. somebody help!!!!!
Thanks in advance.
1. Eliminate the WHILE loop... you're telling SQL how to do it's work, not what you want done (SQL is a declarative language - tell it what you want done and it figures out the best way to accomplish it).
2. Multiple updates to the same column ... get the logic right, and do just one update for the column.
3. Multiple updates to the same table... you can update > 1 column at a time.
I doubt that you'll find any takers to tune it for you... unless you're paying. Then you would probably have a bunch of takers, and guess what... they'd do those three steps I mentioned above.

Yes, but I'm still hoping a 10 minute fix might work when seeing the actual execution plan. At least go down from 60 minutes to 5-10.

Bringing down to 5 sec is pretty much out of the question without a massive investment of <free> time here, which I don't have to spare.

I've seen lots of client side code similar to that and there's often a way to make it run decent without wasting too much time (90%-10% rule).

Going to 100% is ovbiously lots longer... especially on this one.

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

Don't mind , I have not written the code ,,

Somebody has written from client side. Now it is giving problem to client . so they ask me to tune it ..

But I am not that much capable to understand the long script.

Okay , .. Most of the people are asking about the money over here.

Sorry that I posted the Whole script in the forum . thinking that somebody will help ........ Moreover I know it will take time to tune. I am also not in hurry .

Okay sorry once again guys.

Thanks for your Valuable suggestion ....

GSquared SSC Guru Points: 260824 More actions · Answer 8

The problem for me isn't money. It's that there's no way for me to "copy and paste into SSMS and run the query", since I don't have the tables, the database, nor any of the data.

I can give some general suggestions:

First, there's an over-reliance on temp tables.

Second, date manipulation is being done by converting to Unicode strings, and then implicitly converting back to datetime. Use the T-SQL date manipulation functions (DateAdd, DateDiff, et al) instead. Better performance, cleaner code.

Third, it has error-handling built into If Then type loops, where it could be simplified significantly. For example, it sets values for Start Date and End Date, then does an If Then on whether those are NULL or not. It should use Coalesce, and then a Try Catch handling as a backup if absolutely necessary.

Fourth, it inserts place-holder values into a temp table and then updates those, instead of just selecting the correct values in the first place. I've seen this paradigm before, from programmers who learned their trade in the 70s and early 80s on mainframe databases. It's a very poor way to handle data in a modern database, because it's very, very slow compared to selecting the right data the first time.

Handle those things, and you'll have a much faster procedure.

If you want more help than that, you need to provide the table structures (create table scripts) for all of the tables this connects to, and some sample data (scripted insert statements) for each table. With those, I think it would be a matter of minutes to at least get a first run-through of actual improvements.

Full optimization would require longer, and would require access to a copy of the database this will actually be run in, on hardware comparable to the production server. That means your development and testing environment (assuming that's set up correctly). So, if you want a fully optimized query here, I recommend hiring someone to do that for you.

If you just want some first-run improvements (which will almost certainly be much better than the current query), table definitions will at least let it get started.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

Thanks,

I will do the same , as you mention . And also try to hire somebody to solve the issue.

Thanks once again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

If you post what I asked for, maybe we can give you some suggestions.

GilaMonster (11/1/2011)
Do yourself and us a favour, work out which of the queries in there is the most time consuming, and post that query, execution plan, index and table designs: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

If 4-5 of us combine we can possibly get you 50-75% improvement.

Normally that happens over 1-2 weeks because of all the waiting between replies.

95%+ requires to be on site.

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

Thanks,

Don't mind..

I am planning to hire somebody to solve the issue.

LutzM SSC Guru Points: 107049 More actions · Answer 13

dm_tapas (11/1/2011)
Don't mind , I have not written the code ,,
Somebody has written from client side. Now it is giving problem to client . so they ask me to tune it ..
...

Slightly off topic: do you actually have the clients permission to post the code on the web? You might run into an issue...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

I am a DBA. the SP is already there in the database. I have the permission to modify .