更新后更新触发器的性能问题

Question

我在MS-SQL Server 2014数据库中的一个数据库触发器中有一个小的性能问题。

CREATE TRIGGER [dbo].[TRG_T_TPM_Vehicle_Update] ON [dbo].[T_TPM_Vehicle]
   AFTER UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE T_TPM_Vehicle SET LastUpdated = getdate() 
       WHERE Vehicle_Number IN (SELECT Vehicle_Number FROM inserted)
UPDATE T_TPM_Vehicle SET [DisturbedSince] = getdate() 
       WHERE Vehicle_Number IN (SELECT Vehicle_Number FROM inserted WHERE inserted.Emergency_Stop = 1)
         AND Vehicle_Number IN (SELECT Vehicle_Number FROM deleted WHERE deleted.Emergency_Stop = 0)
INSERT INTO T_TPM_Vehicle_HistoricalData
       ([Vehicle_Ref]
       ,[Vehicle_Number]
       ,[Vehicle_Type]
       ,[Pos_X]
       ,[Pos_Y]
       ,[Alpha]
       ,[LastAutoPos_X]
       ,[LastAutoPos_Y]
       ,[LastAutoAlpha]
       ,[Automatic]
       ,[Manual]
       ,[Blocked]
       ,[Loaded]
       ,[Stoped]
       ,[Emergency_Stop]
       ,[User_Required]
       ,[BatteryAlmostEmpty]
       ,[BatteryEmpty]
       ,[BatteryLevel]
       ,[ChargingRelaisEnable]
       ,[NavOK]
       ,[PowerOn]
       ,[Available]
       ,[OperatingMinutes]
       ,[UpdateOperatingMinutes]
       ,[DataChangedByVIS]
       ,[Blockingsreleased]
       ,[Cancelled]
       ,[ProductID]
       ,[HUIdent1]
       ,[HUIdent2]
       ,[HUType]
       ,[DisturbedSince])
 SELECT inserted.[Vehicle_Ref]
  ,inserted.[Vehicle_Number]
  ,inserted.[Vehicle_Type]
  ,inserted.[Pos_X]
  ,inserted.[Pos_Y]
  ,inserted.[Alpha]
  ,inserted.[LastAutoPos_X]
  ,inserted.[LastAutoPos_Y]
  ,inserted.[LastAutoAlpha]
  ,inserted.[Automatic]
  ,inserted.[Manual]
  ,inserted.[Blocked]
  ,inserted.[Loaded]
  ,inserted.[Stoped]
  ,inserted.[Emergency_Stop]
  ,inserted.[User_Required]
  ,inserted.[BatteryAlmostEmpty]
  ,inserted.[BatteryEmpty]
  ,inserted.[BatteryLevel]
  ,inserted.[ChargingRelaisEnable]
  ,inserted.[NavOK]
  ,inserted.[PowerOn]
  ,inserted.[Available]
  ,inserted.[OperatingMinutes]
  ,inserted.[UpdateOperatingMinutes]
  ,inserted.[DataChangedByVIS]
  ,inserted.[Blockingsreleased]
  ,inserted.[Cancelled]
  ,inserted.[ProductID]
  ,inserted.[HUIdent1]
  ,inserted.[HUIdent2]
  ,inserted.[HUType]
  ,inserted.[DisturbedSince] 
FROM inserted
END

它基本上要做的是为插入的所有行中的所有行设置所有行的最后一个列，并为插入行的一个子集设置了列。

最后，插入的行被复制到历史表中。（任何行上的每个更改都必须保存两天）。较旧的数据被维护作业删除。

由于每秒更新了〜300行（可以将行更新在一起），因此我们创建了大量的数据和递归更新。

我现在找到了似乎可以解决由我的触发器引起的递归更新问题的更新触发器，但是我必须在触发器中使用更新语句处理插入的表的每一行。

我不确定这是否真的更快。你们中有人有建议吗？

我真正需要的是在将数据行发送到表之前调整/扩展数据行。有什么方法吗？

例如：类似：

CREATE TRIGGER ... INSTEAD OF UPDATE
AS
BEGIN
    UPDATE inserted SET LastUpdated = getdate()
    UPDATE inserted SET DisturbedSince 
     WHERE Vehicle_Number IN (SELECT Vehicle_Number FROM inserted WHERE inserted.Emergency_Stop = 1)
       AND Vehicle_Number IN (SELECT Vehicle_Number FROM deleted WHERE deleted.Emergency_Stop = 0)
    "SAVE INSERTED"
END

和一个将更改数据存储到历史记录表的后更新触发器。

谢谢您的任何建议。

托马斯

Answer 1

您可以认为使用INSTEAD OF触发器是正确的方法而不是AFTER触发器，而当您想在同一表中更改数据时。<<<<<<<</p>

这将是：

CREATE TRIGGER [dbo].[TRG_T_TPM_Vehicle_Update] ON [dbo].[T_TPM_Vehicle]
   INSTEAD OF UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE tgt
SET
   Vehicle_Ref = i.Vehicle_Ref,
   Vehicle_Type = i.Vehicle_Type,
   ...
   LastUpdated = getdate(),
   DisturbedSince = CASE WHEN i.Emergency_Stop=1 and d.Emergency_Stop=0
                    THEN getdate() ELSE d.DisturbedSince END
OUTPUT
   inserted.[Vehicle_Ref]
  ,inserted.[Vehicle_Number]
  ,inserted.[Vehicle_Type]
  ...
  ,inserted.[HUIdent2]
  ,inserted.[HUType]
  ,inserted.[DisturbedSince] 
INTO T_TPM_Vehicle_HistoricalData
       ([Vehicle_Ref]
       ,[Vehicle_Number]
       ,[Vehicle_Type]
       ...
       ,[HUIdent2]
       ,[HUType]
       ,[DisturbedSince])
FROM
   T_TPM_Vehcile tgt
      inner join
   inserted i
      on
         tgt.Vehicle_Number = i.Vehicle_Number
      inner join
   deleted d
      on
         tgt.Vehicle_Number = d.Vehicle_Number

在

您还会注意到，这有点令人困惑，因为这里有两个inserted s -inserted作为触发器的一部分（将其作为 i的 CC_7换成 CC_7，以避开某些混乱）和 inserted作为OUTPUT条款的一部分。