SQL Server 2008在插入、更新后运行触发器锁定原始表

Question

我有严重的性能问题。

我有一个数据库（与这个问题相关），2 个表。

1 表包含带有一些全局信息的字符串。第二个表包含被剥离为每个单独单词的字符串。因此，该字符串就像在第二个表中逐字索引一样。

第二个表中数据的有效性不如第一个表中数据的有效性重要。

由于第一个表可以增长到 1*10^6 条记录，而第二个表的 1 个字符串平均有 10 个单词，可以增长到 1*10^7 条记录，所以我使用 nolock 来读取剩下的第二个表我可以自由地插入新记录而不锁定它（预计两个表上都会有很多读取）。

我有一个脚本，它不断向 MERGE 语句中的第一个表添加和更新行。平均而言，一次合并的数据约为 20 个字符串，脚本每 5 秒运行一次。

在第一个表上，我有一个在插入或更新时调用的触发器，它获取新插入或更新的数据并调用其上的存储过程，以确保数据在第二个表中建立索引。 （这需要一些重要的时间）。

问题是，当触发器被禁用时，读取第一个表会在几毫秒内发生。然而，当启用触发器时，如果您不幸在更新时尝试读取第一个表，我们的网络服务器会在 10 秒后给您一个超时（无论如何，这太长了）。

我可以从这一部分看出，运行触发器时，第一个表（部分）保持锁定状态，直到触发器完成。

如果我是对的，你觉得怎么样，有没有简单的方法可以解决这个问题？

提前致谢！

根据要求：

ALTER TRIGGER [dbo].[OnFeedItemsChanged] 
   ON  [dbo].[FeedItems] 
   AFTER INSERT,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @id int;
    SELECT @id = ID FROM INSERTED;
    IF @id IS NOT NULL
    BEGIN
        DECLARE @title nvarchar(MAX);
        SELECT @title = Title FROM INSERTED;
        DECLARE @description nvarchar(MAX);
        SELECT @description = [Description] FROM INSERTED;

        SELECT @title = dbo.RemoveNonAlphaCharacters(@title)
        SELECT @description = dbo.RemoveNonAlphaCharacters(@description)

        -- Insert statements for trigger here
        EXEC dbo.usp_index_itemstring @id, @title;
        EXEC dbo.usp_index_itemstring @id, @description;
    END
END

FeedItems 表由以下查询填充：

MERGE INTO FeedItems i
USING @newitems d ON i.Service = d.Service AND i.GUID = d.GUID
WHEN matched THEN UPDATE
    SET i.Title = d.Title,
        i.Description = d.Description,
        i.Uri = d.Uri,
        i.Readers = d.Readers
WHEN NOT matched THEN INSERT
    (Service, Title, Uri, GUID, Description, Readers)
    VALUES
    (d.Service, d.Title, d.Uri, d.GUID, d.Description, d.Readers);

存储过程：IndexItemStrings 正在填充第二个表，执行此过程确实需要时间。问题是在执行这个触发器时。应用于 FeedItems 表的查询大多会超时（即使是那些不使用第二个表的查询）

第一个表：

USE [ICI]
GO

/****** Object:  Table [dbo].[FeedItems]    Script Date: 04/09/2010 15:03:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FeedItems](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Service] [int] NOT NULL,
    [Title] [nvarchar](max) NULL,
    [Uri] [nvarchar](max) NULL,
    [Description] [nvarchar](max) NULL,
    [GUID] [nvarchar](255) NULL,
    [Inserted] [smalldatetime] NOT NULL,
    [Readers] [int] NOT NULL,
 CONSTRAINT [PK_FeedItems] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FeedItems]  WITH CHECK ADD  CONSTRAINT [FK_FeedItems_FeedServices] FOREIGN KEY([Service])
REFERENCES [dbo].[FeedServices] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FeedItems] CHECK CONSTRAINT [FK_FeedItems_FeedServices]
GO

ALTER TABLE [dbo].[FeedItems] ADD  CONSTRAINT [DF_FeedItems_Inserted]  DEFAULT (getdate()) FOR [Inserted]
GO

第二个表：

USE [ICI]
GO

/****** Object:  Table [dbo].[FeedItemPhrases]    Script Date: 04/09/2010 15:04:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FeedItemPhrases](
    [FeedItem] [int] NOT NULL,
    [Phrase] [int] NOT NULL,
    [Count] [smallint] NOT NULL,
 CONSTRAINT [PK_FeedItemPhrases] PRIMARY KEY CLUSTERED 
(
    [FeedItem] ASC,
    [Phrase] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FeedItemPhrases]  WITH CHECK ADD  CONSTRAINT [FK_FeedItemPhrases_FeedItems] FOREIGN KEY([FeedItem])
REFERENCES [dbo].[FeedItems] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FeedItemPhrases] CHECK CONSTRAINT [FK_FeedItemPhrases_FeedItems]
GO

ALTER TABLE [dbo].[FeedItemPhrases]  WITH CHECK ADD  CONSTRAINT [FK_FeedItemPhrases_Phrases] FOREIGN KEY([Phrase])
REFERENCES [dbo].[Phrases] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FeedItemPhrases] CHECK CONSTRAINT [FK_FeedItemPhrases_Phrases]
GO

以及更多：

ALTER PROCEDURE [dbo].[usp_index_itemstring] 
    -- Add the parameters for the stored procedure here
    @item int, 
    @text nvarchar(MAX) 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- DECLARE a table containing all words within the text
    DECLARE @tempPhrases TABLE 
    ( 
        [Index] int,
        [Phrase] NVARCHAR(256) 
    );

    -- extract each word from text and store it in the temp table
    WITH Pieces(pn, start, [stop]) AS 
    ( 
        SELECT 1, 1, CHARINDEX(' ', @text) 
        UNION ALL 
        SELECT pn + 1, CAST([stop] + 1 AS INT), CHARINDEX(' ', @text, [stop] + 1) 
        FROM Pieces 
        WHERE [stop] > 0 
    )   
    INSERT INTO @tempPhrases
    SELECT pn, SUBSTRING(@text, start, CASE WHEN [stop] > 0 THEN [stop]-start ELSE LEN(@text) END) AS s 
    FROM Pieces
    OPTION (MAXRECURSION 0);    

    WITH CombinedPhrases ([Phrase]) AS 
    (
        -- SELECT ALL 2-WORD COMBINATIONS
        SELECT w1.[Phrase] + ' ' + w2.[Phrase] 
        FROM @tempPhrases w1
        JOIN @tempPhrases w2 ON w1.[Index] + 1 = w2.[Index]
        UNION ALL -- SELECT ALL 3-WORD COMBINATIONS
        SELECT w1.[Phrase] + ' ' + w2.[Phrase] + ' ' + w3.[Phrase]
        FROM @tempPhrases w1
        JOIN @tempPhrases w2 ON w1.[Index] + 1 = w2.[Index]
        JOIN @tempPhrases w3 ON w1.[Index] + 2 = w3.[Index]
        UNION ALL  -- SELECT ALL 4-WORD COMBINATIONS
        SELECT w1.[Phrase] + ' ' + w2.[Phrase] + ' ' + w3.[Phrase] + ' ' + w4.[Phrase]
        FROM @tempPhrases w1
        JOIN @tempPhrases w2 ON w1.[Index] + 1 = w2.[Index]
        JOIN @tempPhrases w3 ON w1.[Index] + 2 = w3.[Index]
        JOIN @tempPhrases w4 ON w1.[Index] + 3 = w4.[Index]
    )

    -- ONLY INSERT THE NEW PHRASES IN THE Phrase TABLE      
    INSERT INTO @tempPhrases
    SELECT 0, [Phrase] FROM CombinedPhrases

    -- DELETE PHRASES WHICH ARE EXCLUDED
    DELETE FROM @tempPhrases
    WHERE [Phrase] IN
    (
        SELECT [Text] FROM Phrases p
        JOIN ExcludedPhrases ex
        ON ex.ID = p.ID
    );

    MERGE INTO Phrases p
    USING 
    (
        SELECT DISTINCT Phrase FROM @tempPhrases
    ) t
    ON p.[Text] = t.Phrase
    WHEN NOT MATCHED THEN
        INSERT VALUES (t.Phrase);


    -- Finally create relations between the phrases and feeditem,   
    MERGE INTO FeedItemPhrases p
    USING 
    (
        SELECT @item as [Item], MIN(p.[ID]) as Phrase, COUNT(t.[Phrase]) as [Count]
        FROM Phrases p WITH (NOLOCK)
        JOIN @tempPhrases t ON p.[Text] = t.[Phrase]
        GROUP BY t.[Phrase]
    ) t
    ON p.FeedItem = t.Item
    AND p.Phrase = t.Phrase
    WHEN MATCHED THEN
        UPDATE SET p.[Count] = t.[Count]
    WHEN NOT MATCHED THEN
        INSERT VALUES (t.[Item], t.Phrase, t.[Count]);
END

以及更多：

ALTER Function [dbo].[RemoveNonAlphaCharacters](@Temp NVarChar(max)) 
Returns NVarChar(max) 
AS 
Begin 
    SELECT @Temp = REPLACE (@Temp, '%20', ' ');

    While PatIndex('%[^a-z ]%', @Temp) > 0 
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z ]%', @Temp), 1, '') 
    Return @TEmp 
End 

Answer 1

我在互联网上查了一下，我找不到任何方法可以在不声明锁的情况下触发触发。因此，我选择通过存储过程进行插入，该存储过程又执行先前在触发器中找到的逻辑。这允许我在插入实际数据并解除插入锁后执行事务中触发器的内容。

希望这有帮助！