如何在sql中使用游标减少存储过程的执行时间 [英] How to reduce the execution time for stored procedure using cursors in sql
本文介绍了如何在sql中使用游标减少存储过程的执行时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
您好b $ b
我已经编写了一个存储过程，用于在sql中使用游标查找fifo成本。每次我传递一个产品名称作为参数我有超过1000个产品，所以它逐个检查。这就是为什么它需要更多的时间。
我的SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo]。[SP_GETFIFOCOST]（ @ pid nvarchar （ 250 ）） as
开始
声明 @ qty decimal （ 18 ， 2 ）， @ cost decimal （ 18 ， 2 ）， @ line int
声明 @ qty1 decimal （ 18 ， 2 ）， @ cost1 decimal （ 18 ， 2 ）， @ line1 < span class =code-keyword> int
声明 @ fqty < span class =code-keyword> decimal （ 18 ， 2 ）
set @ fqty = 0
声明 MyCur 光标 for 选择 QTY_IN，UNIT_COST，LINE 来自 STOCKDETAILS where STOCK_JR in （' IN'，' GR'，' TR'，' SA' ，' OS'，' SR'，' AS' ，' OG'，' PO'）和 PRODUCT_NO = @ pid 订单 按 STOCK_DATE
打开 MyCur
声明 MyCur1 光标 选择 QTY_OUT，UNIT_COST，LINE 来自 STOCKDETAILS 其中 STOCK_JR （' OU'，' IS' ，' SJ'，' TR'，' SA'，' CS'，' AS'，' PR'，' DO'）和 PRODUCT_NO = @ pid 订单 按 STOCK_DATE
打开 MyCur1
如果 @ fqty = 0
开始
获取下一步从 MyCur1 进入 @ qty1 ， @ cost1 ， @ line1
获取下一步从 MyCur 进入 @ qty ， @ cost ， @ l ine
结束
@@ Fetch_Status = 0
开始
如果 @ qty> @ qty1
begin
set @ qty = @ qty- @ qty1
更新 STOCKDETAILS 设置 FIFO_COST = @ cost 其中 LINE = @ line1
< span class =code-keyword> set @ fqty = @ fqty + @ qty
if @ fqty> 0
开始
获取下一步来自 MyCur1 < span class =code-keyword> into @ qty1 ， @ cost1 ， @ line1
set @ fqty = @ qty- @ qty1
update STOCKDETAILS set FIFO_COST = @ cost 其中 LINE = @ line1
end
end
如果 @ qty< @ qty1
开始
set @ fqty = @ qty- @ qty1
update STOCKDETAILS set FIFO_COST = @ cost 其中 LINE = @ line1
if @ fqty< 0
开始
获取下一步从 MyCur 进入 @ qty ， @cost ， @ line
获取下一步从 MyCur1 进入 @ qty1 ， @ cost1 ， @ line1
set @ qty1 = @ fqty- @ qty1
set @ fqty = @ qty + @ qty1
如果 @ fqty> 0
开始
set @ qty = @ qty + @ qty1
end
更新 STOCKDETAILS 设置 FIFO_COST = @ cost 其中 LINE = @ line1
< span class =code-keyword> end
end
if @ fqty = 0
开始
更新 STOCKDETAILS 设置 FIFO_COST = @ cost 其中 LINE = @ line1
获取下一步从 MyCur1 进入 @ qty1 ， @ cost1 ， @ line1
获取下一步从 MyCur 进入 @ qty ， @ cost ， @ line
结束
结束
关闭 MyCur1
关闭 MyCur
取消分配 MyCur1
取消分配 MyCur
结束
我已经按了一个按钮。如果你点击那个按钮，它将更新所有产品的fifo成本。
我在按钮上写了这样的代码
尝试
{
str = 按PRODUCT_NO从PRODUCT订单中选择不同的PRODUCT_NO;
cn.Open（）;
cmd.CommandText = str;
cmd.Connection = cn;
dr = cmd.ExecuteReader（）;
尝试
{
而（dr.Read（））
{
使用（DbCommand cmdb = cn.CreateCommand（））
{
尝试
{
cmdb.Connection = cn;
cmdb.CommandText = SP_GETFIFOCOST;
cmdb.CommandType = CommandType.StoredProcedure;
var parameter = cmdb.CreateParameter（）;
parameter.ParameterName = @ pid;
parameter.Value = dr.GetValue（ 0 ）。ToString（）;
cmdb.Parameters.Add（parameter）;
cmdb.ExecuteNonQuery（）;
}
catch （例外情况）{}
}
/ / progressBar1.Value ++;
}
dr.Close（）;
cn.Close（）;
}
catch （Ex Ex）
{
dr.Close（）;
cn.Close（）;
}
请问你解决这个问题
解决方案
最大的性能问题是使用游标。您应该修改过程，以便使用单个语句更新表而不循环遍历游标结果。
我不确定逻辑，因为基于代码有点不清楚条件应该是什么，但总的来说，想法应该是你运行一个更新语句，它使用过程参数的值，而不是光标。
附加：
从我收集的信息中，程序应该根据用作FIFO的股票收入返回产品的成本。
这可能很遥远但是使用示例数据会在最后添加#stockout行的情况下返回正确的结果
CREATE TABLE #StockIn（
OrderNo int ，
数量 int ，
成本 int
）;
- 添加库存插页
INSERT INTO #StockIn VALUES （ 1 ， 10 ， 11 ）;
INSERT INTO #StockIn VALUES （ 2 ， 10 ， 12 ） ;
INSERT INTO #StockIn VALUES （ 3 ， 10 ， 13 ） ;
CREATE TABLE #StockOut（
OrderNo int ，
数量 int ，
成本 int
）;
- 添加股票用途
INSERT INTO #StockOut VALUES （ 1 ， 1 ， 5 ）;
INSERT INTO #StockOut VALUES （ 2 ， 2 ， 5 ） ;
INSERT INTO #StockOut VALUES （ 3 ， 12 ， 5 ） ;
INSERT INTO #StockOut VALUES （ 4 ， 3 ， 5 ） ;
- 根据添加和使用的金额选择库存商品的当前价格
SELECT TOP 1 *
FROM #StockIn si
WHERE （ COALESCE （（ SELECT SUM（si2.Quantity）
FROM #StockIn si2
WHERE si2.OrderNo< si.OrderNo）， 0 ）
+ si.Quantity）> =（ SELECT SUM（so.Quantity）
FROM #StockOut so）
ORDER BY OrderNo;
Hi
I have written one stored procedure for finding fifo cost using cursors in sql. Every time i pass one product name as a parameter i have more than 1000 products so it checks one by one.That's why it takes more time.
My SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) as
Begin
Declare @qty decimal(18,2),@cost decimal(18,2),@line int
Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
Declare @fqty decimal(18,2)
set @fqty=0
Declare MyCur Cursor for select QTY_IN,UNIT_COST,LINE from STOCKDETAILS where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') and PRODUCT_NO=@pid order by STOCK_DATE
Open MyCur
Declare MyCur1 Cursor for select QTY_OUT,UNIT_COST,LINE from STOCKDETAILS where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') and PRODUCT_NO=@pid order by STOCK_DATE
Open MyCur1
if @fqty=0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
Fetch Next From MyCur into @qty,@cost,@line
End
While @@Fetch_Status=0
Begin
if @qty>@qty1
begin
set @qty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
set @fqty=@fqty+@qty
if @fqty>0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
set @fqty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
end
end
if @qty<@qty1
begin
set @fqty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
if @fqty<0
begin
Fetch Next From MyCur into @qty,@cost,@line
Fetch Next From MyCur1 into @qty1,@cost1,@line1
set @qty1=@fqty-@qty1
set @fqty=@qty+@qty1
if @fqty>0
begin
set @qty=@qty+@qty1
end
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
end
end
if @fqty=0
begin
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
Fetch Next From MyCur1 into @qty1,@cost1,@line1
Fetch Next From MyCur into @qty,@cost,@line
End
End
Close MyCur1
Close MyCur
Deallocate MyCur1
Deallocate MyCur
End
I have taken one button. If you click that button it will update fifo cost for all product.
I have written code like this in button
try
{
str = "select distinct PRODUCT_NO from PRODUCT order by PRODUCT_NO";
cn.Open();
cmd.CommandText = str;
cmd.Connection = cn;
dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
using (DbCommand cmdb = cn.CreateCommand())
{
try
{
cmdb.Connection = cn;
cmdb.CommandText = "SP_GETFIFOCOST";
cmdb.CommandType = CommandType.StoredProcedure;
var parameter = cmdb.CreateParameter();
parameter.ParameterName = "@pid";
parameter.Value = dr.GetValue(0).ToString();
cmdb.Parameters.Add(parameter);
cmdb.ExecuteNonQuery();
}
catch (Exception Ex) { }
}
//progressBar1.Value++;
}
dr.Close();
cn.Close();
}
catch (Exception Ex)
{
dr.Close();
cn.Close();
}
Would u pls solve this issue
解决方案
The biggest performance problem is the usage of cursors. You should modify the procedure so that you update the tables with a single statement without looping through the cursor results.
I'm not sure about the logic since based on the code it's somewhat unclear what the conditions should be but in overall the idea should be that you run an update statement which uses the values from the parameters of the procedure, not from the cursor.
ADDITION:
From what I've gathered the procedure should return the cost for a product based on stock incomes which are used as FIFO.
This could be far off but using the example data would the query in the end return correct results in case different #stockout rows are added
CREATE TABLE #StockIn ( OrderNo int, Quantity int, Cost int ); -- Add stock inserts INSERT INTO #StockIn VALUES (1, 10, 11); INSERT INTO #StockIn VALUES (2, 10, 12); INSERT INTO #StockIn VALUES (3, 10, 13); CREATE TABLE #StockOut ( OrderNo int, Quantity int, Cost int ); -- Add stock usages INSERT INTO #StockOut VALUES (1, 1, 5); INSERT INTO #StockOut VALUES (2, 2, 5); INSERT INTO #StockOut VALUES (3, 12, 5); INSERT INTO #StockOut VALUES (4, 3, 5); -- select the current price for an item in stock based on the amount added and used SELECT TOP 1 * FROM #StockIn si WHERE (COALESCE((SELECT SUM(si2.Quantity) FROM #StockIn si2 WHERE si2.OrderNo < si.OrderNo), 0) + si.Quantity) >= (SELECT SUM(so.Quantity) FROM #StockOut so) ORDER BY OrderNo;
这篇关于如何在sql中使用游标减少存储过程的执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
