Stored Proc runs slow - SQL Server Forums

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

Transact-SQL (2005)

Stored Proc runs slow

Author

Topic

CraigDEST
Starting Member

1 Post

Posted - 2007-01-16 : 16:27:23

Hi All

ok have a stored proc which takes about 33 seconds to run for all states in SQL2000 server we are trying to migrate to SQL2005 and the same stored proc takes 38 minutes for all.....running from query anaylser. Here is the stored proc below. Database is by no means perfect and the stored proc could be re-written, but the main question is SQL2005 is meant to be an improvement on SQL2000 yet it is slower by a long way why???? The specs on the new SQL2005 machine are more than enough to handle what it need to do.

Any help would be be appricated..

CREATE PROCEDURE rptIOSPElectorateProjects
@StateCd varchar(10)

AS
BEGIN

SET NOCOUNT ON

Declare @TotalGrants money
Declare @AmountByYear2005 money
Declare @AmountByYear2006 money
Declare @AmountByYear2007 money
Declare @AmountByYear2008 money
Declare @ProjectCostAmt money
Declare @SchoolTotalEnrol decimal(7,1)
Declare @ProjectID int
Declare @ClientId int
Declare @PreClientId int
Declare @ProjectNo int
Declare @ScheduleYr int
Declare @LocationState varchar(10)
Declare @ElectorateNm varchar(25)
Declare @School varchar(120)
Declare @Location varchar(50)
Declare @ProjDesc varchar(1300)
Declare @ElementId varchar(120)
Declare @DelSig varchar(2)
Declare @MinisDt varchar(50)
Declare @ProjectRef varchar(15)
Declare @Alias varchar(120)
Declare @PreRef varchar(20)

DECLARE @ProjectGrant TABLE
(
ProjectId INT,
ClientId INT,
ElementId VARCHAR(8),
ApplicableYr INT,
YrTotalGrants MONEY
)

INSERT INTO @ProjectGrant
SELECT CP.ProjectId
, CP.ClientId
, CG.ElementId
, CG.ApplicableYr
, Sum(CG.GrantAmt)
FROM tblCapProjects CP
INNER JOIN tblCapGrants CG ON CP.ProjectId = CG.ProjectId
WHERE CP.StatusCd IN ('040', '060')
AND CP.ProjectTypeCd IN ('BSC')
AND CP.ProgramId = 'IOSP'
GROUP BY CP.ProjectId, CP.ClientId, CG.ElementId, CG.ApplicableYr, CP.ProjectTypeCd, CP.ProgramId

DECLARE @ProjectTotalGrants TABLE
(
ProjectId INT,
ClientId INT,
ElementId VARCHAR(8),
TotalGrants MONEY
)

INSERT INTO @ProjectTotalGrants
SELECT ProjectId,
ClientId,
ElementId,
sum(YrTotalGrants)
FROM @ProjectGrant
GROUP BY ProjectId, ClientId, ElementId

DECLARE @Project2005Grant TABLE
(
ProjectId INT,
AmountByYear2005 MONEY
)

INSERT INTO @Project2005Grant
SELECT projectID, YrTotalGrants
FROM @ProjectGrant
WHERE ApplicableYr = 2005

DECLARE @Project2006Grant TABLE
(
ProjectId INT,
AmountByYear2006 MONEY
)

INSERT INTO @Project2006Grant
SELECT projectID, YrTotalGrants
FROM @ProjectGrant
WHERE ApplicableYr = 2006

DECLARE @Project2007Grant TABLE
(
ProjectId INT,
AmountByYear2007 MONEY
)

INSERT INTO @Project2007Grant
SELECT projectID, YrTotalGrants
FROM @ProjectGrant
WHERE ApplicableYr = 2007

/* Get grants for appplicable year = 2008 */
DECLARE @Project2008Grant TABLE
(
ProjectId INT,
AmountByYear2008 MONEY
)

INSERT INTO @Project2008Grant
SELECT projectID, YrTotalGrants
FROM @ProjectGrant
WHERE ApplicableYr = 2008

Declare @ProjectFileRef varchar(20)

