November 22, 2007 at 6:09 am
Hi,
I got this SP, which takes too much time to execute. (which cause timeout in my application). can any body help me and optimize this SP.
Thanks in advance.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SSP_GetCustomReportAll]
@CompanyId int,
@userid varchar (1000),
@Department VARCHAR(25),
@ExpenseCode3 VARCHAR(50)
AS
/******************************************************************************
**File: SSP_GetCustomReportAll.sql
**Name: SSP_GetCustomReportAll
**Desc:
**
**This template can be customized:
**
**
**Auth: Nilesh Powniker
**Date: Sep 24 2007
*******************************************************************************
**Change History
*******************************************************************************
**Date:Author:Description:
**Sep 24 2007Nilesh PownikerCreated
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @MembersCount bigint
DECLARE @URLCount int
--BUSINESS LOGIC GOES HERE----------------------------------------------------------
BEGIN
DECLARE @SQL1 varchar (4000)
DECLARE @SQL2 varchar (4000)
SET NOCOUNT ON;
IF @ExpenseCode3= ''
BEGIN
SET @ExpenseCode3 = NULL
END
CREATE TABLE #TBLTEMP12 (userid int)
--SET @SQL2 = 'INSERT INTO #TBLTEMP12 SELECT a.PK_User_Surrogate_Key FROM tbl_JOb a WHERE PK_User_Surrogate_Key IN ('+ convert(Varchar(1000),@UserId ) + ' )'
SET @SQL2 = 'INSERT INTO #tblTemp12 SELECT DISTINCT tbl_JOb.PK_User_Surrogate_Key FROM tbl_JOb
INNER JOIN tbl_Users ON tbl_JOb.PK_User_Surrogate_Key = tbl_Users.PK_User_Surrogate_Key
WHERE tbl_JOb.PK_User_Surrogate_Key IN ('+ convert(Varchar(1000),@UserId ) + ')'
IF @Department <> ''
SET @SQL2 = @SQL2 + ' AND Department LIKE ''' + convert(Varchar(100),@Department ) + ''''
EXEC (@SQL2)
SELECT @MembersCount = (COUNT(tbl_MemberDetails.PK_MEMBERDET_SURROGATE_KEY)) FROM tbl_MemberDetails
WHERE (tbl_MemberDetails.fk_Comp_Surrogate_Key = @COMPANYID) AND
(tbl_MemberDetails.FK_User_Surrogate_Key IN (SELECT * FROM #TBLTEMP12)) AND
tbl_MemberDetails.Removaltype = 'NV'
select @URLCount = COUNT(URL) from TBL_JOBURLTRACKING
WHERE (TBL_JOBURLTRACKING.fk_Comp_Surrogate_Key = @COMPANYID) AND
(TBL_JOBURLTRACKING.FK_User_Surrogate_Key IN (SELECT * FROM #TBLTEMP12))
select A.Names, A.JobsFetched,ROUND(X.ClickThroughs,0) as ClickThroughs ,
ROUND(Y.Opens,0) as Opens,ROUND(Z.Forwards,0) as Forwards ,
ROUND(B.Removals,0) as Removals , ROUND(C.OptOuts,0) as OptOuts,
ROUND(D.NonResponders,0) as NonResponders, MAX(A.UserID) AS UserID
From
(
SELECT (FIRSTNAME + ' ' + LASTNAME) AS NAMES,
TB.PK_USER_SURROGATE_KEY AS USERID,
COUNT(TB.PK_JOB_SURROGATE_KEY)AS JOBSFETCHED
FROM
TBL_JOB TB
INNER JOIN TBL_USERS TU
ON TU.FK_COMP_SURROGATE_KEY = TB.PK_COMP_SURROGATE_KEY
AND TU.PK_USER_SURROGATE_KEY = TB.PK_USER_SURROGATE_KEY
WHERE TB.PK_COMP_SURROGATE_KEY = @COMPANYID
AND TB.PK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND (TB.JobStatus = 9 OR TB.JobStatus = 4)
-- AND ISNULL(@ExpenseCode3,ExpenseCode3)= ExpenseCode3
GROUP BY FIRSTNAME , LASTNAME ,TB.PK_USER_SURROGATE_KEY
) A
LEFT OUTER JOIN (
SELECT TB.PK_USER_SURROGATE_KEY AS USERID,
(Convert(Decimal,Convert(Decimal,COUNT(FK_URLTracking_Surrogate_Key)) * 100 / (@URLCount * @MembersCount))) AS CLICKTHROUGHS
FROM
TBL_JOB TB
INNER JOIN tbl_JobClickTracking JTS
ON JTS.FK_COMP_SURROGATE_KEY = TB.PK_COMP_SURROGATE_KEY
AND JTS.FK_JOB_SURROGATE_KEY = TB.PK_JOB_SURROGATE_KEY
AND JTS.FK_USER_SURROGATE_KEY = TB.PK_USER_SURROGATE_KEY
WHERE TB.PK_COMP_SURROGATE_KEY = @COMPANYID
AND FK_URLTracking_Surrogate_Key > 0
AND TB.PK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND (TB.JobStatus = 9 OR TB.JobStatus = 4)
GROUP BY TB.PK_USER_SURROGATE_KEY
)X
ON A.UserID = X.UserID
left outer join (
SELECT TB.PK_USER_SURROGATE_KEY AS USERID,
Convert(Decimal,Convert(Decimal,COUNT(FK_URLTracking_Surrogate_Key)) * 100 / @MembersCount) AS OPENS
FROM
TBL_JOB TB
INNER JOIN tbl_JobClickTracking JTS
ON JTS.FK_COMP_SURROGATE_KEY = TB.PK_COMP_SURROGATE_KEY
AND JTS.FK_JOB_SURROGATE_KEY = TB.PK_JOB_SURROGATE_KEY
AND JTS.FK_USER_SURROGATE_KEY = TB.PK_USER_SURROGATE_KEY
WHERE TB.PK_COMP_SURROGATE_KEY = @COMPANYID
AND FK_URLTracking_Surrogate_Key = 0
AND TB.PK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND (TB.JobStatus = 9 OR TB.JobStatus = 4)
GROUP BY TB.PK_USER_SURROGATE_KEY
) Y ON A.USERID = Y.USERID
left outer join (
SELECT TB.PK_USER_SURROGATE_KEY AS USERID,
Convert(Decimal,Convert(Decimal,COUNT(TF.FK_JOB_SURROGATE_KEY)) * 100 / @MembersCount) AS FORWARDS
FROM
TBL_JOB TB
LEFT OUTER JOIN tbl_Forwards TF
ON TF.FK_COMP_SURROGATE_KEY = TB.PK_COMP_SURROGATE_KEY
AND TF.FK_JOB_SURROGATE_KEY = TB.PK_JOB_SURROGATE_KEY
AND TF.FK_USER_SURROGATE_KEY = TB.PK_USER_SURROGATE_KEY
WHERE TB.PK_COMP_SURROGATE_KEY = @COMPANYID
AND TB.PK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND (TB.JobStatus = 9 OR TB.JobStatus = 4)
GROUP BY TB.PK_USER_SURROGATE_KEY
) Z ON A.UserId = Z.UserId
left outer join (
SELECT TMD.FK_USER_SURROGATE_KEY AS USERID,
ISNULL(((SELECT COUNT(PK_MEMBERDET_SURROGATE_KEY) FROM TBL_MEMBERDETAILS WHERE TBL_MEMBERDETAILS.REMOVALTYPE != 'NV' AND TBL_MEMBERDETAILS.REMOVALTYPE != 'INV' AND FK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND FK_JOB_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12) ) * 100) / (COUNT(PK_MEMBERDET_SURROGATE_KEY)),0) AS REMOVALS
FROM TBL_MEMBERDETAILS TMD
INNER JOIN TBL_JOB TB
ON TMD.FK_COMP_SURROGATE_KEY = TB.PK_COMP_SURROGATE_KEY
AND TMD.FK_JOB_SURROGATE_KEY = TB.PK_JOB_SURROGATE_KEY
AND TMD.FK_USER_SURROGATE_KEY = TB.PK_USER_SURROGATE_KEY
WHERE TMD.FK_COMP_SURROGATE_KEY = @COMPANYID
AND TMD.REMOVALTYPE != 'INV'
AND TMD.FK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND (TB.JobStatus = 9 OR TB.JobStatus = 4)
GROUP BY TMD.FK_USER_SURROGATE_KEY
) B on A.userid = B.userid
left outer join (
SELECT TMR.FK_USER_SURROGATE_KEY AS USERID,
ISNULL(((COUNT(STATUS) * 100 )/ @MembersCount),0) AS OPTOUTS
FROM TBL_JOB TB
LEFT OUTER JOIN TBL_MAILINGRESULT TMR
ON TMR.FK_COMP_SURROGATE_KEY = TB.PK_COMP_SURROGATE_KEY
AND TMR.FK_JOB_SURROGATE_KEY = TB.PK_JOB_SURROGATE_KEY
AND TMR.FK_USER_SURROGATE_KEY = TB.PK_USER_SURROGATE_KEY
WHERE TMR.FK_COMP_SURROGATE_KEY = @COMPANYID
AND STATUS = 'OO'
AND TMR.FK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND (TB.JobStatus = 9 OR TB.JobStatus = 4)
GROUP BY TMR.FK_USER_SURROGATE_KEY
HAVING COUNT(PK_JOB_SURROGATE_KEY) > 0
) C on A.userid = C.userid
left outer join (
SELECT TMR.FK_USER_SURROGATE_KEY AS USERID,
ISNULL((COUNT(STATUS) * 100 )/ @MembersCount,0) AS NONRESPONDERS
FROM TBL_MAILINGRESULT TMR
RIGHT OUTER JOIN TBL_JOB TB
ON TMR.FK_COMP_SURROGATE_KEY = TB.PK_COMP_SURROGATE_KEY
AND TMR.FK_JOB_SURROGATE_KEY = TB.PK_JOB_SURROGATE_KEY
AND TMR.FK_USER_SURROGATE_KEY = TB.PK_USER_SURROGATE_KEY
WHERE TMR.FK_COMP_SURROGATE_KEY = @COMPANYID
AND STATUS = 'NR'
AND TMR.FK_USER_SURROGATE_KEY IN (SELECT * FROM #TBLTEMP12)
AND (TB.JobStatus = 9 OR TB.JobStatus = 4)
GROUP BY TMR.FK_USER_SURROGATE_KEY
HAVING COUNT(PK_JOB_SURROGATE_KEY) > 0
) D on A.userid = D.userid
GROUP BY A.Names, A.JobsFetched,X.ClickThroughs ,Y.Opens,
Z.Forwards ,B.Removals ,C.OptOuts,D.NonResponders
END
--END OF BUSINESS LOGIC GOES HERE------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
November 22, 2007 at 6:32 am
Rajeev
You've got seven different SELECT statements in there. Have you tried running them individually to see which one is taking most of the time? Once you identify that, look at the execution plan and try to identify any table scans or clustered index scans, which are usually (but not always) undesirable. You may be able to eliminate these with proper indexing. Have you updated your statistics and/or reindexed recently? Use the sys.dm_db_index_physical_stats function to see whether you need to reorganise or rebuild your indexes. Have you checked that there isn't any blocking that's stopping your procedure from running?
This isn't an exhaustive checklist, just a few things I thought of off the top of my head.
John
November 22, 2007 at 9:03 am
To add to that ...
I count at least 10 occurrences of
SOME_KEY IN (SELECT * FROM #TBLTEMP12)
Can you convert those IN () sub-queries to EXISTS () or INNER JOINs ?
Also, if #TBLTEMP12 is large, you might consider creating an index on it with a high fillfactor.
November 22, 2007 at 6:13 pm
Well I didnt really tried to understand what was going in your procedure.
In my opinion you should move all buisness logic to your application layer.
Then you most likely never need to write stored procedures that run more then a few lines of code.
November 26, 2007 at 6:50 am
In addition to what everyone else has suggested, I'd also suggest taking the delimited list that you start with and instead of using it to load up a temp table you use a function (several examples available in the scripts section of the site here) to turn that list into a table. That way you can just do simple select statements with a join or exists, just like another one of the suggestions earlier.
One other thing I'd suggest, keep this proc as a wrapper proc, but make all the other select statements as individual procedures. This will allow each statement to generate an individual execution plan, making for more accurate plans and faster compile times. With a plan this big the optimizer isn't going to be able to spend much time trying to find an optimal execution path.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply