我正在使用以下代码检查@act_value特定行的列是否高于24.0，并使用游标遍历表的每一行。如果@act_value大于24.0，则将完成一些操作以及更新该条目并插入新条目。就是说，查询要花26分钟才能运行（从下面提供此添加之前的0:16分钟开始），并且可能占用大量资源。是否有更好的配置可以加快此操作？
@act_value
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上设置了一个示例，该示例通过使用余。这是工作：
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
使用交叉应用，并获得可在其中拆分给定值的人为的行，然后使用小时和天数以各种方式将其放入select语句中，以达到预期的效果。您希望将其隔离在单个事务中，以防止在insert和delete语句之间进行新的插入。这将比带有谓词的光标执行得更好。