DECLARE @Determ_MinisterApproval TABLE
(
ProjectFileRef VARCHAR(15),
MinisterApprovalDate datetime,
DelegateSignatureDt datetime
)

Declare Deter_cursor cursor For
select Distinct DPL.ProjectFileRef FROM tblDetermination D
INNER JOIN tblDetCapProjectLink DPL ON D.DeterminationId = DPL.DeterminationId
WHERE (D.ProgramId = 'IOSP') AND (D.StatusCd = 'APR')
open Deter_cursor
fetch next from Deter_cursor
into @ProjectFileRef

While @@Fetch_Status = 0
Begin
INSERT INTO @Determ_MinisterApproval
Select DPL.ProjectFileRef, D.MinisterApprovalDate, D.DelegateSignatureDt
FROM tblDetermination D
INNER JOIN tblDetCapProjectLink DPL ON D.DeterminationId = DPL.DeterminationId
WHERE (D.ProgramId = 'IOSP') AND (D.StatusCd = 'APR') and DPL.ProjectFileRef = @ProjectFileRef
and D.DelegateSignatureDt = (Select max(D.DelegateSignatureDt)
FROM tblDetermination D
INNER JOIN tblDetCapProjectLink DPL ON D.DeterminationId = DPL.DeterminationId
WHERE (D.ProgramId = 'IOSP') AND (D.StatusCd = 'APR') and DPL.ProjectFileRef = @ProjectFileRef)

fetch next from Deter_cursor
into @ProjectFileRef
End
close Deter_cursor
Deallocate Deter_cursor

DECLARE @Report TABLE
(
State VARCHAR(10),
Electorate varchar(25),
[Schedule Year] int,
School varchar(120),
Location varchar(50),
[Project Decription] varchar(1300),
[Total Grant Amount] money,
[Grant Amount By Year 2005] money,
[Grant Amount By Year 2006] money,
[Grant Amount By Year 2007] money,
[Grant Amount By Year 2008] money,
[Project Value] money,
[Total Number of Students (FTE)] decimal(7,1),
[Small/Large Project (S/L)] varchar(2),
BGA varchar(120),
[Delegate Approved (Y/N)] varchar(2),
MinisterApprovalDate VARCHAR(50),
[Project Reference] varchar(15),
[Project Number] int
)

