Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi,
 
The function below takes a very long time nearly 2 hours to execute. Can any body tell how to optimize this function??
 
Or how can we re-write this function without using Cursors??
 
I tried SQL Profiler and Tuning also but of no help.
 
Please help me get out of this.
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
Alter FUNCTION [dbo].[FUNC_STOCK_SUMMARY_NEW_04012013](@F_WAREHOUSE NVARCHAR(20),@T_WAREHOUSE NVARCHAR(20), @F_ITEMGROUP NVARCHAR(20), @T_ITEMGROUP NVARCHAR(20), @F_DATE DATETIME,@T_DATE DATETIME)
RETURNS
@TAB_STOCK Table (WH NVARCHAR(20),ITEMGROUPNAME NVARCHAR(100),ITEMCODE NVARCHAR(20), ITEMDESC NVARCHAR(100), OB NUMERIC(16,4), OB_VAL NUMERIC(16,4), IN_QTY NUMERIC(16,4), IN_VAL NUMERIC(16,4), ITMGRP_IN_QTY NUMERIC(16,4), ITMGRP_IN_VAL NUMERIC(16,4), OUT_QTY NUMERIC(16,4), OUT_VAL NUMERIC(16,4), ITMGRP_OUT_QTY NUMERIC(16,4), ITMGRP_OUT_VAL NUMERIC(16,4), CB_QTY NUMERIC(16,4), CB_VAL NUMERIC(16,4),packcartons NUMERIC(16,4),itmprice NUMERIC(16,4),ITMGRPCD NVARCHAR(20),DTE DATETIME)
AS
BEGIN
	
	DECLARE @WAREHOUSE NVARCHAR(20), @ITEMCODE NVARCHAR(20), @ITEMDESC NVARCHAR(100), @ITEMGROUP NVARCHAR(20), @OPENQTY NUMERIC(16,4), @OPENVALUE NUMERIC(16,4), @RECEIPTQTY NUMERIC(16,4), @RECEIPTVALUE NUMERIC(16,4), @GROUPTOT_RECEIPTQTY NUMERIC(16,4), @GROUPTOT_RECEIPTVALUE NUMERIC(16,4), @ISSUEDQTY NUMERIC(16,4), @ISSUEDVALUE NUMERIC(16,4), @CLOSINGQTY NUMERIC(16,4), @CLOSINGVALUE NUMERIC(16,4), @GROUPTOT_ISSUEDQTY NUMERIC(16,4), @GROUPTOT_ISSUEDVALUE NUMERIC(16,4)
 
	DECLARE @DATE DATETIME 
	DECLARE @INQTY NUMERIC(16,4), @OUTQTY NUMERIC(16,4), @TRANSTYPE NVARCHAR(6), @CALCPRICE NUMERIC(16,4)
 
	DECLARE @TOT_IN NUMERIC(16,4), @TOT_OUT NUMERIC(16,4), @TOT_IN_PRICE NUMERIC(16,4), @TOT_OUT_PRICE NUMERIC(16,4)
 
	DECLARE @OPENQTYPRICE NUMERIC(16,4), @OpQty NUMERIC(16,4), @OpVal NUMERIC(16,4), @ClQty NUMERIC(16,4), @ClVal NUMERIC(16,4)
 
	DECLARE @PREVDAYCLOSING NUMERIC(16,4), @PREVDAYCLOSINGVAL NUMERIC(16,4), @DAYOPENINGBALANCE NUMERIC(16,4), @DAYOPENINGBALANCEVAL NUMERIC(16,4), @DAYCLOSINGBALANCE NUMERIC(16,4), @DAYCLOSINGBALANCEVAL NUMERIC(16,4)
 
	DECLARE @TAB_DAY_STOCK TABLE (WH NVARCHAR(20),ITMCORTON NUMERIC(16,4), ITEMGROUP NVARCHAR(20),ITEMCODE NVARCHAR(20), DATE DATETIME, OB NUMERIC(16,4), OB_VAL NUMERIC(16,4), IN_QTY NUMERIC(16,4), IN_QTY_VAL NUMERIC(16,4), OUT_QTY NUMERIC(16,4), OUT_QTY_VAL NUMERIC(16,4), CB_QTY NUMERIC(16,4), CB_QTY_VAL NUMERIC(16,4), INV_REV_FLAG INT,itmprice NUMERIC(16,4))
 
	DECLARE @ST_DATE DATETIME, @EN_DATE DATETIME, @IN_DATE DATETIME
 
	DECLARE @ITMCOD NVARCHAR(20)
 
	DECLARE @INV_REVALUE NUMERIC(16,4), @INV_REV_FLAG INT
 
	DECLARE @TOT_BALANCE NUMERIC(16,4), @TOT_TRANS_BALANCE NUMERIC(16,4)
 
    declare @packunit NUMERIC(16,4), @packcartons NUMERIC(16,4),@itmprice NUMERIC(16,4) 
 
    SET @packunit = 0
    SET @packcartons = 0
	SET @TOT_BALANCE = 0
	SET @TOT_TRANS_BALANCE = 0
    
	SET @INV_REV_FLAG = 0
 
	SET @ST_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE)
	SET @EN_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE DESC)
	SET @DATE = @ST_DATE
 
	DECLARE CUR_WAREHOUSE CURSOR FOR SELECT WHSCODE FROM OWHS WHERE ((WHSCODE >= @F_WAREHOUSE AND WHSCODE <= @T_WAREHOUSE) OR (@F_WAREHOUSE = '' AND @T_WAREHOUSE = '')) ORDER BY WHSCODE
	OPEN CUR_WAREHOUSE
	FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
 
		DECLARE CUR_ITEMGROUP CURSOR FOR SELECT ITMSGRPCOD FROM OITB 
