sql-server – SQL Server 2008在Insert,Update之后运行触发器锁

我有一个严重的性能问题.

我有一个数据库(与此问题相关),2个表.

1表包含带有一些全局信息的字符串.第二个表包含向下分散到每个单词的字符串.所以字符串就像在第二个表中逐字索引一样.

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

由于第一个表可以增长到1 * 10 ^ 6个记录,而第二个表平均有10个字,1个字符串可以增长为1 * 10 ^ 7个记录,我使用一个nolock来读取第二个这个叶子我可以自由地插入新记录而不锁定它(期望在两个表上读取很多内容).

我有一个脚本,它继续向MERGE语句中的第一个表添加和更新行.平均而言,合并的数据类似于每次20个字符串,并且脚本每5秒运行一次.

在第一个表上,我有一个在Insert或Update上调用的触发器,它接受新插入或更新的数据并在其上调用存储过程,这确保数据在第二个表中被索引. (这需要一些重要的时间).

问题是当触发器被移除时,读取第一个表就会在几毫秒内发生.但是,当启用触发器并且在更新时尝试读取第一个表时运气不好,我们的网络服务器会在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);

sproc：IndexItemStrings正在填充第二个表,执行此proc确实需要他的时间.问题是在执行此触发器时.应用于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,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),@text,[stop] + 1) 
        FROM Pieces 
        WHERE [stop] > 0 
    )   
    INSERT INTO @tempPhrases
    SELECT pn,SUBSTRING(@text,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

（编辑：南京站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!