SQL Server -- 提高光标速度 [英] SQL Server -- Improve Speed of Cursor

问题描述

我正在使用下面的代码来检查列 @act_value 是否高于特定行的 24.0，并使用游标循环遍历表的每一行.如果 @act_value 高于 24.0，则将完成多个操作以及更新该条目并插入新条目.也就是说，查询运行需要超过 26 分钟(从下面提供的添加之前的 0:16 分钟开始)并且可能过于占用资源.有没有更好的配置可以加速这个操作?

I am using the below code to check if column @act_value is above 24.0 for a specific row, looping through each row of the table with a cursor. If @act_value is above 24.0, then several operations will be completed as well as updating that entry and inserting a new entry. That said, the query takes over 26 min to run (up from 0:16 min prior to this addition provided below) and perhaps too resource intensive. Is there a better configuration that can speed up this operation?

DECLARE cur_hours_check CURSOR
FOR
    SELECT 
       Period, Act_Batch_Time, Kg_Per_Hour, Total_QT_Produced, 
       Total_Possible_KG,
       --The columns not affected but need for inserting the shadow day
       Division, Region, Business_Region, Plant, Work_Center, DaysInMonth, 
       Total_Possible_Hours, Demonstrated_Capacity
    FROM 
       zt_Demonstrated_Capacity_Trend

OPEN cur_hours_check

DECLARE             --Cursor Variables Pulling data
    @period                   datetime
,   @act_hours                float
,   @over_hours               float
,   @Total_QT_Produced        float
,   @KG_per_hour              float
,   @Total_Possible_KG        float
--then the extra
,   @Division                 varchar(20)
,   @Region                   varchar(20)
,   @Business_Region          varchar(20)
,   @Plant                    varchar(40)
,   @Work_Center              varchar(40)
,   @DaysInMonth              float
,   @Total_Possible_Hours     float
,   @Demonstrated_Capacity    float

DECLARE             --Cursor Shadow variables:
    @shadow_period                 datetime 
,   @shadow_act_hours              float
,   @shadow_Total_QT_Produced      float
,   @shadow_Total_Possible_KG      float
,   @shadow_Percent_of_Total_Hours float

DECLARE             --Cursor adjusted first day variables
    @adj_act_hours                 float
,   @adj_Total_QT_Produced         float
,   @adj_Total_Possible_KG         float
,   @adj_Percent_of_Total_Hours    float

FETCH FROM cur_hours_check
    INTO @period, @act_hours, @KG_per_hour, @Total_QT_Produced, @Total_Possible_KG
   ,@Division, @Region, @Business_Region, @Plant, @Work_Center, @DaysInMonth, @Total_Possible_Hours, @Demonstrated_Capacity

WHILE @@FETCH_STATUS = 0
BEGIN

    IF @act_hours > 24.0
    BEGIN
    -- Setting the shadow period to carry over values relative to 24 hour time window
        SET @shadow_period = dateadd(dd, datediff(dd, 0, @period) + 1, 0) 
        SET @shadow_act_hours = @act_hours - 24.0
        SET @shadow_Total_QT_Produced = (@Total_QT_Produced - (@KG_per_hour * @shadow_act_hours))
        SET @shadow_Percent_of_Total_Hours = (@shadow_act_hours/24.0)*100

    -- Adjusting first period for 24 hour window of operation
        SET @adj_act_hours = 24.0
        SET @adj_Total_QT_Produced =  @Total_QT_Produced - (@KG_per_hour * 24.0)
        SET @adj_Total_Possible_KG = @Total_Possible_KG *(1-( @shadow_act_hours/@act_hours ))
        SET @adj_Percent_of_Total_Hours = 100

    -- now update that record, as well as insert the shadow day
        UPDATE zt_Demonstrated_Capacity_Trend
        SET Period = @period
        , Act_Batch_Time = 24.0
        , Total_QT_Produced = @adj_Total_QT_Produced
        , Total_Possible_KG = @adj_Total_Possible_KG
        , Kg_Per_Hour = @KG_per_hour
        , Percent_of_Total_Hours = @adj_Percent_of_Total_Hours
        , Division = @Division
        , Region = @Region
        , Business_Region = @Business_Region
        , Plant = @Plant
        , Work_Center = @Work_Center
        , DaysInMonth = @DaysInMonth
        , Total_Possible_Hours = @Total_Possible_Hours
        , Demonstrated_Capacity = @Demonstrated_Capacity
        WHERE CURRENT OF cur_hours_check 

    --Inserting the shadow record
        INSERT INTO [dbo].[zt_Demonstrated_Capacity_Trend](
        [Period]
        ,[Division]
        ,[Region]
        ,[Business_Region]
        ,[Plant]
        ,[Work_Center]
        ,[Kg_Per_Hour]
        ,[Total_QT_Produced]
        ,[Act_Batch_Time]
        ,[DaysInMonth]
        ,[Total_Possible_Hours]
        ,[Demonstrated_Capacity]
        ,[Total_Possible_KG]
        ,[Percent_of_Total_Hours])
        VALUES
        (@shadow_period
        ,@Division
        ,@Region
        ,@Business_Region
        ,@Plant
        ,@Work_Center
        ,@Kg_Per_Hour
        ,@shadow_Total_QT_Produced
        ,@shadow_act_hours
        ,@DaysInMonth
        ,@Total_Possible_Hours
        ,@Demonstrated_Capacity
        ,@adj_Total_Possible_KG
        ,@shadow_Percent_of_Total_Hours)
    END
    FETCH NEXT FROM cur_hours_check
    INTO @period, @act_hours, @KG_per_hour, @Total_QT_Produced, @Total_Possible_KG
   ,@Division, @Region, @Business_Region, @Plant, @Work_Center, @DaysInMonth, @Total_Possible_Hours, @Demonstrated_Capacity