WHERE ((ITMSGRPCOD = @F_ITEMGROUP AND ITMSGRPCOD <= @T_ITEMGROUP) 
OR (@F_ITEMGROUP = ''AND @T_ITEMGROUP = '')) 
ORDER BY ITMSGRPCOD
		OPEN CUR_ITEMGROUP
		FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
 
			DECLARE CUR_ITEM CURSOR FOR SELECT DISTINCT T6.ITEMCODE FROM OINM T6 WHERE ((T6.ITEMCODE IN (SELECT T0.[ItemCode] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.ITMSGRPCOD = @ITEMGROUP)) AND (T6.WAREHOUSE = @WAREHOUSE)) ORDER BY T6.ITEMCODE
			OPEN CUR_ITEM
			FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
			WHILE(@@FETCH_STATUS = 0)
			BEGIN
 
				SET @PREVDAYCLOSING = 0
				SET @PREVDAYCLOSINGVAL = 0
 

				SET @ST_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE)
				SET @EN_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE DESC)
				SET @DATE = @ST_DATE
 
				WHILE (@DATE <= @EN_DATE)
				BEGIN
 
					SET @INQTY = 0
					SET @OUTQTY = 0
					SET @CALCPRICE = 0
					SET @TOT_IN = 0
					SET @TOT_OUT = 0
					SET @TOT_IN_PRICE = 0
					SET @TOT_OUT_PRICE = 0
					SET @OPENQTY = 0
					SET @OPENQTYPRICE = 0
 
					SET @DAYOPENINGBALANCE = 0
					SET @DAYOPENINGBALANCEVAL = 0
 
					SET @DAYCLOSINGBALANCE = 0
					SET @DAYCLOSINGBALANCEVAL = 0
 
					SET @INV_REVALUE = 0
 
					DECLARE CUR_ITEM_IN CURSOR FOR SELECT  (CONVERT(NUMERIC(16,4), T6.INQTY)), (CONVERT(NUMERIC(16,4), T6.OUTQTY)), (CONVERT(NUMERIC(16,4), T6.TRANSVALUE)), T6.TRANSTYPE  FROM OINM T6 WHERE ((T6.ITEMCODE = @ITEMCODE) AND (T6.WAREHOUSE = @WAREHOUSE) AND (T6.DOCDATE = @DATE) and t6.transtype <> 67)
		
			OPEN CUR_ITEM_IN
					FETCH NEXT FROM CUR_ITEM_IN INTO @INQTY, @OUTQTY, @CALCPRICE, @TRANSTYPE
					WHILE(@@FETCH_STATUS = 0)
					BEGIN
 
						IF @INQTY = 0 AND @OUTQTY = 0 
							SET @INV_REVALUE = @INV_REVALUE + @CALCPRICE
 
						IF @CALCPRICE < 0 
							SET @CALCPRICE = @CALCPRICE * (-1)
 
						IF (@INQTY > 0) 						BEGIN
					
							SET @TOT_IN = @TOT_IN + @INQTY
							SET @TOT_IN_PRICE  = @TOT_IN_PRICE + @CALCPRICE
 
						END
 
						IF (@OUTQTY > 0) --AND (@TRANSTYPE != '-2')
						BEGIN
					
							SET @TOT_OUT = @TOT_OUT + @OUTQTY
							SET @TOT_OUT_PRICE  = @TOT_OUT_PRICE + @CALCPRICE
 
						END
 
						FETCH NEXT FROM CUR_ITEM_IN INTO @INQTY, @OUTQTY, @CALCPRICE, @TRANSTYPE
					END
					CLOSE CUR_ITEM_IN
					DEALLOCATE CUR_ITEM_IN
 
					SET @INV_REV_FLAG = 0
 
					IF @OPENQTY != 0 OR @TOT_IN != 0 OR @TOT_OUT != 0 OR @F_DATE = @DATE OR @T_DATE = @DATE OR @INV_REVALUE != 0
					BEGIN
 
	
						IF @INV_REVALUE != 0 AND @TOT_IN = 0 AND @TOT_OUT = 0
							SET @INV_REV_FLAG = 1
	
 
						SET @DAYOPENINGBALANCE = @PREVDAYCLOSING
						SET @DAYOPENINGBALANCEVAL = @PREVDAYCLOSINGVAL
 
						SET @DAYCLOSINGBALANCE = @DAYOPENINGBALANCE + (@OPENQTY + @TOT_IN) - @TOT_OUT
						SET @DAYCLOSINGBALANCEVAL = @DAYOPENINGBALANCEVAL + (@OPENQTYPRICE + @TOT_IN_PRICE) - @TOT_OUT_PRICE + @INV_REVALUE
						INSERT INTO @TAB_DAY_STOCK VALUES(@WAREHOUSE,@packcartons, @ITEMGROUP, @ITEMCODE, @DATE, @DAYOPENINGBALANCE, @DAYOPENINGBALANCEVAL, (@OPENQTY + @TOT_IN), (@OPENQTYPRICE + @TOT_IN_PRICE), @TOT_OUT, @TOT_OUT_PRICE, @DAYCLOSINGBALANCE, @DAYCLOSINGBALANCEVAL, @INV_REV_FLAG,@itmprice)
 
						SET @PREVDAYCLOSING = @DAYCLOSINGBALANCE
						SET @PREVDAYCLOSINGVAL = @DAYCLOSINGBALANCEVAL	
			
						IF @T_DATE = @DATE			
							SET @TOT_BALANCE = @TOT_BALANCE + @DAYCLOSINGBALANCEVAL
	
					END
 
					SET @DATE = @DATE + 1				
				END
									
 
				FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
			END
			CLOSE CUR_ITEM
			DEALLOCATE CUR_ITEM
 
			FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
		END
		CLOSE CUR_ITEMGROUP
		DEALLOCATE CUR_ITEMGROUP
 
		FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
	END
	CLOSE CUR_WAREHOUSE
	DEALLOCATE CUR_WAREHOUSE
 
	DECLARE @WH_TS NVARCHAR(20), @ITEMGROUP_TS NVARCHAR(20),@ITEMCODE_TS NVARCHAR(20), @DATE_TS DATETIME, @OB_TS NUMERIC(16,4), @OB_VAL_TS NUMERIC(16,4), @IN_QTY_TS NUMERIC(16,4), @IN_QTY_VAL_TS NUMERIC(16,4), @OUT_QTY_TS NUMERIC(16,4), @OUT_QTY_VAL_TS NUMERIC(16,4), @CB_QTY_TS NUMERIC(16,4), @CB_QTY_VAL_TS NUMERIC(16,4), @INV_REV_FLAG_TS INT
	DECLARE @TEMP NUMERIC(16,4)
 
	DECLARE @DATE_IN DATETIME
	DECLARE @FIRST_REC INT
	SET @FIRST_REC = 0
 
	DECLARE @OB_QTY_IN NUMERIC(16,4), @OB_VAL_IN NUMERIC(16,4), @CL_QTY_IN NUMERIC(16,4), @CL_VAL_IN NUMERIC(16,4)
 
	DECLARE @TOT_IN_QTY_IN NUMERIC(16,4), @TOT_IN_VAL_IN NUMERIC(16,4), @TOT_OUT_QTY_IN NUMERIC(16,4), @TOT_OUT_VAL_IN NUMERIC(16,4), @TOT_INV_REV_FALG INT
 
	DECLARE @GTOT_IN_QTY_IN NUMERIC(16,4), @GTOT_IN_VAL_IN NUMERIC(16,4), @GTOT_OUT_QTY_IN NUMERIC(16,4), @GTOT_OUT_VAL_IN NUMERIC(16,4)
 
	DECLARE @GRPNAME NVARCHAR(100), @ITMDESC NVARCHAR(100)
 
	DECLARE @GRAND_TOT_IN NUMERIC(16,4), @GRAND_TOT_OUT NUMERIC(16,4)
 
	SET @GRAND_TOT_IN = 0
	SET @GRAND_TOT_OUT = 0
 
	DECLARE CUR_WAREHOUSE CURSOR FOR SELECT WHSCODE FROM OWHS WHERE ((WHSCODE >= @F_WAREHOUSE AND WHSCODE <= @T_WAREHOUSE) OR (@F_WAREHOUSE = '' AND @T_WAREHOUSE = '')) ORDER BY WHSCODE
	OPEN CUR_WAREHOUSE
	FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
 
		DECLARE CUR_ITEMGROUP CURSOR FOR SELECT ITMSGRPCOD FROM OITB 
