Cursor and cursorless performance

Hi

I am doing the Serial Balance Report

The data is given in a range format i need to culculate the balance,for your information below is an example

Given Stock In Serial Range

10000 - 19999

Stock Out

15000 - 15999

Balance (this is what i calculate out)

10000-14999

16000 -19999

First Loop

BEGIN

--Given the Stock Out Get the Source Stock In and put it into another cursor(Normarlly just 1 record will found)

SECOND LOOP

BEGIN

--calculation

END

END

Previously Im using 2 cursor to loop and perform the calculation, due to the slow performance i change it to cursorless.

I have found that the first 1000 of 55492(2 Month) data its use almost 10 min to finish up the loop, 2000 used 30 min. The performance is decreasing, I have use up to 17 hour but still havent finish 55492 records.

Please Advice.

You are going to have to be more specific, I think, unless this exact problem reminds them of a problem they have. Can you build a small example and show how you have done it?|||

IF OBJECT_ID('tempdb..#tblBalance') IS NOT NULL
DROP Table #tblBalance

Create Table #tblBalance
(
RowID numeric(18,0) IDENTITY(1, 1) not null,
SerialNoFrom nvarchar(50),
SerialNoTo nvarchar(50))

Declare @.SerialNoFrom varchar(50)
Declare @.SerialNoTo varchar(50)
Declare @.FrontSerialNoFrom nvarchar(50)
Declare @.EndSerialNoTo nvarchar(50)
Declare @.FrontSerialNoFromNext nvarchar(50)
Declare @.EndSerialNoToNext nvarchar(50)
Declare @.SKUCode nvarchar(50)
Declare @.SLoc nvarchar(10)
Declare @.Tmp nvarchar(12)
Declare @.tmpCount integer
Declare @.sql varchar(100)

Declare @.count int
Declare @.iRow int
Declare @.countNext int
Declare @.iRowNext int

SET @.count = (SELECT MAX(RowID) FROM multicom_Qty_Minus)

SET @.iRow = 1

Print 'Total Count' + convert(varchar,@.count)
WHILE @.iRow <= @.count
BEGIN
PRINT 'Current Count ' + Convert(varchar,@.iRow)
PRINT 'GET SERIAL NO FROM'
SET @.SerialNoFrom = (SELECT SerialNoFrom FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SERIAL NO TO'
SET @.SerialNoTo = (SELECT SerialNoTo FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SLOC'
SET @.SLoc = (SELECT SLoc FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SKUCode'
SET @.SKUCode = (SELECT SKUCode FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)

PRINT @.SerialNoFrom + '-' + @.SerialNoTo
PRINT 'SKUCode ' + @.SKUCode
PRINT 'SLoc ' + @.SLoc

INSERT INTO #tblBalance
SELECT SerialNoFrom,SerialNoTo FROM multicom WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND
QuantityPlus > 0 AND
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) = Quantity AND
LEN(SerialNoTo) = LEN(@.SerialNoTo) AND LEN(SerialNoFrom) = LEN(@.SerialNoFrom) AND
SKUCode = @.SKUCode AND SLoc = @.SLoc AND
convert(numeric,SerialNoFrom) <> convert(numeric,@.SerialNoFrom) AND
convert(numeric,SerialNoTo)<>convert(numeric,@.SerialNoTo)AND
((CONVERT(numeric,SerialNoFrom) <= CONVERT(numeric,@.SerialNoFrom) AND
CONVERT(numeric,SerialNoTo) >= CONVERT(numeric,@.SerialNoTo)) OR
(CONVERT(numeric,SerialNoTo) >= CONVERT(numeric,@.SerialNoTo) AND
CONVERT(numeric,SerialNoFrom)<= CONVERT(numeric,@.SerialNoFrom)))

SET @.countNext = @.@.ROWCOUNT
SET @.iRowNext = 1

-IF @.countNext == 0 then put the Searil into the ErrorQtyMinus
/*
--
--
*/
IF @.CountNext = 0
BEGIN
INSERT INTO multicom_Error_Qty_Minus
SELECT Plnt,SLoc,
SLocDescription,SKUCode,MvT,PostDate,DocDate,[Serial No (from Material Doc.)],[Serial No (from Delivery Item)],
MatDoc,Item,InvNo,Quantity,Sign,Payer,UserID,PONo,SOff,DeliveryNo,PODate,SOrg,Salesman,MatGroup,DC,SoldTo,CoCd,InvoiceDt,
SerialNoTo,SerialNoFrom,SLocFrom,SLocTo,SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,PrdGrp4,QuantityPlus,QuantityMinus
FROM multicom_Qty_Minus WHERE RowID = @.iRow
END
PRINT 'Total 2nd loop COUNT'
PRINT @.countNext
WHILE @.iRowNext <= @.countNext
BEGIN
Print 'Current 2nd loop Count ' + convert(varchar,@.iRowNext)
SET @.FrontSerialNoFrom = (SELECT SerialNoFrom From #tblBalance WHERE RowID = @.iRowNext)
SET @.EndSerialNoTo = (SELECT SerialNoTo From #tblBalance WHERE RowID = @.iRowNext)
Print @.FrontSerialNoFrom + '-' + @.EndSerialNoTo

IF ISNUMERIC(@.FrontSerialNoFrom) = 1 AND ISNUMERIC(@.EndSerialNoTo) = 1 AND ISNUMERIC(@.SerialNoFrom)=1 AND ISNUMERIC(@.SerialNoTo)= 1
BEGIN
IF CONVERT(numeric,@.SerialNoFrom) > CONVERT(numeric,@.FrontSerialNoFrom)
BEGIN
SET @.Tmp = (CONVERT(numeric,@.SerialNoFrom) -1)
SET @.Tmp = REPLICATE('0',(12 - (len(@.Tmp)))) + @.Tmp
PRINT 'BALANCE'
PRINT @.FrontSerialNoFrom + '-' + CONVERT(nvarchar(12),@.Tmp)

PRINT 'QUANTITY'
PRINT (Convert(numeric,@.Tmp) - (Convert(numeric,@.FrontSerialNoFrom)) + 1)

Insert INTO multicom_Balance
Select Plnt,SLoc,SLocDescription,SKUCode,MvT,PostDate,DocDate,MatDoc,Item,
(Convert(numeric,@.Tmp) - (Convert(numeric,@.FrontSerialNoFrom)) + 1),
Convert(nvarchar(12),@.Tmp),@.FrontSerialNoFrom,SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,QuantityPlus,
QuantityMinus From multicom_Qty_Minus WHERE RowID = @.iRow
END

IF CONVERT(numeric,@.EndSerialNoTo) > CONVERT(numeric,@.SerialNoTo)
BEGIN
SET @.Tmp = (CONVERT(numeric,@.SerialNoTo) + 1)
SET @.Tmp = REPLICATE('0',(12 - (len(@.Tmp)))) + @.Tmp
PRINT 'Last Balance'
PRINT CONVERT(nvarchar(12),@.Tmp) + '-' + @.EndSerialNoTo

PRINT 'QUANTITY'
PRINT (Convert(numeric,@.EndSerialNoTo) - (Convert(numeric,@.Tmp)) + 1)
Insert INTO multicom_Balance
Select Plnt,SLoc,SLocDescription,SKUCode,MvT,PostDate,DocDate,MatDoc,Item,
(Convert(numeric,@.EndSerialNoTo) - (Convert(numeric,@.Tmp)) + 1),
@.EndSerialNoTo,CONVERT(nvarchar(12),@.Tmp),SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,QuantityPlus,
QuantityMinus From multicom_Qty_Minus WHERE RowID = @.iRow
END
SET @.FrontSerialNoFrom = ''
SET @.EndSerialNoTo = ''
END
SET @.iRowNext = @.iRowNext + 1
END
Truncate Table #tblBalance
SET @.iRow = @.iRow + 1
END

I think It is my problem not SQL SERVER problem, Is there any way i can speed up the query?

|||

I don;t know what kind of logic you are using behind this query... But I am willing to say few generic comments..

Replace the following statements

SET @.SerialNoFrom = (SELECT SerialNoFrom FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SERIAL NO TO'
SET @.SerialNoTo = (SELECT SerialNoTo FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SLOC'
SET @.SLoc = (SELECT SLoc FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SKUCode'
SET @.SKUCode = (SELECT SKUCode FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)

with

SELECT
@.SerialNoFrom=SerialNoFrom,
@.SerialNoTo =SerialNoTo,
@.SLoc=SLoc,
@.SKUCode=SKUCode
FROM
multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity

Replace the following statements

SET @.FrontSerialNoFrom = (SELECT SerialNoFrom From #tblBalance WHERE RowID = @.iRowNext)
SET @.EndSerialNoTo = (SELECT SerialNoTo From #tblBalance WHERE RowID = @.iRowNext)

with

SELECT @.FrontSerialNoFrom=SerialNoFrom, @.EndSerialNoTo=SerialNoTo From #tblBalance WHERE RowID = @.iRowNext

When you assing the values from the Select statement you need not to use SET statement; Advantage the same query executed more than once to fetch the value when you use SET, but the suggested query will be executed once and fetch all the required data....

|||

WOW,Thanks a lot.