Function takes 1:30 mins to 2 hours to execute in SQL SERVER 2008

Question

0.00/5 (No votes)

See more:

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.

SQL

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 1:58am

Sanjay Tiwari1

Updated 9-Jan-13 0:00am

v3

Add a Solution

Comments

Sandeep Mewara 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mehdi Gholam · Answer 1 · 2013-01-05T07:10:00

Solution 2

Run the code in SQLProfiler and have it create indexes for you.
http://msdn.microsoft.com/en-us/library/ms181091.aspx[^]

http://www.datasprings.com/resources/articles-information/sql-server-2008-profiler-and-tuning-wizard[^]

Posted 5-Jan-13 7:10am

Mehdi Gholam