Hello,
I have the below SP that is running very slow, can any one Help please.
Thanks!


----------------------


CREATE PROCEDURE dbo.C1_ProcessChangeQueueItem
(
@ErrNo INT OUTPUT
, @ErrMsg NVARCHAR(510) OUTPUT
, @RowsAffected INT OUTPUT
, @QueueID INT -- Queue item to be processed
, @LogLevelID INT -- Level of logging to perform while
processing item
, @DebugMode BIT = 0 -- Debug mode adds print statements
for debugging
)

AS

/***************************************************************************************************
Procedure name: C1_ProcessChangeQueueItem
File name: C1_ProcessChangeQueueItem.sql
Purpose: Processes a single ChangeAction for an item in ChangeQueue.

Modification History
Created: 11/03/2004 Jim Simonson

Modified:
12/07/2004 Jim Simonson Made changes resulting from code review.
12/21/2004 Jim Simonson Removed references to TableID from Table_List.
12/27/2004 Jim Simonson Added @DebugMode and related statements.
01/31/2005 Jim Simonson Only execute enabled actions.
***************************************************************************************************/

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON

DECLARE @ErrorNo INT
, @ErrorMsg VARCHAR(255)
, @StepNo INT
, @RowCount INT
, @ServerRole VARCHAR(100)
, @FailOnError BIT
, @PK1Value VARCHAR(64)
, @PK2Value VARCHAR(64)
, @PK3Value VARCHAR(64)
, @PK4Value VARCHAR(64)
, @PK5Value VARCHAR(64)
, @PK6Value VARCHAR(64)
, @SQLStatement NVARCHAR(4000)
, @ParamDef NVARCHAR(4000)
, @ActionID INT
, @Ordinal INT
, @TranStarted BIT
, @TempTableOriginator BIT
, @ActionStartTime DATETIME
, @ActionEndTime DATETIME

-- If logging temp table doesn't exist, then create it
IF NOT EXISTS (SELECT *
FROM tempdb..sysobjects
WHERE id = OBJECT_ID('tempdb..#ChangeLog'))
BEGIN
CREATE TABLE #ChangeLog
(
LogID INT IDENTITY(1, 1) NOT NULL,
QueueID INT NULL,
Description VARCHAR(255) NULL,
CreatedBy VARCHAR(100) NOT NULL,
CreatedAt DATETIME NOT NULL,
ChangedBy VARCHAR(100) NOT NULL,
ChangedAt DATETIME NOT NULL,
ChangedApp VARCHAR(100) NOT NULL
)
SET @TempTableOriginator = 1
END
ELSE
BEGIN
DELETE
FROM #ChangeLog
SET @TempTableOriginator = 0
END

DECLARE @ChangeLog TABLE
(
LogID INT IDENTITY(1, 1) NOT NULL,
QueueID INT NULL,
Description VARCHAR(255) NULL,
CreatedBy VARCHAR(100) NOT NULL,
CreatedAt DATETIME NOT NULL,
ChangedBy VARCHAR(100) NOT NULL,
ChangedAt DATETIME NOT NULL,
ChangedApp VARCHAR(100) NOT NULL
)

-- Declare table to hold actions to be performed
DECLARE @Actions TABLE
(
ActionID INT
, Ordinal INT PRIMARY KEY
, SQLStatement NVARCHAR(3700)
, PK1Value VARCHAR(64)
, PK2Value VARCHAR(64)
, PK3Value VARCHAR(64)
, PK4Value VARCHAR(64)
, PK5Value VARCHAR(64)
, PK6Value VARCHAR(64)
)

-- Initialize output variables
SELECT @ErrNo = 0
, @ErrMsg = ' '

-- Initialize declared variables
SELECT @ErrorNo = 0
, @ErrorMsg = ' '
, @StepNo = NULL
, @RowCount = 0

-------------------------------------------------------------------------------
-- Prevent Modifications Issued Against a Child Ref Server
-------------------------------------------------------------------------------
SELECT @StepNo = 10