WHERE ((ITMSGRPCOD = @F_ITEMGROUP AND ITMSGRPCOD <= @T_ITEMGROUP) 
OR (@F_ITEMGROUP = ''AND @T_ITEMGROUP = '')) 
ORDER BY ITMSGRPCOD
		OPEN CUR_ITEMGROUP
		FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
 
			SET @GTOT_IN_QTY_IN = 0
			SET @GTOT_IN_VAL_IN = 0
			SET @GTOT_OUT_QTY_IN = 0
			SET @GTOT_OUT_VAL_IN = 0
 
			DECLARE CUR_ITEM CURSOR FOR SELECT DISTINCT T6.ITEMCODE FROM OINM T6 WHERE ((T6.ITEMCODE IN (SELECT T0.[ItemCode] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.ITMSGRPCOD = @ITEMGROUP)) AND (T6.WAREHOUSE = @WAREHOUSE)) ORDER BY T6.ITEMCODE 
			OPEN CUR_ITEM
			FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
			WHILE(@@FETCH_STATUS = 0)
			BEGIN
 
				SET @TOT_IN_QTY_IN = 0
				SET @TOT_IN_VAL_IN = 0
				SET @TOT_OUT_QTY_IN = 0
				SET @TOT_OUT_VAL_IN = 0
 
				SET @TOT_INV_REV_FALG = 0
 
				SET @FIRST_REC = 0
		
				DECLARE CUR_DATE_IN CURSOR FOR SELECT DISTINCT DATE FROM @TAB_DAY_STOCK WHERE (WH = @WAREHOUSE) AND (ITEMGROUP = @ITEMGROUP)AND (ITEMCODE = @ITEMCODE) AND ((DATE >= @F_DATE AND DATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )
				OPEN CUR_DATE_IN
				FETCH NEXT FROM CUR_DATE_IN INTO @DATE_IN
				WHILE(@@FETCH_STATUS = 0)
				BEGIN
 
					DECLARE CUR_TRANS CURSOR FOR SELECT * FROM @TAB_DAY_STOCK WHERE ((WH = @WAREHOUSE) AND (ITEMGROUP = @ITEMGROUP) AND (DATE = @DATE_IN) AND (ITEMCODE = @ITEMCODE))
					OPEN CUR_TRANS
FETCH NEXT FROM CUR_TRANS INTO @WH_TS,@packcartons, @ITEMGROUP_TS,@ITEMCODE_TS, @DATE_TS, @OB_TS, @OB_VAL_TS, @IN_QTY_TS, @IN_QTY_VAL_TS, @OUT_QTY_TS, @OUT_QTY_VAL_TS, @CB_QTY_TS, @CB_QTY_VAL_TS, @INV_REV_FLAG_TS,@itmprice
					WHILE (@@FETCH_STATUS = 0)
					BEGIN
						SET @FIRST_REC = @FIRST_REC + 1
						IF @FIRST_REC = 1 
						BEGIN
							SET @OB_QTY_IN = @OB_TS
							SET @OB_VAL_IN = @OB_VAL_TS
						END
 
						SET @TOT_IN_QTY_IN = @TOT_IN_QTY_IN + @IN_QTY_TS
						SET @TOT_IN_VAL_IN = @TOT_IN_VAL_IN + @IN_QTY_VAL_TS
						SET @TOT_OUT_QTY_IN = @TOT_OUT_QTY_IN + @OUT_QTY_TS
						SET @TOT_OUT_VAL_IN = @TOT_OUT_VAL_IN + @OUT_QTY_VAL_TS
 
						SET @GTOT_IN_QTY_IN = @GTOT_IN_QTY_IN + @IN_QTY_TS
						SET @GTOT_IN_VAL_IN = @GTOT_IN_VAL_IN + @IN_QTY_VAL_TS
						SET @GTOT_OUT_QTY_IN = @GTOT_OUT_QTY_IN + @OUT_QTY_TS
						SET @GTOT_OUT_VAL_IN = @GTOT_OUT_VAL_IN + @OUT_QTY_VAL_TS
 
						SET @CL_QTY_IN = @CB_QTY_TS
						SET @CL_VAL_IN = @CB_QTY_VAL_TS
 
						SET @TOT_INV_REV_FALG = @TOT_INV_REV_FALG + @INV_REV_FLAG_TS
						
						FETCH NEXT FROM CUR_TRANS INTO @WH_TS,@packcartons, @ITEMGROUP_TS,@ITEMCODE_TS, @DATE_TS, @OB_TS, @OB_VAL_TS, @IN_QTY_TS, @IN_QTY_VAL_TS, @OUT_QTY_TS, @OUT_QTY_VAL_TS, @CB_QTY_TS, @CB_QTY_VAL_TS, @INV_REV_FLAG_TS,@itmprice
					END	
					CLOSE CUR_TRANS
					DEALLOCATE CUR_TRANS
 
				FETCH NEXT FROM CUR_DATE_IN INTO @DATE_IN
				END
				CLOSE CUR_DATE_IN
				DEALLOCATE CUR_DATE_IN
 
				SET @GRPNAME = (SELECT ITMSGRPNAM FROM OITB WHERE ITMSGRPCOD = @ITEMGROUP)
				SET @ITMDESC = (SELECT ITEMNAME FROM OITM WHERE ITEMCODE = @ITEMCODE)
 
declare cur_item_packunit cursor for select SALPACKUN from oitm  where itemcode = @ITEMCODE
					open cur_item_packunit
					fetch  next from cur_item_packunit into @packunit
					while(@@FETCH_STATUS = 0)
				begin
					SET @packcartons = @CL_QTY_IN/@packunit
					fetch next from cur_item_packunit into @packunit
                end
					close cur_item_packunit
					deallocate cur_item_packunit
declare cur_item_price cursor for select price from itm1  where itemcode = @ITEMCODE
					open cur_item_price
					fetch  next from cur_item_price into @itmprice
					set @itmprice=@itmprice
					close cur_item_price
					deallocate cur_item_price
		
INSERT INTO @TAB_STOCK VALUES(@WH_TS,@GRPNAME, @ITEMCODE_TS, @ITMDESC, @OB_QTY_IN, @OB_VAL_IN, @TOT_IN_QTY_IN, @TOT_IN_VAL_IN, @GTOT_IN_QTY_IN, @GTOT_IN_VAL_IN, @TOT_OUT_QTY_IN, @TOT_OUT_VAL_IN, @GTOT_OUT_QTY_IN, @GTOT_OUT_VAL_IN, @CL_QTY_IN, @CL_VAL_IN,@packcartons,@itmprice,@ITEMGROUP,@DATE)
				
 
				IF @TOT_IN_QTY_IN != 0 OR @TOT_OUT_QTY_IN != 0 OR @TOT_INV_REV_FALG > 0
				BEGIN
					SET @TOT_TRANS_BALANCE = @TOT_TRANS_BALANCE + @CL_VAL_IN
				END
 
				FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
			END
			CLOSE CUR_ITEM
			DEALLOCATE CUR_ITEM
			IF @GTOT_IN_VAL_IN != 0 OR @GTOT_OUT_VAL_IN != 0
			BEGIN
				SET @GRAND_TOT_IN = @GRAND_TOT_IN + @GTOT_IN_VAL_IN
				SET @GRAND_TOT_OUT = @GRAND_TOT_OUT + @GTOT_OUT_VAL_IN
			END
 
			FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
		END
		CLOSE CUR_ITEMGROUP
		DEALLOCATE CUR_ITEMGROUP
 
		FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
	END
	CLOSE CUR_WAREHOUSE
	DEALLOCATE CUR_WAREHOUSE
  Return
	END
 
[edit]Code block added - OriginalGriff[/edit]
Posted 5-Jan-13 2:58am
Edited 9-Jan-13 1:00am
v3
Comments
Sandeep Mewara at 5-Jan-13 13:35pm
   
How can you expect someone to suggest anything just looking at it until you share the details about the tables and number of records and how are they related?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 8,149
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100