SQL Server 2008 Laufender Trigger nach Einfügen, Update sperrt die ursprüngliche Tabelle
Ich habe ein ernstes Leistungsproblem.
Ich habe eine Datenbank mit (im Zusammenhang mit diesem Problem), 2 Tabellen.
1 Tabelle enthält Zeichenfolgen mit einigen globalen Informationen. Die zweite Tabelle enthält die Zeichenfolge, die auf jedes einzelne Wort reduziert ist. Die Zeichenfolge ist also wie in der zweiten Tabelle Wort für Wort indiziert.
Die Gültigkeit der Daten in der zweiten Tabelle ist weniger wichtig als die Gültigkeit der Daten in der ersten Tabelle.
Da die erste Tabelle zu 1 * 10 ^ 6 Datensätzen anwachsen kann und die zweite Tabelle mit einem Durchschnitt von 10 Wörtern für 1 Zeichenfolge zu 1 * 10 ^ 7 Datensätzen anwachsen kann, verwende ich einen Nolock, um die zweite zu lesen Ich kann keine neuen Datensätze einfügen, ohne sie zu sperren. (Erwarten Sie viele Lesevorgänge in beiden Tabellen.)
Ich habe ein Skript, das der ersten Tabelle in einer MERGE-Anweisung weiterhin Zeilen hinzufügt und diese aktualisiert. Im Durchschnitt werden die Daten mit jeweils 20 Zeichenfolgen zusammengeführt, und die Skripte werden alle 5 Sekunden als solche ausgeführt.
In der ersten Tabelle habe ich einen Trigger, der bei einem Insert oder Update aufgerufen wird, der die neu eingefügten oder aktualisierten Daten aufnimmt und eine gespeicherte Prozedur darauf aufruft, die sicherstellt, dass die Daten in der zweiten Tabelle indiziert sind. (Dies dauert einige Zeit.)
Das Problem ist, dass beim Deaktivieren des Triggers das Lesen der ersten Tabelle in wenigen ms erfolgt. Wenn Sie jedoch den Auslöser aktivieren und das Pech haben, die erste Tabelle zu lesen, während diese aktualisiert wird, gibt Ihnen unser Webserver nach 10 Sekunden eine Zeitüberschreitung (was ohnehin viel zu lang ist).
ch kann aus diesem Teil ableiten, dass beim Ausführen des Triggers die erste Tabelle (teilweise) in einem Schloss gehalten wird, bis der Trigger abgeschlossen is
Was denkst du, wenn ich recht habe, gibt es einen einfachen Weg, das zu umgehen?
Danke im Voraus
Wie gewünscht
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
Die FeedItems-Tabelle wird mit dieser Abfrage gefüllt:
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);
The sproc: IndexItemStrings füllt die zweite Tabelle, die Ausführung dieses Vorgangs nimmt in der Tat seine Zeit in Anspruch. Das Problem ist, dass beim Ausführen dieses Triggers. Bei Abfragen, die auf die FeedItems-Tabelle angewendet werden, tritt meistens eine Zeitüberschreitung auf (auch bei Abfragen, die die zweite Tabelle nicht verwenden
Erster Tisch:
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
Zweite Tabelle:
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
Und mehr
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
und mehr
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