END;

CLOSE       cur_hours_check
DEALLOCATE  cur_hours_check

推荐答案

像预期的那样使用 TSQL 进行基于集合的操作，我已经对您使用的变量进行了修改，并在 SQLFiddle 完成将超过 24 小时的单个记录拆分为多个 24 小时记录和剩余部分.这是工作:

Using TSQL for set-based operations like it was intended, I've taken the modifications to the variables you've used and set up a sample at SQLFiddle that accomplishes splitting a single record of greater than 24 hours into many 24 hour ones with the remainder. Here's the work:

BEGIN TRAN
INSERT INTO zt_Demonstrated_Capacity_Trend (
        -- The Columns we care about modifying
        period, Act_Batch_Time, Total_QT_Produced, KG_per_hour, Total_Possible_KG, Percent_of_Total_Hours
        -- The rest that you aren't touching in your procedure
        , Division, Region, Business_Region, Plant, Work_Center, DaysInMonth, Total_Possible_Hours, Demonstrated_Capacity, over_hours
    )
SELECT
    -- Add a day equal to the rows we generate
      DATEADD(dd,ShadowDays, Period) AS Shadow_Period
    -- Hours we generate
    , ShadowHours AS Shadow_Hours
    -- Our Hours times the rate is total produced
    , Kg_Per_Hour * ShadowHours AS Shadow_Total_QT_Produced
    -- Unmodified Kg_Per_Hour
    , Kg_Per_Hour
    -- Unmodified Kg_Per_Hour times 24 is total in a day possible.
    , Kg_Per_Hour * 24.0 as Total_Possible_KG
    -- Our hours divided by hours in a day
    , ShadowHours / 24.0 * 100 AS Shadow_Percent_of_Total_Hours
    -- the rest
    , Division, Region, Business_Region, Plant, Work_Center, DaysInMonth, Total_Possible_Hours, Demonstrated_Capacity, over_hours
FROM zt_Demonstrated_Capacity_Trend T
    CROSS APPLY (
        SELECT
            ROWID-1 AS ShadowDays
            , CASE WHEN CAST(T.Act_Batch_Time AS NUMERIC(38,15)) - ROWID*24 > 0 THEN 24 ELSE CAST(T.Act_Batch_Time AS NUMERIC(38,15)) % 24.0 END AS ShadowHours
        FROM (
            SELECT ROW_NUMBER()OVER(ORDER BY S.NAME) AS ROWID
            FROM master..spt_values S
            ) X
        WHERE T.Act_Batch_Time - ((ROWID-1)*24) > 0
    ) Z
WHERE T.Act_Batch_Time > 24

DELETE FROM zt_Demonstrated_Capacity_Trend WHERE Act_Batch_Time> 24
COMMIT TRAN

使用交叉应用，并获得可以分割给定值的人工行，然后我使用小时和天数以各种方式放入选择语句中以获得预期的效果.您希望将其隔离在单个事务中以防止在插入和删除语句之间进行新插入.这将比带有谓词的游标表现得更好.

Using a cross apply, and getting artificial rows where we can split a given value, I then use the Hours and days to put into the select statement in various ways to get your intended effect. You want this isolated in a single transaction to prevent new inserts between the insert and delete statement. This will perform way better than your cursor with predicate.

这篇关于SQL Server -- 提高光标速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！