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

The sproc: IndexItemStrings is populating the second table, executing this proc does indeed take his time. The problem is that while executing this trigger. Queries applied to the FeedItems table are mostly timing out (even those queries who dont uses the second table)

第一张桌子:

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 

推荐答案

我在 Internet 上环顾四周，找不到任何方法可以在不要求锁定的情况下触发触发器.因此，我选择通过存储过程进行插入，然后执行之前在触发器中找到的逻辑.这使我能够在插入实际数据并解除插入锁之后在事务中执行触发器的内容.