Hello,
I have a certain Stored Procedure which process large amount of records. The SP is doing what it suppose to do OK but it is taking it long time to do it. Of course, part of the "blame" is the large amount of data but I think my SP implementation can be improved. Can someone here help me to improve this SP yet keep it's original logic of course untouched?
Here it is:
I have a certain Stored Procedure which process large amount of records. The SP is doing what it suppose to do OK but it is taking it long time to do it. Of course, part of the "blame" is the large amount of data but I think my SP implementation can be improved. Can someone here help me to improve this SP yet keep it's original logic of course untouched?
Here it is:
Code:
CREATE PROCEDURE [dbo].[ProcessTankNumber]
@tankNumber varchar(10), @caseYear varchar(10), @office smallint, @method smallint, @type smallint
AS
BEGIN
DECLARE @maxNumber integer
DECLARE @minNumber integer
DECLARE @counter integer
DECLARE @missingItems varchar (2000)
DECLARE @hall varchar (50)
SET @missingItems = ''
SET @minNumber = (SELECT MIN(convert(integer,case_number)) FROM ROTOPLAS_ITEMS WHERE tank_number=@tankNumber AND case_year=@caseYear AND office=@office AND method=@method AND type=@type)
SET @maxNumber = (SELECT MAX(convert(integer,case_number)) FROM ROTOPLAS_ITEMS WHERE tank_number=@tankNumber AND case_year=@caseYear AND office=@office AND method=@method AND type=@type)
SET @hall = (SELECT DISTINCT hall FROM ROTOPLAS_ITEMS WHERE tank_number=@tankNumber AND case_year=@caseYear AND office=@office AND method=@method AND type=@type)
SET @counter = @minNumber
WHILE (@counter<=@maxNumber)
BEGIN
IF NOT EXISTS ( SELECT record_number FROM ROTOPLAS_ITEMS WHERE case_number=@counter AND tank_number=@tankNumber AND case_year=@caseYear AND office=@office AND method=@method AND type=@type)
SET @missingItems = @missingItems + convert(varchar,@counter) + ', '
set @counter = @counter + 1
END
IF @missingItems = ''
BEGIN
SET @missingItems = 'No missing files'
END
IF RIGHT(@missingItems,2) = ','
BEGIN
SET @missingItems = LEFT(@missingItems,LEN(@missingItems)-1)
END
INSERT INTO ROTOPLAS_TEMP_REPORT
(tankNumber, hall, minNumber, maxNumber, missingNumbers)
VALUES
(@tankNumber, @hall, @minNumber, @maxNumber,@missingItems)
END
GO