<msnews.microsoft.com>
3/18/2007 5:56:00 AM
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
, @PK2Value = PK2Value
, @PK3Value = PK3Value
, @PK4Value = PK4Value
, @PK5Value = PK5Value
, @PK6Value = PK6Value
, @ActionID = ActionID
FROM @Actions
WHERE Ordinal = @Ordinal
IF @LogLevelID > 1
BEGIN
INSERT INTO #ChangeLog
(
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
)
VALUES
(
@QueueID,
'Start Action ' + CAST(ISNULL(@ActionID, 'UNKNOWN') AS VARCHAR) + ' - '
+ 'Parameters: ' + ISNULL(@PK1Value, 'NULL') + ',' + ISNULL(@PK2Value,
'NULL')
+ ',' + ISNULL(@PK3Value, 'NULL') + ',' + ISNULL(@PK4Value, 'NULL')
+ ',' + ISNULL(@PK5Value, 'NULL') + ',' + ISNULL(@PK6Value, 'NULL'),
SUSER_SNAME(),
GETDATE(),
SUSER_SNAME(),
GETDATE(),
APP_NAME()
)
END
IF @DebugMode = 1
BEGIN
PRINT '----------'
PRINT 'Processing ActionID = ' + CAST(ISNULL(@ActionID, 'NULL') AS VARCHAR)
SELECT @ActionStartTime = GETDATE()
PRINT 'Action start time = ' + CONVERT(VARCHAR, @ActionStartTime, 109)
PRINT 'Parameters = ' + ISNULL(@PK1Value, 'NULL') + ',' + ISNULL(@PK2Value,
'NULL')
+ ',' + ISNULL(@PK3Value, 'NULL') + ',' + ISNULL(@PK4Value, 'NULL')
+ ',' + ISNULL(@PK5Value, 'NULL') + ',' + ISNULL(@PK6Value, 'NULL')
END
EXEC sp_ExecuteSQL
@SQLStatement, @ParamDef,
@ErrNo OUTPUT, @ErrMsg OUTPUT, @RowsAffected OUTPUT,
@QueueID, @LogLevelID, @PK1Value, @PK2Value, @PK3Value, @PK4Value,
@PK5Value, @PK6Value, @DebugMode
SELECT @ErrorNo = @@ERROR
, @RowCount = @@ROWCOUNT
-- Check for local (process) failure
IF @ErrorNo <> 0
BEGIN
SELECT @ErrNo = @ErrorNo
, @ErrorMsg = 'Unexpected error encountered attempting to execute
action: '
, @RowsAffected = @RowCount
GOTO ErrorExit
END
-- Check for global (action) failure
IF @ErrNo <> 0
BEGIN
IF @FailOnError = 1
BEGIN
SELECT @ErrorNo = @ErrNo
, @ErrorMsg = 'Unexpected error encountered attempting to execute action:
'
, @RowsAffected = @RowCount
GOTO ErrorExit
END /* if @FailOnError = 1 */
ELSE
BEGIN
SELECT @ErrorNo = 0
, @ErrNo = 0
END
END /* if @ErrNo <> 0 */
IF @LogLevelID > 1
BEGIN
INSERT INTO #ChangeLog
(
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
)
VALUES
(
@QueueID,
'End Action ' + CAST(ISNULL(@ActionID, 'UNKNOWN') AS VARCHAR),
SUSER_SNAME(),
GETDATE(),
SUSER_SNAME(),
GETDATE(),
APP_NAME()
)
END
DELETE FROM @Actions
WHERE Ordinal = @Ordinal
IF @DebugMode = 1
BEGIN
PRINT '@RowCount = ' + CAST(ISNULL(@RowCount, 0) AS VARCHAR)
SELECT @ActionEndTime = GETDATE()
PRINT 'Action end time = ' + CONVERT(VARCHAR, @ActionEndTime, 109)
PRINT 'Action duration = ' + CONVERT(VARCHAR, DATEPART(hh, @ActionEndTime -
@ActionStartTime)) + ':'
+ CONVERT(VARCHAR, DATEPART(mi, @ActionEndTime - @ActionStartTime))
+ ':'
+ CONVERT(VARCHAR, DATEPART(ss, @ActionEndTime - @ActionStartTime))
+ ':'
+ CONVERT(VARCHAR, DATEPART(ms, @ActionEndTime - @ActionStartTime))
END
END
-------------------------------------------------------------------------------
SuccessfulExit:
-------------------------------------------------------------------------------
IF @@TRANCOUNT > 0 AND @TranStarted > 0
BEGIN
COMMIT TRAN
INSERT INTO ChangeLog
(
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
)
SELECT
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
FROM #ChangeLog
ORDER BY LogID
END
IF @TempTableOriginator = 1
BEGIN
DROP TABLE #ChangeLog
END
RETURN
-------------------------------------------------------------------------------
ErrorExit:
-------------------------------------------------------------------------------
IF @@TRANCOUNT > 0 AND @TranStarted > 0
BEGIN
-- Pick up any logging so far
INSERT INTO @ChangeLog
(
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
)
SELECT
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
FROM #ChangeLog
ORDER BY LogID
ROLLBACK TRAN
INSERT INTO ChangeLog
(
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
)
SELECT
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
FROM @ChangeLog
ORDER BY LogID
END
SET @ErrorMsg = @ErrorMsg + 'Error Number = ' + CONVERT(VARCHAR, @ErrorNo)
+ ', Stored Procedure = ' + OBJECT_NAME(@@ProcID)
+ ', Date = ' + CONVERT(VARCHAR(22), GETDATE(), 121)
+ ', Database = ' + DB_NAME()
+ ', Server name = ' + @@SERVERNAME
+ ', Step Number = ' + CONVERT(VARCHAR, @StepNo)
INSERT INTO ChangeLog
(
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
)
VALUES
(
@QueueID,
@ErrorMsg,
SUSER_SNAME(),
GETDATE(),
SUSER_SNAME(),
GETDATE(),
APP_NAME()
)
IF @FailOnError = 1 AND @LogLevelID > 1
BEGIN
INSERT INTO ChangeLog
(
QueueID,
Description,
CreatedBy,
CreatedAt,
ChangedBy,
ChangedAt,
ChangedApp
)
VALUES
(
@QueueID,
'End Action ' + CAST(ISNULL(@ActionID, 'UNKNOWN') AS VARCHAR),
SUSER_SNAME(),
GETDATE(),
SUSER_SNAME(),
GETDATE(),
APP_NAME()
)
END
IF @TempTableOriginator = 1
BEGIN
DROP TABLE #ChangeLog
END
RAISERROR (@ErrorMsg, 16, 1)
RETURN
GO