Es wäre also hilfreich und würde den Aufwand halbieren, wenn man beides in eine Abfrage bündeln könnte, eine UPSERT-Funktion also, die man als Datenbankskript (StoredProcedure). in der Datenbank speichert. So ein Skript könnte etwa so aussehen:
-- =============================================
-- Author: Peter-W. Fischer
-- Create date: 29.4.2011
-- Description:
-- =============================================
--CREATE TABLE [dbo].[Test](
-- [TestID] [uniqueidentifier] NOT NULL,
-- [TestWert] [nvarchar](200) NOT NULL,
-- [Inserted] [datetime] NOT NULL,
-- [Updated] [datetime] NULL,
-- CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
--(
-- [TestID] ASC
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--) ON [PRIMARY]
CREATE PROCEDURE [dbo].[TestUpSert]
@TestWert nvarchar(200)
,@Check uniqueidentifier OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @Check =null
BEGIN TRANSACTION
UPDATE dbo.Test WITH(SERIALIZABLE)
set
@Check = TestID
,Updated=GETDATE()
where TestWert = @TestWert
IF @Check is null
BEGIN
set @Check=NEWID();
INSERT dbo.Test (TestID, TestWert, Inserted)
values (@Check, @TestWert, GETDATE())
END
COMMIT TRANSACTION
-- select * from dbo.Test
END
GO
Die Tabelle hat vier Spalten.
- TestID (Uniqueidentifier/Primary Key),
- TestWert, die keine doppelten Einträge zulässt.(Indiziert!)
- Inserted (wird nur beim ersten Eintragen gesetzt) und
- Updated (wird bei jeder Aktualisierung gesetzt)
- TestWert, der neue oder zu aktualisierende Eintrag
- Check, ein Rückgabeparameter, der die TestID zur späteren Verwendung enthält
Falls aber Check nach dem Update immer noch NULL ist, gab es TestWert noch nicht in der Tabelle und wird nun per INSERT eingefügt; dabei wird die Spalte Inserted ebenfalls aktualisiert.
Das ganze wird threadsafe durch die Durchführung in einer Transaction und dem SERIALIZE-Attribut beim UPDATE.
Soweit, so gut.
Zum Testen kann man folgendes Skript verwenden:
DECLARE @Check uniqueidentifier
,@Counter int=0
,@Wert nvarchar(100)
Print 'Start: ' + CONVERT(nvarchar(30), GETDATE(), 126)
WHILE @Counter<100000
BEGIN
SET @Wert='Wert' + CONVERT(nvarchar(10),FLOOR(RAND()*50000))
EXEC dbo.TestUpSert @Wert, @Check OUTPUT
SET @Counter=@Counter+1
END
Print 'Ende: ' + CONVERT(nvarchar(30), GETDATE(), 126)
SELECT * FROM dbo.Test ORDER BY TestWert
Es werden 100.000 Einträge in die Tabelle vorgenommen, wobei der TestWert aus dem Wort 'Wert' und einer Zufallszahl zwischen 0 und 49999 besteht, damit es mit einer 50% Chance zu Updates kommen kann.
Server intern: ~ 50 Sekunden
Die Anfangs - und Endzeit des Skripts wird ausgegeben. Bei mir, auf einem mittelmäßigen Dualcore-PC, braucht das Skipt ca. 50 Sekunden, also im Schnitt etwa eine halbe Millisekunde pro Eintrag, was recht schnell ist.
Leider lässt sich diese Zeit nicht erreichen, wenn man TestUpSert aus einem VB .NET Programm aufruft. Ich habe dort in den Datadesigner die StoredProcedure übernommen, sie ist dort anschließend unter QueriesTableAdapter verfügbar und kann mit einer einfachen Funktion aufgerufen werden:
Dim start As Date = Now
Console.WriteLine("{0:G}: StoredProcedure-Test gestartet.", start)
Using qadap As New dsMMDBTableAdapters.QueriesTableAdapter
Dim i As Integer, id As Guid = Guid.Empty
For i = 1 To 100000
qadap.TestUpSert("Testwert" & Int(Rnd() * 50000), id)
Next
End Using
Console.WriteLine("{0:G}: StoredProcedure-Test beendet. Dauer: {1:0.000} Sekunden", Now, Now.Subtract(start).TotalSeconds)
Aufruf per .NET: ~ 8 Minuten
Im Prinzip passiert hier exakt das selbe wie im obigen Skript; allerdings dauert nun die ganze Aktion über 7 (!) Minuten, also mehr als 8x so lang. Tatsächlich braucht jeder neue Eintrag ein paar Millisekunden länger, der letzte fast 20 Sekunden, obwohl FeldWert in der Tabelle indiziert ist?!
Eine weitere Möglichkeit besteht darin, die Eintragungen zunächst innerhalb des Programms , also direkt im Speicher vorzunehmen und anschließend die gesamte Tabelle in einem einzigen Update in die Datenbank zu schreiben. Dazu könnte man zunächst mit einer [Tabelle].Select-Abfrage testen ob der Wert bereits vorhanden ist und danach entweder updaten oder inserten. Tatsächlich kostet dieses Verfahren ebenfalls sehr viel Zeit, wenn die Zahl der Einträge in die Hunderttausend geht. Erheblich verbessern kann man das, indem man einen virtuelle Index auf die Testwert-Spalte setzt. Das geht, indem man ein Dataview über die Tabelle stülpt, das mit einem Sortierfilter arbeitet:
dvTab = New DataView(Tab)
dvTab.Sort = Tab.TestWertColumn.ColumnName
Dann sieht die eigentliche interne UpSert-Funktion so aus:
Public Function Upsert(ByVal Testwert As String) As dsMMDB.TestRow
Upsert = Nothing
Try
Dim rws() As DataRowView = dvTab.FindRows(Testwert)
Dim rw As dsMMDB.TestRow = Nothing
If rws Is Nothing Or rws.Length = 0 Then
rw = Tab.NewTestRow
rw.TestID = Guid.NewGuid
rw.TestWert = Testwert
rw.Inserted = Now
Tab.AddTestRow(rw)
Else
rw = rws(0).Row
rw.Updated = Now
End If
Return rw
Catch ex As Exception
End Try
End Function
.Net intern, dann DB-Update: ~ 4 Minuten
Die braucht, was kaum überrascht, für das interne Eintragen von 100.000 Werten nur knapp 5 Sekunden, allerdings braucht das Updaten der Datenbank dann zusätzliche 4 Minuten. Das sind aber immerhin nur noch etwas mehr als die Hälfte der Zeit, die der Aufruf der StoredProcedure braucht.
Aufgrund dieser Ergebnisse, würde ich also immer die letzte Methode bevorzugen, wenn es darum geht, eine große Menge von Daten in kurzer Zeit entweder zu aktualisieren oder zu insertieren. Der Nachteil der internen Methode ist allerdings, dass Daten solange verloren gehen können, solange sie nicht komplett in die Datenbank geschrieben wurden. Wenn es also vor allem um Datensicherheit geht, ist der Aufruf der StoredProcedure wohl unumgänglich.
Warum dieses Verfahren allerdings so 'teuer', also zeitintensiv ist, konnte ich bisher auch nicht erklären.
Falls jemand was weiß - bitte posten!
