Extended stored procedure to avoid Cursor ?
-
Hi All,
I want to avoid cursors. Will I get any advantage if doing the same thing in extended stored procedure (iterating each record using native C and apply custom instructions). Will I get performance benefits comparing to sql cursors ? Any help / suggestion will help me alot.
Thanks & Regards,
Govind Bhavan
-
There are many ways to avoid using cursors depending on how and what you want to do. I never use extended stored procedure but I am able to avoid using cursor. Unless you give me an example, it is hard to give you any suggestion.
-
Thanks loner.
Its a FIFO based costing routine. Source of cost have multiple paths, i.e. Exact item received / purchased, Std cost, Conversion of items (cost carrying & Issuing item differ from receiving item for a job) etc.
Here is the sample of code
DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR
SELECT Z.ROWNO, Z.TYPE
, Z.DEPT, Z.ACT_DEPT
, Z.ITEMTYPEID, Z.ITEMID, ISNULL(Z.SRC_ITEMID,-2) AS [SRC_ITEMID]
, CASE WHEN Z.ITEMTYPEID IN (1) THEN Z.SHADE ELSE '' END AS [SHADE], Z.REF
, ISNULL(Z.DOC,'') AS [DOC], Z.DO_SERIAL
, CASE WHEN Z.TYPE = 'R' THEN REC_VALUE ELSE ISS_VALUE END AS [VALUE]
, CASE WHEN Z.TYPE = 'R' THEN RATE ELSE ISS_RATE END AS [RATE]
, CASE WHEN Z.TYPE = 'R' THEN CB_QTY ELSE ISS_QTY END AS [QTY]
, CASE WHEN Z.TYPE = 'R' THEN CB_QTY ELSE ISS_QTY END - Z.CALC_QTY AS [QTYBAL]
, ISNULL(MAIN_DEPT.RMWAREHOUSE,0) AS [RMWAREHOUSE]
, ISNULL(MAIN_DEPT.WPDWAREHOUSE,0) AS [WPDWAREHOUSE]
, ISNULL(MAIN_DEPT.FINISHINGWAREHOUSE,0) AS [FINISHINGWAREHOUSE]
, ISNULL(MAIN_DEPT.TWISTINGWAREHOUSE,0) AS [TWISTINGWAREHOUSE]
, ISNULL(MAIN_DEPT.WBCWAREHOUSE,0) AS [WBCWAREHOUSE]
, ISNULL(MAIN_DEPT.DCWAREHOUSE,0) AS [DCWAREHOUSE]
, ISNULL(MAIN_DEPT.MUMWAREHOUSE,0) AS [MUMWAREHOUSE]
, ISNULL(MAIN_DEPT.ISMILLSTORE,0) AS [ISMILLSTORE]
, ISNULL(MAIN_DEPT.ISCOLORLAB,0) AS [ISCOLORLAB]
, ISNULL(MAIN_DEPT.ISVENDOR,0) AS [ISVENDOR]
, ISNULL(MAIN_DEPT.ISQCD,0) AS [ISQCD]
, ISNULL(MAIN_DEPT.FGWAREHOUSE,0) AS [FGWAREHOUSE]
, ISNULL(ACT_DEPT.FGWAREHOUSE,0) AS [ACT_FGWAREHOUSE]
FROM TMP_COSTLEDGER Z
INNER JOIN DEPARTMENTS MAIN_DEPT ON Z.DEPT = MAIN_DEPT.DEPTCODE
LEFT JOIN DEPARTMENTS ACT_DEPT ON Z.ACT_DEPT = ACT_DEPT.DEPTCODE
WHERE (ISNULL(MAIN_DEPT.FGWAREHOUSE,0) = 0 OR Z.SOURCE <> 'RECEIVE')
AND Z.TYPE IN ('I')
AND Z.CALC_QTY < (Z.ISS_QTY + Z.REC_QTY)
AND Z.ITEMTYPEID IN (1,2,3,4)
AND Z.SOURCE NOT IN ('OPENING','PURCHASE','PURCHASE_RETURN','SALE','SALE_RETURN','SAMPLE')
AND EXISTS (SELECT X.ROWNO FROM TMP_COSTLEDGER X
INNER JOIN DEPARTMENTS DEPT ON X.DEPT = DEPT.DEPTCODE
LEFT JOIN DEPARTMENTS TODEPT ON X.ACT_DEPT = TODEPT.DEPTCODE WHERE 1 = 1
AND ( (X.ITEMID = Z.ITEMID OR X.ITEMID = ISNULL(Z.SRC_ITEMID,0) OR Z.ITEMID = ISNULL(X.SRC_ITEMID,0) OR ISNULL(X.SRC_ITEMID,-1) = ISNULL(Z.SRC_ITEMID,0) )
OR ( (ISNULL(DEPT.WPDWAREHOUSE,0) = 1 OR ISNULL(DEPT.FINISHINGWAREHOUSE,0) = 1) AND X.ITEMTYPEID = Z.ITEMTYPEID AND X.REF = Z.REF))
AND X.TYPE IN ('I','R')
AND (X.REF = Z.REF OR ISNULL(DEPT.RMWAREHOUSE,0) = 1 OR ISNULL(DEPT.DCWAREHOUSE,0) = 1 OR ISNULL(DEPT.MUMWAREHOUSE,0) = 1 OR ISNULL(DEPT.ISMILLSTORE,0) = 1 OR ISNULL(DEPT.DCWAREHOUSE,0) = 1 OR ISNULL(DEPT.FGWAREHOUSE,0) = 1 OR ISNULL(DEPT.ISCOLORLAB,0) = 1 OR ISNULL(DEPT.ISQCD,0) = 1 OR ISNULL(DEPT.ISVENDOR,0) = 1 )
AND X.USED_QTY < (X.ISS_QTY + X.REC_QTY + X.OPEN_QTY)
AND X.CALC_QTY >= ABS(X.CB_QTY)
AND ISNULL(CASE WHEN X.TYPE = 'R' THEN X.RATE ELSE X.ISS_RATE END,0) <> 0.0
AND CASE WHEN X.TYPE = 'R' THEN X.DEPT ELSE X.ACT_DEPT END = Z.DEPT
 
AND ISNULL(CASE WHEN Z.TYPE = 'R' THEN CB_QTY ELSE ISS_QTY END - Z.CALC_QTY,0) > 0.0
ORDER BY CASE WHEN Z.DOC = 'ADJ' AND Z.TYPE = 'I' THEN 999 ELSE 1 END, Z.TYPE, Z.[DATE], Z.ITEMTYPEID, Z.COSTINGSORTNO, Z.DEPT, Z.ITEMID, Z.SHADE, CASE WHEN Z.REF = '' THEN 'Z999999' ELSE Z.REF END, Z.ROWNO
OPEN CUR
FETCH NEXT FROM CUR
INTO @ROWNO, @TYPE, @DEPT, @ACT_DEPT, @ITEMTYPEID, @ITEMID, @SRC_ITEMID, @SHADE, @REF, @DOC, @DO_SERIAL, @VALUE, @RATE, @QTY, @QTYBAL, @RMWAREHOUSE , @WPDWAREHOUSE , @FINISHINGWAREHOUSE , @TWISTINGWAREHOUSE , @WBCWAREHOUSE , @DCWAREHOUSE , @MUMWAREHOUSE , @ISMILLSTORE , @ISCOLORLAB , @ISVENDOR , @ISQCD , @FGWAREHOUSE, @ACT_FGWAREHOUSE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TOTAL_CALCQTY = 0
WHILE (1 = 1)
BEGIN
IF (@QTYBAL > 0.0)
BEGIN
SET @CURRUSED = 0
SET @ROWFOUND = NULL
SELECT @ROWFOUND = MIN(Z.ROWNO) FROM TMP_COSTLEDGER Z
INNER JOIN DEPARTMENTS DEPT ON Z.DEPT = DEPT.DEPTCODE
LEFT JOIN DEPARTMENTS TODEPT ON Z.ACT_DEPT = TODEPT.DEPTCODE
WHERE Z.ROWNO <> @ROWNO AND Z.ITEMID IN (@ITEMID, @SRC_ITEMID)
AND Z.TYPE IN ('I','R') AND (@ITEMTYPEID <> 1 OR Z.SHADE = @SHADE)
AND Z.USED_QTY < (Z.ISS_QTY + Z.REC_QTY + Z.OPEN_QTY)
AND (@RMWAREHOUSE = 1 OR @DCWAREHOUSE = 1 OR @MUMWAREHOUSE = 1 OR @FGWAREHOUSE = 1 OR @ISMILLSTORE = 1 OR Z.REF = @REF OR @REF = '')
AND ISNULL(CASE WHEN Z.TYPE = 'R' THEN Z.RATE ELSE Z.ISS_RATE END,0) <> 0.0
AND CASE WHEN Z.TYPE = 'R' THEN Z.DEPT ELSE Z.ACT_DEPT END = @DEPT
AND Z.CALC_QTY >= ABS(Z.CB_QTY)
IF ((@ROWFOUND IS NULL) AND (@WPDWAREHOUSE = 0 AND @FINISHINGWAREHOUSE = 0))
SELECT @ROWFOUND = MIN(Z.ROWNO) FROM TMP_COSTLEDGER Z
INNER JOIN DEPARTMENTS DEPT ON Z.DEPT = DEPT.DEPTCODE
LEFT JOIN DEPARTMENTS TODEPT ON Z.ACT_DEPT = TODEPT.DEPTCODE
WHERE Z.ROWNO <> @ROWNO AND Z.SRC_ITEMID IN (@ITEMID, @SRC_ITEMID)
AND Z.TYPE IN ('I','R') AND (@ITEMTYPEID <> 1 OR Z.SHADE = @SHADE)
AND Z.USED_QTY < (Z.ISS_QTY + Z.REC_QTY + Z.OPEN_QTY)
AND (@RMWAREHOUSE = 1 OR @DCWAREHOUSE = 1 OR @MUMWAREHOUSE = 1 OR @FGWAREHOUSE = 1 OR @ISMILLSTORE = 1 OR Z.REF = @REF OR @REF = '')
AND ISNULL(CASE WHEN Z.TYPE = 'R' THEN Z.RATE ELSE Z.ISS_RATE END,0) <> 0.0
AND CASE WHEN Z.TYPE = 'R' THEN Z.DEPT ELSE Z.ACT_DEPT END = @DEPT
AND Z.CALC_QTY >= ABS(Z.CB_QTY) AND Z.SRC_ITEMID IS NOT NULL
ELSE IF ((@ROWFOUND IS NULL) AND (@WPDWAREHOUSE = 1 OR @FINISHINGWAREHOUSE = 1))
BEGIN
SELECT @ROWFOUND = MIN(Z.ROWNO) FROM TMP_COSTLEDGER Z WHERE
Z.ROWNO <> @ROWNO AND Z.ITEMID IN (@ITEMID, @SRC_ItemId)
AND Z.TYPE IN ('R') AND (@ITEMTYPEID <> 1 OR Z.SHADE = @SHADE)
AND Z.USED_QTY < (Z.ISS_QTY + Z.REC_QTY + Z.OPEN_QTY)
AND Z.REF = @REF
AND ISNULL(CASE WHEN Z.TYPE = 'R' THEN Z.RATE ELSE Z.ISS_RATE END,0) <> 0.0
AND CASE WHEN Z.TYPE = 'R' THEN Z.DEPT ELSE Z.ACT_DEPT END = @DEPT
AND Z.CALC_QTY >= ABS(Z.CB_QTY)
IF (@ROWFOUND IS NULL)
SELECT @ROWFOUND = MIN(Z.ROWNO) FROM TMP_COSTLEDGER Z WHERE
Z.ROWNO <> @ROWNO AND Z.ITEMTYPEID = @ITEMTYPEID
AND Z.TYPE IN ('R') AND (@ITEMTYPEID <> 1 OR Z.SHADE = @SHADE)
AND Z.USED_QTY < (Z.ISS_QTY + Z.REC_QTY + Z.OPEN_QTY)
AND Z.REF = @REF
AND ISNULL(CASE WHEN Z.TYPE = 'R' THEN Z.RATE ELSE Z.ISS_RATE END,0) <> 0.0
AND CASE WHEN Z.TYPE = 'R' THEN Z.DEPT ELSE Z.ACT_DEPT END = @DEPT
AND Z.CALC_QTY >= ABS(Z.CB_QTY)
IF (@ROWFOUND IS NULL)
SELECT @ROWFOUND = MIN(Z.ROWNO) FROM TMP_COSTLEDGER Z WHERE
Z.ROWNO <> @ROWNO AND Z.SRC_ITEMID IN (@ITEMID, @SRC_ItemId)
AND Z.TYPE IN ('R') AND (@ITEMTYPEID <> 1 OR Z.SHADE = @SHADE)
AND Z.USED_QTY < (Z.ISS_QTY + Z.REC_QTY + Z.OPEN_QTY)
AND Z.REF = @REF
AND ISNULL(CASE WHEN Z.TYPE = 'R' THEN Z.RATE ELSE Z.ISS_RATE END,0) <> 0.0
AND CASE WHEN Z.TYPE = 'R' THEN Z.DEPT ELSE Z.ACT_DEPT END = @DEPT
AND Z.CALC_QTY >= ABS(Z.CB_QTY) AND Z.SRC_ITEMID IS NOT NULL
END --NOT FOUND
IF (@ROWFOUND IS NOT NULL)
BEGIN
SET @bRecordFound = 1
SELECT @FOUND_VALUE = CASE WHEN Z.TYPE = 'R' THEN Z.CB_VALUE ELSE Z.ISS_VALUE END
, @FOUND_RATE = CASE WHEN Z.TYPE = 'R' THEN Z.RATE ELSE Z.ISS_RATE END
, @FOUND_QTY = CASE WHEN Z.TYPE = 'R' THEN Z.CB_QTY ELSE Z.ISS_QTY END
, @FOUND_USEDQTY = Z.USED_QTY
FROM TMP_COSTLEDGER Z WHERE Z.ROWNO = @ROWFOUND
IF (@FOUND_RATE IS NULL)
RAISERROR ('RATE IS NULL', 16, 1)
If ((@FOUND_QTY - @FOUND_USEDQTY) >= @QTYBAL)
SET @CURRUSED = @QTYBAL
Else
SET @CURRUSED = (@FOUND_QTY - @FOUND_USEDQTY)
SET @QTYBAL = @QTYBAL - @CURRUSED
UPDATE TMP_COSTLEDGER SET USED_QTY = USED_QTY + @CURRUSED WHERE ROWNO = @ROWFOUND
SET @VALUE = (@VALUE + (@CURRUSED * @FOUND_RATE))
If (@CURRUSED > 0.0)
BEGIN
INSERT INTO TMP_COSTLEDGER_DETAIL (ROWFK, REF_ROWFK, COSTTYPEID, STAGEID, ITEMTYPEID, QTY, RATE, VALUE,
ACTUAL_COST, WIP, REMARKS, INDIRECT, OPENING_ENTRY)
SELECT @ROWNO, @ROWFOUND, 1 AS [COSTTYPEID], NULL STAGEID, @ITEMTYPEID,
@CURRUSED, @FOUND_RATE, @CURRUSED * @FOUND_RATE AS VALUE, 0 AS ACTUAL_COST, 0 AS WIP,
'MAT FIFO ' + CAST(@iLoopCounter AS VARCHAR(5)), 0 AS INDIRECT, 0 AS OPENING_ENTRY
UPDATE TMP_COSTLEDGER SET
@TOTAL_CALCQTY = CALC_QTY + @CURRUSED
, CALC_QTY = CALC_QTY + @CURRUSED
, REC_VALUE = CASE WHEN TYPE IN ('R') THEN @VALUE ELSE ISNULL(REC_VALUE,0) END
, ISS_VALUE = CASE WHEN TYPE IN ('I') THEN @VALUE ELSE ISNULL(ISS_VALUE,0) END
, REC_RATE = CASE WHEN TYPE IN ('R') THEN @VALUE / @QTY ELSE ISNULL(REC_RATE,0) END
, ISS_RATE = CASE WHEN TYPE IN ('I') THEN @VALUE / @QTY ELSE ISNULL(ISS_RATE,0) END
, RATE = @VALUE / @QTY
, CB_VALUE = CASE WHEN TYPE IN ('I') THEN -1 ELSE 1 END * @VALUE
, CB_RATE = @VALUE / @QTY
WHERE ROWNO = @ROWNO
END --CURRUSED > 0.0
END --FOUND
ELSE IF (@ROWFOUND IS NULL)
GOTO INNER_SKIP1
END -- QTYBAL
IF (ROUND(@QtyBal, 4) <= 0) AND (ABS(@TOTAL_CALCQTY - @QTY) > 0)
BEGIN
UPDATE TMP_COSTLEDGER SET @CALC_QTY = @QTY, CALC_QTY = @QTY WHERE ROWNO = @ROWNO
SET @QtyBal = 0
SET @TOTAL_CALCQTY = @QTY
END
IF ((@TYPE = 'I') AND (@DOC NOT IN ('ADJ','')) AND (@QTY <> 0.0))
BEGIN
SET @b-2 = 0
IF ((@TOTAL_CALCQTY >= @QTY) OR (@B = 1))
BEGIN
IF ((@ACT_FGWAREHOUSE = 0) OR (@ITEMTYPEID = 4))
BEGIN
SET @@USED_QTY_IN_REF = 0
SELECT @RATE = RATE, @USED_QTY = ISNULL(USED_QTY,0), @CALC_QTY = CALC_QTY FROM TMP_COSTLEDGER WHERE ROWNO = @ROWNO
EXEC CostProc_UpdateRefCost @ROWNO , @DEPT, @ACT_DEPT ,
@TYPE , @ITEMTYPEID , @ITEMID , @SRC_ITEMID ,
@SHADE , @REF , @DOC , @DO_SERIAL ,
@VALUE , @RATE , @QTY , @CALC_QTY , @USED_QTY ,
@@USED_QTY_IN_REF OUTPUT
, @iLoopCounter
IF (ISNULL(@@USED_QTY_IN_REF,0) <> 0)
UPDATE TMP_COSTLEDGER SET USED_QTY = @@USED_QTY_IN_REF WHERE ROWNO = @ROWNO
END --NOT TO FWH OR MUM
END --UPDATE RECEIVING
END --TYPE = 'I'
SET @b-2 = CASE WHEN (@ROWFOUND IS NULL) THEN 1 ELSE 0 END
IF (@B = 0)
SET @b-2 = CASE WHEN ABS(@QTY) - @TOTAL_CALCQTY <= 0 THEN 1 ELSE 0 END
IF (@B = 1)
GOTO INNER_SKIP1 --ESCAPE FROM INFINITE LOOP & GOTO MAIN RECORD LOOP
END --INNERLOOP
INNER_SKIP1:
FETCH NEXT FROM CUR
INTO @ROWNO, @TYPE, @DEPT, @ACT_DEPT, @ITEMTYPEID, @ITEMID, @SRC_ITEMID, @SHADE, @REF, @DOC, @DO_SERIAL, @VALUE, @RATE, @QTY, @QTYBAL, @RMWAREHOUSE , @WPDWAREHOUSE , @FINISHINGWAREHOUSE , @TWISTINGWAREHOUSE , @WBCWAREHOUSE , @DCWAREHOUSE , @MUMWAREHOUSE , @ISMILLSTORE , @ISCOLORLAB , @ISVENDOR , @ISQCD , @FGWAREHOUSE, @ACT_FGWAREHOUSE
END --CURSOR
CLOSE CUR
DEALLOCATE CUR
END -- @iLoopCounter <= 2
The procedure also transfer computed cost to other production area until in Finished form, by exeucting another stored procedure write after completion of every single transaction. Right now the server is executing 3 transactions per second. Approx I have 50000 transaction to compute viz 5 Hrs.
Please help me to boost this thing.
Regards,
Govind Bhavan
-
Additionally is Sql 2005 CLR integration can be a good solution for this problem w.r.t performance ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply