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

和更多：

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),'') 
    Return @TEmp 
End

解决方法

我在互联网上环顾四周,在找不到锁定的情况下,我找不到任何触发器的方法.因此,我选择通过存储过程执行插入操作,而存储过程又执行先前在触发器中找到的逻辑.这允许我在插入实际数据并解除插入锁定之后在事务中执行触发器的内容.

希望这可以帮助！

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

解决方法

猜你在找的MsSQL相关文章