cursorless performance

Question

Post reply

cursorless performance

CHAN YAO KANG

SSC Journeyman

Points: 80
More actions
December 19, 2006 at 11:19 pm

#193541

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

Is it a other way to speed up the SQL performance other than using cursorless

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
Above is the code example

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andy B. SSC Eights! Points: 890 More actions · Answer 1

I think there is room for a lot of improvement.

Let's start by reducing the number of reads from 4 queries down to 1.

Replace "SET @SerialNoFrom = (...", "SET @SerialNoTo = (...", "SET @SLoc = (...", and "SET @SKUCode = (..." with the following:

------- BEGIN CODE -------

PRINT 'GET SERIAL NO FROM, SERIAL NO TO, SLOC, AND SKUCode'

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)

------- END CODE -------

Now, all of your variables are set.

More to come in a couple minutes...

Enjoy!

A Brown

Manage all of your backups in one simple job using this script.

Andy B. SSC Eights! Points: 890 More actions · Answer 2

Second...

ISNUMERIC will return 0 for '' AND NULL values, so there's no need to check for '' in the where statements...

Also, there's no need to check your @SerialNo variables again to see if they are still numeric, as you did that when you populated them.

Enjoy!

A Brown

Manage all of your backups in one simple job using this script.

Andy B. SSC Eights! Points: 890 More actions · Answer 3

Unfortunately, I've run out of time...

But here's a cleaner version of the script, which should help others help you.

I may have more time later as well.

------ BEGIN CODE ------

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 INT

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 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,@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 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 = ''

SET @iRowNext = @iRowNext + 1

END

TRUNCATE TABLE #tblBalance

SET @iRow = @iRow + 1

END

------ END CODE ------

Enjoy!

A Brown

Manage all of your backups in one simple job using this script.