Declare ProjNo_Cursor cursor for
SELECT CC.LocationState as 'State',
FE.ElectorateNm as 'Electorate',
CP.ScheduleYr as 'Schedule Year',
School =
(SELECT CASE
WHEN CP.CampusId IS NULL
THEN CC.ClientName
ELSE CC.ClientName + ' ('
+ (SELECT CC2.ClientName
FROM tblClientCharacteristic CC2
WHERE CC2.ClientID = CP.CampusId AND CC2.CurrentPeriodInd = - 1)
+ ')'
END),
Location =
(SELECT CASE
WHEN CP.CampusId IS NULL
THEN CC.LocationLocation
ELSE (SELECT CC.LocationLocation
FROM tblClientCharacteristic CC3
WHERE CC3.ClientID = CP.CampusId AND CC3.CurrentPeriodInd = - 1)
END),
'Project Decription' =
REPLACE(REPLACE(LTRIM(RTRIM(CP.ProjectDescription1
+ ISNULL(COALESCE(CP.ProjectDescription2, NULL), '')
+ ISNULL(COALESCE(CP.ProjectDescription3, NULL), '')
+ ISNULL(COALESCE(CP.ProjectDescription4, NULL), ''))), CHAR(13), ''), CHAR(10), ''),
PG.TotalGrants AS 'Total Grant Amount',
PG1.AmountByYear2005 as 'Grant Amount By Year 2005',
PG2.AmountByYear2006 as 'Grant Amount By Year 2006',
PG3.AmountByYear2007 as 'Grant Amount By Year 2007',
PG4.AmountByYear2008 as 'Grant Amount By Year 2008',
CP.ProjectCostAmt as 'Project Value',
SchoolTotalEnrol AS 'Total Number of Students (FTE)',
(Select case
When PG.ElementId ='IOSPBGA' then 'L'
Else 'S'
End) as 'Small/Large Project (S/L)',
CC1.AliasName as 'BGA',
(Select case
when DM.DelegateSignatureDt IS NULL then 'N'
Else 'Y'
End) as 'Delegate Approved (Y/N)',
CONVERT(VARCHAR(50), DM.MinisterApprovalDate, 103) AS 'Minister Approval Date',
CP.ProjectFileRef as 'Project Reference', CC.ClientID
FROM @ProjectTotalGrants PG
INNER JOIN tblClientCharacteristic CC
ON (CC.ClientID = PG.ClientID) AND (CC.CurrentPeriodInd = - 1)
INNER JOIN tblCapProjects CP
ON CP.ProjectId = PG.ProjectId
LEFT JOIN vwGRNonGovProgYrSchoolEnrol GREN
ON (GREN.ProgYr = CP.ScheduleYr) AND (GREN.SchoolID = PG.ClientID )
INNER JOIN tblClientCharacteristic CC1
ON (CC1.ClientID = CP.AuthorityId) AND (CC1.CurrentPeriodInd = - 1)
LEFT JOIN @Project2005Grant PG1 ON (PG1.ProjectId = PG.ProjectId)
LEFT JOIN @Project2006Grant PG2 ON (PG2.ProjectId = PG.ProjectId)
LEFT JOIN @Project2007Grant PG3 ON (PG3.ProjectId = PG.ProjectId)
LEFT JOIN @Project2008Grant PG4 ON (PG4.ProjectId = PG.ProjectId)
INNER JOIN @Determ_MinisterApproval DM ON (DM.ProjectFileRef = CP.ProjectFileRef )
INNER JOIN tblFederalElectorates FE ON (FE.FederalElectorateCd = CC.FedElectorateCd)
WHERE CC.LocationState LIKE
(SELECT CASE
WHEN @StateCd = 'ALL'
THEN '%'
ELSE @StateCd
END)
ORDER BY CC.ClientID, CP.ScheduleYr, CP.ProjectFileRef

open ProjNo_Cursor
fetch next from ProjNo_Cursor
into @LocationState, @ElectorateNm, @ScheduleYr, @School, @Location, @ProjDesc, @TotalGrants, @AmountByYear2005,
@AmountByYear2006, @AmountByYear2007, @AmountByYear2008, @ProjectCostAmt, @SchoolTotalEnrol, @ElementId,
@Alias, @DelSig, @MinisDt, @ProjectRef, @ClientID

While @@Fetch_Status = 0
Begin

if @PreClientId = @ClientId and @PreRef <> @ProjectRef
set @ProjectNo= @ProjectNo + 1
else
set @ProjectNo = 1

Insert into @Report
values (@LocationState, @ElectorateNm, @ScheduleYr, @School, @Location, @ProjDesc, @TotalGrants,
@AmountByYear2005, @AmountByYear2006, @AmountByYear2007, @AmountByYear2008,
@ProjectCostAmt, @SchoolTotalEnrol, @ElementId, @Alias, @DelSig, @MinisDt, @ProjectRef, @ProjectNo)

set @PreClientId = @ClientId
set @PreRef = @ProjectRef

fetch next from ProjNo_Cursor
into @LocationState, @ElectorateNm, @ScheduleYr, @School, @Location, @ProjDesc, @TotalGrants, @AmountByYear2005,
@AmountByYear2006, @AmountByYear2007, @AmountByYear2008, @ProjectCostAmt, @SchoolTotalEnrol, @ElementId,
@Alias, @DelSig, @MinisDt, @ProjectRef, @ClientID

end

close ProjNo_Cursor
deallocate ProjNo_Cursor

Select * from @Report
ORDER BY State, Electorate, School, [Schedule Year]
End
GO

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 16:46:52

There's a lot of areas in this that have potential to run slow. Make sure you are using the correct index's when needed. Make sure to check your execution plan to see if there is any 1 area where it is causing the major delay. I would also probably use Tmp tables rather than variable tables, and I would strongly recommend getting away from the cursors, they're undoubtably not helping your issue.

i.e. (create table #xxx vs. Decalare @xxx table)

The tmp tables wont be such a resource hog.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources