代码之家 › 专栏 › 技术社区 › curious slab

如何使T-SQL游标更快?

cursor sql-server sql

curious slab · 技术社区 · 17 年前

嘿,我在SQLServer2000下的存储过程中有一个游标(现在不可能更新),它更新了表的所有内容,但通常需要几分钟才能完成。我需要加快速度。下面是按任意产品id筛选的示例表; Example table http://img231.imageshack.us/img231/9464/75187992.jpg 而GDEPO:入口仓库,CDEPO:出口仓库,Adet:数量,E_CIKAN使用的数量。

1:20机组进入01车辆段, 3:5单位假期01(第一次记录的E_CIKAN现在将为15) 4:10多个机组进入01号车辆段。 5:3单元从第一个记录中留下01。请注意,第一条记录的E_CIKAN设置为18。

下面是翻译成英语的存储过程;

CREATE PROC [dbo].[UpdateProductDetails]
as
UPDATE PRODUCTDETAILS SET E_CIKAN=0;
DECLARE @ID int
DECLARE @SK varchar(50),@DP varchar(50)  --SK = STOKKODU = PRODUCTID, DP = DEPOT
DECLARE @DEMAND float     --Demand=Quantity, We'll decrease it record by record
DECLARE @SUBID int
DECLARE @SUBQTY float,@SUBCK float,@REMAINS float
DECLARE SH CURSOR FAST_FORWARD FOR
SELECT [ID],PRODUCTID,QTY,EXITDEPOT FROM PRODUCTDETAILS  WHERE (EXITDEPOT IS NOT NULL) ORDER BY [DATE] ASC
OPEN SH
FETCH NEXT FROM SH INTO @ID, @SK,@DEMAND,@DP

WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE SA CURSOR FAST_FORWARD FOR
   SELECT [ID],QTY,E_CIKAN FROM PRODUCTDETAILS  WHERE (QTY>E_CIKAN) AND (PRODUCTID=@SK) AND (ENTRYDEPOT=@DP) ORDER BY [DATE] ASC
   OPEN SA
   FETCH NEXT FROM SA INTO @SUBID, @SUBQTY,@SUBCK
   WHILE (@@FETCH_STATUS = 0) AND (@DEMAND>0)
   BEGIN
      SET @REMAINS=@SUBQTY-@SUBCK
      IF @DEMAND>@REMAINS  --current record isnt sufficient, use it and move on
      BEGIN
         UPDATE PRODUCTDETAILS SET E_CIKAN=QTY WHERE ID=@SUBID;
         SET @DEMAND=@DEMAND-@REMAINS
      END
      ELSE
      BEGIN
         UPDATE PRODUCTDETAILS SET E_CIKAN=E_CIKAN+@DEMAND WHERE ID=@SUBID;
         SET @DEMAND=0
      END
      FETCH NEXT FROM SA INTO @SUBID, @SUBAD,@SUBCK
   END
   CLOSE SA
   DEALLOCATE SA
   FETCH NEXT FROM SH INTO @ID, @SK,@DEMAND,@DP
END
CLOSE SH
DEALLOCATE SH

6 回复 | 直到 17 年前

codeulike 17 年前

根据我对这个问题的另一个回答中的对话,我想我已经找到了一种加快你日常生活的方法。

您有两个嵌套游标:

内部光标循环穿过已指定entrydepot的产品/仓库的行。它为每一个添加到E_CIKAN中,直到分配完所有产品。

所以

你的外环只需要得到 全部的 每个产品/仓库组合的发货数量。因此,您可以将外部光标定义更改为:

DECLARE SH CURSOR FAST_FORWARD FOR
    SELECT PRODUCTID,EXITDEPOT, Sum(Qty) as TOTALQTY
    FROM PRODUCTDETAILS  
    WHERE (EXITDEPOT IS NOT NULL) 
    GROUP BY PRODUCTID, EXITDEPOT
OPEN SH
FETCH NEXT FROM SH INTO @SK,@DP,@DEMAND

(然后还将代码末尾的SH中匹配的FETCH更改为match,显然)

这意味着您的外部游标将有更少的行循环通过,而您的内部游标将有大致相同数量的行循环通过。

所以这应该更快。

Justin Niessner 17 年前

您有两种选择,这取决于您真正想要完成的任务的复杂性:

将光标替换为表变量(带标识列)、计数器和while循环的组合。然后可以循环遍历表变量的每一行。性能比光标更好…即使它看起来不像光标。

Slim 17 年前

移除光标并执行批更新。我还没有找到一个不能批量完成的更新。

KM. 17 年前

移除游标并重写,作为加入游标查询的更新,如果需要,可以将IFs作为案例。我今天太忙了,没时间为你写更新。。。

Garrett 17 年前

首先,如果必须使用游标,并且要更新内容,则使用FORUPDATE子句声明游标。(请参见下面的示例。请注意,该示例完全不是基于您的代码。)

话虽如此,使用游标以外的东西有很多种方法,通常利用临时表。我会调查这条路线,而不是用光标。

DECLARE LoopingCursor CURSOR LOCAL DYNAMIC
FOR
    select sortorder from customfielddefinition
    where context=@targetContext
FOR UPDATE OF sortorder

codeulike 17 年前

我可以看出您试图解决的问题相当复杂:

那一排
当有两个单独的行进行进货(指定GDEPO)时,当一行的E_CIKAN达到最大值(ADET)时,有时会出现溢出,然后您希望将剩余部分添加到下一行。

这是一个相当棘手的计算,因为您必须比较不同的行,然后返回并更改一行或两行中的值,以跟踪每个股票交易。

那里也许

例如,与记录股票交易的同一个表中的股票跟踪不同,您是否可以有一个单独的表,其中包含“Product_id,Depo_id,amount”列,用于一次跟踪每个仓库中每个产品的总金额?

这样的数据库设计更改可能会使事情变得更简单。

或而不是用E_CIKAN来跟踪 ,使用它来跟踪 还有什么 . 并在每行中保留一个E_CIKAN值。所以,无论何时库存进出仓库,都要重新计算E_CIKAN 那时并将其存储在该事务行中(而不是尝试返回到原始的“库存”行并在那里进行更新)。然后要找出当前的库存,您只需查看该产品/仓库的最新交易即可。

总之,我要说的是,您的计算速度慢且繁琐,因为您以一种奇怪的方式存储数据。从长远来看,更改数据库设计以简化编程可能是值得的。