在SQL SERVER 2008中执行函数需要1:30到2小时 [英] Function takes 1:30 mins to 2 hours to execute in SQL SERVER 2008
本文介绍了在SQL SERVER 2008中执行函数需要1:30到2小时的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
以下功能需要很长时间才能执行。任何人都可以告诉如何优化这个功能??
或者我们如何在不使用游标的情况下重新编写此函数？
我试过SQL Profiler但也没有帮助。
请帮我摆脱这个。
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]
[edit]Code block added - OriginalGriff[/edit]
推荐答案
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[^]
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[^]
这篇关于在SQL SERVER 2008中执行函数需要1:30到2小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