SELECT @ServerRole = Value
FROM DBConfiguration WITH (NOLOCK)
WHERE Name = 'ServerRole'
SELECT @ErrorNo = @@ERROR, @RowCount = @@ROWCOUNT
IF @ErrorNo <> 0
BEGIN
SELECT @ErrorMsg = 'Unexpected error encountered selecting
DBConfiguration: '
GOTO ErrorExit
END
IF @ServerRole <> 'MasterRefServer'
BEGIN
SELECT @ErrorMsg = 'This operation must be executed aginst the
MasterRefServer.'
GOTO ErrorExit
END

-------------------------------------------------------------------------------
-- If this is the beginning of the process, start a transaction
-------------------------------------------------------------------------------
SELECT @StepNo = 20

IF @@TRANCOUNT = 0
BEGIN
SET @TranStarted = 1
BEGIN TRAN
END
ELSE
BEGIN
SET @TranStarted = 0
END

-------------------------------------------------------------------------------
-- Retrieve the details of item to be processed
-------------------------------------------------------------------------------
SELECT @StepNo = 30

SELECT @FailOnError = FailOnError
FROM ChangeQueue
WHERE QueueID = @QueueID

INSERT INTO @Actions
(
ActionID
, Ordinal
, SQLStatement
, PK1Value
, PK2Value
, PK3Value
, PK4Value
, PK5Value
, PK6Value
)

SELECT
TA.ChangeActionID
, TA.Ordinal
, CA.SQLStatement
, Q.PK1Value
, Q.PK2Value
, Q.PK3Value
, Q.PK4Value
, Q.PK5Value
, Q.PK6Value
FROM ChangeQueue Q
JOIN ChangeTypeChangeAction TA ON TA.ChangeTypeID = Q.ChangeTypeID
JOIN ChangeAction CA ON CA.ChangeActionID = TA.ChangeActionID
WHERE QueueID = @QueueID
AND TA.Enabled = 1

-------------------------------------------------------------------------------
-- Execute the actions for the items to be processed
-------------------------------------------------------------------------------
SELECT @StepNo = 40

WHILE (SELECT COUNT(*) FROM @Actions) > 0
BEGIN

SELECT @Ordinal = MIN(Ordinal)
FROM @Actions

SELECT @ParamDef = N'@ErrNo INT OUTPUT'
SELECT @ParamDef = @ParamDef + N', @ErrMsg NVARCHAR(510) OUTPUT'
SELECT @ParamDef = @ParamDef + N', @RowsAffected INT OUTPUT'
SELECT @ParamDef = @ParamDef + N', @QueueID INT'
SELECT @ParamDef = @ParamDef + N', @LogLevelID INT'
SELECT @ParamDef = @ParamDef + N', @PK1Value VARCHAR(64)'
SELECT @ParamDef = @ParamDef + N', @PK2Value VARCHAR(64)'
SELECT @ParamDef = @ParamDef + N', @PK3Value VARCHAR(64)'
SELECT @ParamDef = @ParamDef + N', @PK4Value VARCHAR(64)'
SELECT @ParamDef = @ParamDef + N', @PK5Value VARCHAR(64)'
SELECT @ParamDef = @ParamDef + N', @PK6Value VARCHAR(64)'
SELECT @ParamDef = @ParamDef + N', @DebugMode BIT'

SELECT @SQLStatement = N'EXEC ' + SQLStatement
+ ' @ErrNo = @ErrNo OUTPUT'
+ ', @ErrMsg = @ErrMsg OUTPUT'
+ ', @RowsAffected = @RowsAffected OUTPUT'
+ ', @QueueID = @QueueID'
+ ', @LogLevelID = @LogLevelID'
+ ', @PK1Value = @PK1Value'
+ ', @PK2Value = @PK2Value'
+ ', @PK3Value = @PK3Value'
+ ', @PK4Value = @PK4Value'
+ ', @PK5Value = @PK5Value'
+ ', @PK6Value = @PK6Value'
+ ', @DebugMode = @DebugMode'
, @PK1Value = PK1Value

Hi
1) Google for "parameter sniffing"
2) You can create an index/s on tempoary table as well as onm permanent
tables




[quoted text, click to view]