Does this Stored procedure degrade performance? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Analysis Server and Reporting Services (2005)

Does this Stored procedure degrade performance?

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-21 : 12:42:54

HI I have written this Stored procedure is use on a Report as a subreport. I noticed that my machine slows considerably after I run this report. It could be coincidence of course but I wanted to know i there is something here which could be improved upon, or is something missing that might help such a problem? The subreport does the same read as the first part of the report.

USE [CRSS]
GO
/****** Object: StoredProcedure [dbo].[spu_CRSS_Section3] Script Date: 07/19/2009 10:09:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 17-July-2009
-- Description: To automatically produce section 3 data
-- =============================================
ALTER PROCEDURE [dbo].[spu_CRSS_Section3]
(
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20)
)
AS
BEGIN

DECLARE @STARTDATE1 DATETIME
DECLARE @STARTDATE2 DATETIME
DECLARE @STARTDATE3 DATETIME
DECLARE @STARTDATE4 DATETIME

--- SET STARTDATES

SET @STARTDATE1 = DateAdd (m , -1 , @EndDate +1)

SET @STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )

SET @STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )

SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )

SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' AS source

INTO #temp

FROM sop10200 AS SOP10200 INNER JOIN
iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)
UNION ALL
SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,

SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' AS source

FROM sop30300 AS SOP30300 LEFT OUTER JOIN
iv00101 AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
sop30200 AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL LEFT OUTER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)

SELECT
CustName,
ItemDesc,
CAST(OXTNDPRC AS FLOAT) AS OXTNDPRC,
CAST(Quantity AS FLOAT) AS QUANTITY,
DOCDATE,
SOURCE,
ITEMNMBR,
CPRCSTNM,
CAST(0.0 AS FLOAT) AS BILLED,
CAST(0.0 AS FLOAT) AS BILLED1,
CAST(0.0 AS FLOAT) AS BILLED2,
CAST(0.0 AS FLOAT) AS BILLED3,
CAST(0.0 AS FLOAT) AS BILLED4,
CAST(0.0 AS FLOAT) AS CENSUS,
CAST(0.0 AS FLOAT) AS CENSUS1,
CAST(0.0 AS FLOAT) AS CENSUS2,
CAST(0.0 AS FLOAT) AS CENSUS3,
CAST(0.0 AS FLOAT) AS CENSUS4,
CAST(0.0 AS FLOAT) AS PPD1,
CAST(0.0 AS FLOAT) AS PPD2,
CAST(0.0 AS FLOAT) AS PPD3,
CAST(0.0 AS FLOAT) AS PPD4,
CAST(0.0 AS FLOAT) AS QUOTED,
CAST(0.0 AS FLOAT) AS QUOTED1,
CAST(0.0 AS FLOAT) AS QUOTED2,
CAST(0.0 AS FLOAT) AS QUOTED3,
CAST(0.0 AS FLOAT) AS QUOTED4,
CAST(0.0 AS FLOAT) AS LTOT

INTO #FinalReport
FROM #temp
WHERE (CPRCSTNM = @Customer) OR (@Customer IS NULL) OR (@Customer='')

UPDATE #FinalReport
SET BILLED = CASE WHEN SOURCE = 'History' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET BILLED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN BILLED ELSE 0 END,
BILLED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN BILLED ELSE 0 END,
BILLED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN BILLED ELSE 0 END,
BILLED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN BILLED ELSE 0 END

UPDATE #FinalReport
SET CENSUS = CASE WHEN SOURCE='History' THEN Quantity ELSE 0 END

UPDATE #FinalReport
SET CENSUS1 = CASE WHEN DOCDATE BETWEEN @StartDate1 AND @EndDate THEN CENSUS ELSE 0 END,
CENSUS2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN CENSUS ELSE 0 END,
CENSUS3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN CENSUS ELSE 0 END,
CENSUS4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN CENSUS ELSE 0 END

UPDATE #FinalReport
SET QUOTED = CASE WHEN SOURCE = 'current' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET QUOTED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN QUOTED ELSE 0 END,
QUOTED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN QUOTED ELSE 0 END,
QUOTED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN QUOTED ELSE 0 END,
QUOTED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN QUOTED ELSE 0 END

--- SUMMARY FRO PPD Calculation
SELECT CUSTNAME,
CPRCSTNM,
MAX(CENSUS1) AS CENSUS1,
MAX(BILLED1) AS BILLED1,
MAX(CENSUS2) AS CENSUS2,
MAX(BILLED2) AS BILLED2,
MAX(CENSUS3) AS CENSUS3,
MAX(BILLED3) AS BILLED3,
MAX(CENSUS4) AS CENSUS4,
MAX(BILLED4) AS BILLED4,
SUM(QUOTED1) AS QUOTED1,
SUM(QUOTED2) AS QUOTED2,
SUM(QUOTED3) AS QUOTED3,
SUM(QUOTED4) AS QUOTED4,
CAST(0 AS FLOAT) AS PPD1,
CAST(0 AS FLOAT) AS PPD2,
CAST(0 AS FLOAT) AS PPD3,
CAST(0 AS FLOAT) AS PPD4,
CAST(0 AS FLOAT) AS LTOT

INTO #SUMMARY
FROM #FinalReport
GROUP BY CUSTNAME

UPDATE #SUMMARY
SET PPD1 = CASE WHEN S.CENSUS1 = 0 THEN 0 ELSE S.BILLED1/S.CENSUS1 END
FROM #SUMMARY S
WHERE S.CUSTNAME = #FINALREPORT.CUSTNAME

UPDATE #SUMMARY
SET PPD2 = CASE WHEN S.CENSUS2 = 0 THEN 0 ELSE S.BILLED2/S.CENSUS2 END
FROM #SUMMARY S
WHERE S.CUSTNAME = #FINALREPORT.CUSTNAME

UPDATE #SUMMARY
SET PPD3 = CASE WHEN S.CENSUS3 = 0 THEN 0 ELSE S.BILLED3/S.CENSUS3 END
FROM #SUMMARY S
WHERE S.CUSTNAME = #FINALREPORT.CUSTNAME

UPDATE #SUMMARY
SET PPD4 = CASE WHEN S.CENSUS4=0 THEN 0 ELSE S.BILLED4/S.CENSUS4 END
FROM #SUMMARY S
WHERE S.CUSTNAME = #FINALREPORT.CUSTNAME

UPDATE #SUMMARY
SET LTOT = S.QUOTED1 + S.QUOTED2 + S.QUOTED3 + S.QUOTED4 + S.BILLED1 + S.BILLED2 + S.BILLED3 + S.BILLED4
FROM #SUMMARY S
WHERE S.CUSTNAME = #FINALREPORT.CUSTNAME

SELECT * FROM #SUMMARY

END
GO

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-07-21 : 13:10:10

I'd have concerns about some of your coding..particularly towards the bottom, because you look to be trying to join 2 temp tables...with noindices, which unless they are very small tables is a big no-no. Table-scans in effect.

UPDATE #SUMMARY
SET PPD1 = CASE WHEN S.CENSUS1 = 0 THEN 0 ELSE S.BILLED1/S.CENSUS1 END
FROM #SUMMARY S
WHERE S.CUSTNAME = #FINALREPORT.CUSTNAME

I'd also have a concern as to how the joins are being performed in that I can't see any obvious 2 tables being read from in the update statements.

UPDATE #SUMMARY
SET PPD1 = CASE WHEN S.CENSUS1 = 0 THEN 0 ELSE S.BILLED1/S.CENSUS1 END
FROM #SUMMARY S
inner join #FINALREPORT on x=y.....I think you need something like this at least for the code to work, never mind work fast.
WHERE S.CUSTNAME = #FINALREPORT.CUSTNAME

I can see this code working slowly. I can't see why it would leave a system slow after it was run however.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources