I am working this stored proc which was there in my DB , part of early development.
I am in the process. of performance tuning by removing the cursors used.
Please guide me in this process.
CREATE PROCEDURE [dbo].usp_StatusHistory
@specimensCodes NVARCHAR (4000)
AS
-- Declare temp tables
CREATE TABLE #ItemHistory
(
storingEventSqlId SMALLINT,
storingEventIncId INT ,
containerSqlId SMALLINT,
containerIncId INT ,
storingEventModifiedOn DATETIME,
storingEventModifiedBySqlId SMALLINT,
storingEventModifiedByIncId SMALLINT
);
CREATE TABLE #Result
(
specimenCode NVARCHAR (25) NOT NULL,
clientSampleCode NVARCHAR (50) ,
internalSampleCode NVARCHAR (50) ,
dateIn DATETIME ,
operatorName NVARCHAR (120),
location NVARCHAR (MAX) NOT NULL
);
-- Declare variables
DECLARE @debug AS INT;
DECLARE @specimenCode AS NVARCHAR (25);
DECLARE @samplePartnerCode AS NVARCHAR (25);
DECLARE @internalSampleCode AS NVARCHAR (50);
DECLARE @clientSampleCode AS NVARCHAR (50);
DECLARE @specimensCodesCursor AS CURSOR;
DECLARE @samplePartnerKeyCursor AS CURSOR;
DECLARE @samplePartnerSqlId AS SMALLINT;
DECLARE @samplePartnerIncId AS INT;
DECLARE @itemHistoryCursor AS CURSOR;
DECLARE @modifiedOn AS DATETIME;
DECLARE @typeOfEventName AS NVARCHAR (50);
DECLARE @parentContainerName AS NVARCHAR (50);
DECLARE @operatorName AS NVARCHAR (120);
SET @debug = 0;
SET @specimensCodesCursor = CURSOR
FOR SELECT *
FROM dbo.eufn_e5_eLIMS_SE_Parse_Delimited_List (@specimensCodes, ',');
SET @itemHistoryCursor = CURSOR
FOR SELECT IH.storingEventModifiedOn,
O.OperatorName
FROM #ItemHistory AS IH
INNER JOIN
Operators AS O
ON O.OperatorSqlId = IH.storingEventModifiedBySqlId
AND O.OperatorIncId = IH.storingEventModifiedByIncId
AND O.isDeleted = 0
ORDER BY IH.storingEventModifiedOn ASC;
-- Code
IF @debug = 1
SELECT 'START eusp_e5_eSM_SE_GetHistoryForSpecimen',
@specimensCodes AS specimensCodes;
OPEN @specimensCodesCursor;
FETCH NEXT FROM @specimensCodesCursor INTO @specimenCode;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @debug = 1
SELECT @specimenCode AS specimenCode;
SET @specimenCode = '%' + @specimenCode + '%';
SET @samplePartnerKeyCursor = CURSOR
FOR SELECT samplePartnerCode,
clientSampleCode,
internalSampleCode,
samplePartnerSqlId,
samplePartnerIncId
FROM SamplesPartners
WHERE samplePartnerCode LIKE @specimenCode
OR clientSampleCode LIKE @specimenCode
OR internalSampleCode LIKE @specimenCode;
OPEN @samplePartnerKeyCursor;
FETCH @samplePartnerKeyCursor INTO @samplePartnerCode, @clientSampleCode, @internalSampleCode, @samplePartnerSqlId, @samplePartnerIncId;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @debug = 1
SELECT @samplePartnerSqlId AS samplePartnerSqlId,
@samplePartnerIncId AS samplePartnerIncId;
EXECUTE [dbo].eusp_e5_eSM_SE_GetHistoryForItem NULL, NULL, @samplePartnerSqlId, @samplePartnerIncId, 2, 293;
IF @debug = 1
SELECT 'RESULT #ItemHistory',
*
FROM #ItemHistory;
OPEN @itemHistoryCursor;
FETCH NEXT FROM @itemHistoryCursor INTO @modifiedOn, @operatorName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @debug = 1
SELECT 'RESULT #Parents',
*
FROM #Parents;
DECLARE @location AS NVARCHAR (MAX);
SET @location = [dbo].eufn_e5_eSM_SE_GetItemLocation(@modifiedOn, @samplePartnerSqlId, @samplePartnerIncId, 2, 293);
IF @debug = 1
SELECT @location AS location;
INSERT INTO #Result
SELECT @samplePartnerCode,
@clientSampleCode,
@internalSampleCode,
@modifiedOn,
@operatorName,
@location;
FETCH NEXT FROM @itemHistoryCursor INTO @modifiedOn, @operatorName;
END
CLOSE @itemHistoryCursor;
DELETE #ItemHistory;
FETCH @samplePartnerKeyCursor INTO @samplePartnerCode, @clientSampleCode, @internalSampleCode, @samplePartnerSqlId, @samplePartnerIncId;
END
CLOSE @samplePartnerKeyCursor;
FETCH NEXT FROM @specimensCodesCursor INTO @specimenCode;
END
CLOSE @specimensCodesCursor;
DROP TABLE #ItemHistory;
SELECT *
FROM #Result
ORDER BY internalSampleCode ASC, clientSampleCode ASC, dateIn ASC;
I have started like converting the first cursor, @specimensCodesCursor
CREATE #Temp_SpecimenCodes(ID INT IDENTITY(1,1), specimensCodes NVARCHAR(4000)
INSERT INTO #Temp_SpecimenCodes
SELECT *
FROM dbo.eufn_e5_eLIMS_SE_Parse_Delimited_List (@specimensCodes, ',');
Please comment whether is this is the way to proceed on this. Thanks in advance.