Optimizing stored procedure - 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 2000 Forums

Transact-SQL (2000)

Optimizing stored procedure

Author

Topic

taraernst
Starting Member

9 Posts

Posted - 2009-04-07 : 11:15:32

Requesting assistance in optimizing the following stored procedure. When the statements are run individually in QA, results are fast. When run through QA as a stored procedure inserting values for the parameters, it runs forever.... Any help is greatly appreciated!

CREATE PROCEDURE [dbo].[sMngmtRpts_Sel_MonthlyCallAnalysis]

@Filter varchar (50),
@Name varchar (100)

AS

CREATE TABLE #FilteredCalls
(
CustomerID varchar(100), CallDate datetime, Reporter varchar(500), CallTeam varchar (1000), Prime varchar(100), Supervisor varchar(100), BManager varchar(100)
)

IF @Filter = 'ByMgr'
BEGIN

INSERT INTO #FilteredCalls (CustomerID, CallDate, Reporter, CallTeam, Prime, Supervisor, BManager)

SELECT c.CustomerID, CallDate, Reporter, CallTeam, o.Prime, p.Supervisor, BManager
FROM dbo.tblOrgs o INNER JOIN
dbo.tblPeopleWithStatus p ON o.Prime= p.MIName RIGHT OUTER JOIN
dbo.tblCalls c ON o.CustomerID = c.CustomerID
WHERE (c.CallDate > YEAR(GETDATE() - 3)) AND (p.BManager = @Name)
END
ELSE IF @Filter = 'ByTeamLead'
BEGIN
INSERT INTO #FilteredCalls (CustomerID, CallDate, Reporter, CallTeam, Prime, Supervisor, BManager)

SELECT c.CustomerID, CallDate, Reporter, CallTeam, o.Prime, p.Supervisor, BManager
FROM dbo.tblOrgs o INNER JOIN
dbo.tblPeopleWithStatus p ON o.Prime= p.MIName RIGHT OUTER JOIN
dbo.tblCalls c ON o.CustomerID = c.CustomerID
WHERE (c.CallDate > YEAR(GETDATE() - 3)) AND (p.Supervisor = @Name)
END
ELSE IF @Filter = 'ByPrime'
BEGIN
INSERT INTO #FilteredCalls (CustomerID, CallDate, Reporter, CallTeam, Prime, Supervisor, BManager)

SELECT c.CustomerID, CallDate, Reporter, CallTeam, o.Prime, p.Supervisor, BManager
FROM dbo.tblOrgs o INNER JOIN
dbo.tblPeopleWithStatus p ON o.Prime= p.MIName RIGHT OUTER JOIN
dbo.tblCalls c ON o.CustomerID = c.CustomerID
WHERE (c.CallDate > YEAR(GETDATE() - 3)) AND (o.Prime= @Name)
END

SELECT MONTH(CallDate) as Month, *, BManager, Supervisor
FROM #FilteredCalls
WHERE (Reporter = Prime) OR
(CHARINDEX(Prime, CallTeam) > 0)
DROP TABLE #FilteredCalls
GO

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-07 : 11:21:41

can be a case of parameter sniffing. Give this a read.
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:32:58

may be this

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

taraernst
Starting Member

9 Posts

Posted - 2009-04-07 : 11:52:12

Thank you for the prompt responses. I have attempted to use local variables and assigned the input parameters to the local variables and then used the local variables in the queries. Although this concept makes sense, it is not helping the performance at all. Any other thoughts?

CREATE PROCEDURE [dbo].[sMngmtRpts_Sel_MonthlyCallAnalysis]

@Filter varchar (50),
@Name varchar (100)

AS

BEGIN

DECLARE @LocFilter varchar(50)
DECLARE @LocName varchar(100)

SET @LocFilter = @Filter
SET @LocName = @Name

CREATE TABLE #FilteredCalls
(
CustomerID varchar(100), CallDate datetime, Reporter varchar(500), CallTeam varchar (1000), BankPrime varchar(100), Supervisor varchar(100), BManager varchar(100)
)

IF @LocFilter = 'ByMgr'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 13:06:25

[code]
CREATE PROCEDURE [dbo].[sMngmtRpts_Sel_MonthlyCallAnalysis]

@Filter varchar (50),
@Name varchar (100)

AS

CREATE TABLE #FilteredCalls
(
CustomerID varchar(100), CallDate datetime, Reporter varchar(500), CallTeam varchar (1000), Prime varchar(100), Supervisor varchar(100), BManager varchar(100)
)

INSERT INTO #FilteredCalls (CustomerID, CallDate, Reporter, CallTeam, Prime, Supervisor, BManager)

SELECT c.CustomerID, CallDate, Reporter, CallTeam, o.Prime, p.Supervisor, BManager
FROM dbo.tblOrgs o INNER JOIN
dbo.tblPeopleWithStatus p ON o.Prime= p.MIName RIGHT OUTER JOIN
dbo.tblCalls c ON o.CustomerID = c.CustomerID
WHERE c.CallDate > YEAR(GETDATE() - 3) AND
CASE @Filter
WHEN 'ByMgr' THEN p.BManager
WHEN 'ByTeamLead' THEN p.Supervisor
WHEN 'ByPrime' THEN o.Prime
END= @Name

SELECT MONTH(CallDate) as Month, *, BManager, Supervisor
FROM #FilteredCalls
WHERE (Reporter = Prime) OR
(CHARINDEX(Prime, CallTeam) > 0)

DROP TABLE #FilteredCalls
GO
[/code]

taraernst
Starting Member

9 Posts

Posted - 2009-04-07 : 14:14:01

visakh16 -

Thank you very much! This certainly has simplified the procedure. I have also optimized the indexes on the join fields and it is now working great!

Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources