sql-server - INSTEAD OF DELETE 触发器超时

Question

我在 SQL Server 2008 R2 下遇到以下超时问题，而不是删除触发器。它似乎仅在许多(> 6 左右)CustomFieldOption 时发生s 在同一个事务中被删除。

CREATE TRIGGER dbo.[DeleteCustomFieldOptionInsteadOfTrigger] ON dbo.[CustomFieldOption] INSTEAD OF DELETE
AS

BEGIN

    SET NOCOUNT ON;

    UPDATE ucf  SET ucf.PendingCustomFieldOptionIdValue = NULL, ucf.PendingProposedStateId = NULL
                FROM UserCustomField ucf
    INNER JOIN deleted d ON d.CustomFieldOptionId = ucf.PendingCustomFieldOptionIdValue
    WHERE ucf.CurrentCustomFieldOptionIdValue IS NOT NULL;

    SET NOCOUNT ON;

    DELETE ucf FROM UserCustomField ucf
                INNER JOIN deleted d ON d.CustomFieldOptionId = ucf.PendingCustomFieldOptionIdValue
    WHERE ucf.CurrentCustomFieldOptionIdValue IS NULL;

    SET NOCOUNT ON;

    DELETE ucf FROM UserCustomField ucf
                INNER JOIN deleted d ON d.CustomFieldOptionId = ucf.CurrentCustomFieldOptionIdValue;

    SET NOCOUNT ON;


    DELETE cfo FROM CustomFieldOption cfo
    INNER JOIN deleted d ON d.CustomFieldOptionId = cfo.CustomFieldOptionId;

    SET NOCOUNT OFF;

END

我意识到可能需要进一步的上下文(我会根据需要更新问题)-我在这里做的事情明显错误吗？

Answer 1

您的表是否有适当的索引用于删除您正在执行的连接？这些语句的执行计划是什么样的？