Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Red face Unanswered: Optimising a unstructured SQL function

    Hi,

    I have an unstructured SQL Function which takes around 2 hours to return a table with just nine hundred rows. I have deleted some text from code because it was more than the limit of this website.

    How to structure or optimize the below function to imporve its performance. Plese help me to get out of this:

    Code:
    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
    	
    	--Declaration Part
    	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 --NVARCHAR(10)
    
    	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
    
    	--Warehouse Cursor
    	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
    
    		--Item Group Cursor
    		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
    
    			--Item Cursor
    			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 -- AND ((T6.DOCDATE >= @F_DATE AND T6.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )) 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
    
    				--DECLARE CUR_DATE CURSOR FOR SELECT DISTINCT T0.DOCDATE FROM OINM T0 ORDER BY T0.DOCDATE --WHERE  ((T0.DOCDATE >= @F_DATE AND T0.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') ) ORDER BY T0.DOCDATE
    				--OPEN CUR_DATE
    				--FETCH NEXT FROM CUR_DATE INTO @DATE
    				--WHILE (@@FETCH_STATUS = 0)
    				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
    
    --modified for excluding the transtype = 67   starts on 11-10-2011
    
    					
    						IF (@OUTQTY > 0) --AND (@TRANSTYPE != '-2')
    						BEGIN
    					
    							SET @TOT_OUT = @TOT_OUT + @OUTQTY
    							SET @TOT_OUT_PRICE  = @TOT_OUT_PRICE + @CALCPRICE
    
    						END
    
    --						IF (@INQTY > 0) AND (@TRANSTYPE = '-2')
    --						BEGIN
    --							
    --							SET @OPENQTY = @OPENQTY + @INQTY
    --							SET @OPENQTYPRICE = @OPENQTYPRICE + @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
    						--IF @OPENQTY != 0 OR @TOT_IN != 0 OR @TOT_OUT != 0
    						--IF @DAYOPENINGBALANCE != 0 OR @DAYCLOSINGBALANCE != 0
    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
    
    					--FETCH NEXT FROM CUR_DATE INTO @DATE
    					SET @DATE = @DATE + 1				
    				END
    				--CLOSE CUR_DATE
    				--DEALLOCATE CUR_DATE
    --SALE PACKUNIT
    					
    
    				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 @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
    
    						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)
    
    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
    				--INSERT INTO @TAB_STOCK VALUES('', '', '', 'Sub Total(' + @GRPNAME + ')', NULL, NULL, NULL, @GTOT_IN_VAL_IN, NULL, NULL, NULL, @GTOT_OUT_VAL_IN, NULL, NULL, NULL, NULL)
    				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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you posted isn't syntactically correct. Please post:
    1. All of the source that you want us to help you improve the performance
    2. All of the table definitions (DECLARE TABLE statements) used by the code from item 1.
    3. At least a few rows of data (enough to produce three or more rows of output) for the code in item 1
    4. The output you expect based on the data from step 3.

    That's pretty much "bare minimum" for what I need in order to help you.

    If there is too much information for one post, then you'll probably need to use multiple posts or upload the information as files.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Now all we can do it guess at everything. Think about it; do you program without DDL?

    We hate function, triggers and cursors. SQL is a declarative language, mimic monolithic 1960's COBOL. We hate local variable for them same reasons; read “An Introduction to Functional Programming” by Greg Michaelson for the details of why. We also hate BIT flags; that was assembly language and has no place in RDBMS; SQL is a predicate language in which we discover the state of being in the schema with search conditions.

    We never prefix a function with a FORTRAN II style “func_”, “udf_”, “fn_” OR other violations of ISO-11179. The 3 to 4 times in our entire career where we might write a function, we never write a table function. This is how you COBOL programmers fake a scratch tape file like you had in 1960.

    SQL Programmers use <> and not != (that was C), DECIMAL(16,4) and not NUMERIC (more precision is allowed), we do not use WHILE loops, the variable names have no consistency (item is also itm, etc). You COBOL programmers write this:


    IF @in_qty_tot <> 0
    OR @out_qty_tot <> 0
    OR @inv_rev_flag <> 0
    BEGIN
    SET @trans_balance_tot = @trans_balance_tot + @in_cl_val;
    END;

    (I changed the data element names to ISO-11179 rules) But an SQL programmer would have written:

    SET @trans_balance_tot
    = @trans_balance_tot +
    CASE WHEN @in_qty_tot <> 0.00
    OR @out_qty_tot <> 0.00
    OR @inv_rev_flag <> 0
    THEN @in_cl_val ELSE 0.00 END;

    The CASE expression is a start. The real goal would be to get rid of the assembly language flag and all the other local variables.

    In this one module you have NINE cursor allocations. My rule of thumb is that you should never write more than 4 OR 5 of them in your entire career.

    In short, this code would be a really bad example in one of my books. I played with it for about an hour, but cannot help you. This needs to be replaced starting from the original specs. Based on my experience, I will estimate that your code is 3 or more orders of magnitude than a real SQL statement. My guess is that this could be a single MERGE statement

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •