Datenabgleich SQL Server 2005

11/03/2009 - 17:49 von Dirk Ohrmund | Report spam
Hallo,
ich habe folgendes Problem:
Meine Datenbank (2,2GB) auf einem SQL Server 2005 Standard SP3 (Windows
Server 2003 Standard SP2)hat folgende Tabelle:

AutoID int
ArtikelNr nvarchar(30)
ArtikelNrTrim nvarchar(30)
SortNr nvarchar(30)
SortBuchstabe nvarchar(1)
Preis money
Hersteller nvarchar(8)
Bezeichnung nvarchar(50)
Datum datetime

mit 1,8 Mio Eintràgen.
Die Tabelle hat den PK auf AutoID und je einen Index auf ArtikelNrTrim und
Hersteller.
Die Tabelle hat eine Größe von 220 MB, der Index von 90 MB.
Es ist keine Volltextindexierung eingestellt.

Hardware: Dual XEON System mit 2,66 Ghz, 4 GB RAM

SQL Servereigenschaft:
Min serverarbeitsspeicher: 1024 MB
Max Serverarbeitsspeicher: 2147483647 MB
Arbeitsspeicher für Indexerstellung: 0
Minimaler Arbeitsspeicher pro Abfrage: 1024 KB

Prozessor-Affinitàtsmaske für alle Prozessoren automatisch festlegen: ja
E/A-Affinitàtsmaske für alle Prozessoren automatisch festlegen: ja

Max Threadanzahl: 0
SQL-Server-Prioritàt höher stufen: ja
Windows-Fibers verwenden: ja

Entwicklungsumgebung: Visual Studio 2008 Professional SP1
Entwicklungssprache: Visual Basic 2008

Ich lese eine CSV-Datei mit 350.000 DS in eine Datatable ein.
Mit einer
For Each cRow As DataRow In dt.Rows
...
WriteSQLData(ArtikelNr, Hersteller)
...
Next
lese ich die einzelnen DS aus und konvertiere (z.B. ein Punkt durch einen
Komma; Leerzeichen entfernen)einige Elemente des DS.

Die Datatable wird mit der Datenbank verglichen.


Private Sub WriteSQLData(ArtikelNr, Hersteller)
Dim dt As New DataTable
Dim SQLBefehl As String = "SELECT * FROM ORI_Preislisten WHERE
ArtikelNr ='" & ArtikelNr & "' AND Hersteller = '" & Hersteller & "'"
Dim da As New SqlDataAdapter(SQLBefehl, Conn)
da.Fill(dt)

Dim cmd As SqlCommand = Conn.CreateCommand

If dt.Rows.Count = 0 Then 'Kein Datensatz vorhanden
cmd.CommandText = "INSERT INTO ORI_Preislisten " & _
"(ArtikelNr, ArtikelNrTrim," & _
"SortNr, SortBuchstabe," & _
"Preis, Hersteller, Bezeichnung, Datum)" & _
"VALUES (" & _
"'" & ArtikelNr & "'," & _
"'" & ArtikelNrTrim & "'," & _
"'" & SortNr & "'," & _
"'" & Sortbuchstabe & "'," & _
"'" & Preis & "'," & _
"'" & Hersteller & "'," & _
"'" & Bezeichnung & "'," & _
"'" & Datum & "')"
cmd.ExecuteNonQuery()
Else 'Datensatz vorhanden
Dim rw As DataRow = dt.Rows(0)
Dim DataPreis As String = rw("Preis").ToString
DataPreis = DataPreis.Replace(",", ".")
Dim Preis1 As String = ""

If DataPreis.Contains(".") = True Then
Dim Sem() As String
Sem = DataPreis.Split(".")
If Sem.Length = 2 Then
If Sem(1).Length = 4 Then
DataPreis = Sem(0) & "." & Sem(1).Substring(0, 2)
End If
End If
End If
If DataPreis <> Preis Then 'Preise unterschiedlich? Wenn ja
UPDATE
cmd.CommandText = "UPDATE ORI_Preislisten SET " & _
"Preis ='" & Preis & "'," & _
"Datum ='" & Datum & "'" & _
" Where AutoID ='" & rw("AutoID").ToString & "'"
cmd.ExecuteNonQuery()
End If
End If
End Sub


Ich frage also, ob der DS in der DB vorhanden, wenn nein INSERT, wenn ja
UPDATE.

Ist die Tabelle leer, so schaffe ich bis zu 300 DS / s.
Bei voller Tabelle schaffe ich nur 7 DS / s. Also viel zu langsam.

Mein Programm liegt bei unter 1% Auslastung.
Bei dem SQL-Server 2005 sind beide XEON-Prozessoren auf 100%. Und das bei 7
DS / s!!!

Ein Dataset (lokales Abbild der DB) habe ich nicht benutzt, da die DB für
die Clients zu groß ist.
Die Clients sind bei uns nur kleine Terminals.

Hat jemand vielleicht eine Lösung parat.
Bitte mit Beispielcode.

Im voraus vielen Dank.

Dirk Ohrmund
 

Lesen sie die antworten

#1 Elmar Boye
11/03/2009 - 19:47 | Warnen spam
Hallo Dirk,

"Dirk Ohrmund" schrieb im Newsbeitrag news:gp8q2k$jg3$
ich habe folgendes Problem:
Meine Datenbank (2,2GB) auf einem SQL Server 2005 Standard SP3 (Windows Server 2003 Standard SP2)hat folgende Tabelle:


mit 1,8 Mio Eintràgen.
Die Tabelle hat den PK auf AutoID und je einen Index auf ArtikelNrTrim und Hersteller.
Die Tabelle hat eine Größe von 220 MB, der Index von 90 MB.
Es ist keine Volltextindexierung eingestellt.

Hardware: Dual XEON System mit 2,66 Ghz, 4 GB RAM

Ich lese eine CSV-Datei mit 350.000 DS in eine Datatable ein.
Mit einer
For Each cRow As DataRow In dt.Rows
...
WriteSQLData(ArtikelNr, Hersteller)
...
Next
lese ich die einzelnen DS aus und konvertiere (z.B. ein Punkt durch einen Komma; Leerzeichen entfernen)einige Elemente des DS.



Der Knackpunkt liegt hier. Denn Du betreibst eine zeilenweise
verarbeitung und die ist langsam.
Hinzu kommen etliche Roundtrips Client <-> Datenbank.

Die Datatable wird mit der Datenbank verglichen.

Private Sub WriteSQLData(ArtikelNr, Hersteller)
Dim dt As New DataTable
Dim SQLBefehl As String = "SELECT * FROM ORI_Preislisten WHERE ArtikelNr ='" & ArtikelNr & "' AND Hersteller = '" &
Hersteller & "'"
Dim da As New SqlDataAdapter(SQLBefehl, Conn)
[...]



auch hier wird viel Zeit mit dem Erstellen von SQL Befehlen etc. vertan.
Wenn Du dabei bleiben willst, solltest Du durchgàngig (auch für
INSERT, UPDATE) vorbereitete SqlCommand Objekte mit SqlParameter
verwenden.
Oder gar eine Prozedur für alles zusammen (vermeidet die Roundtrips).

Zudem solltest Du in einer Transaktion arbeiten, nicht nur wegen
der Sicherheit, sondern weil dadurch weniger protokolliert werden muß.

Ist die Tabelle leer, so schaffe ich bis zu 300 DS / s.
Bei voller Tabelle schaffe ich nur 7 DS / s. Also viel zu langsam.



Ein anderer Ansatz als Alternative, um es mehr zu beschleunigen:
Bereite die Daten in der DataTable auf (in Blöcken a 10TSD...,
je nach verfügbaren Speicher), so dass sie direkt eingefügt werden können.

Für den Import kannst Du z. B. verwenden:
http://www.codeproject.com/KB/datab...eader.aspx
wenn Du da bisher manuell arbeitest.

Für den Import verwende SqlBulkCopy anstatt eines SqlDataAdapter:
http://msdn.microsoft.com/de-de/lib...qlbulkcopy(VS.80).aspx
um die Daten in eine Import-Tabelle zu übertragen.

Nach dem Import wende auf die Daten eine setorientierte Verarbeitung an:
BEGIN TRAN

UPDATE dbo.ORI_Preisliste
SET Preis = Import.Preis,
FROM dbo.ORI_Preisliste
INNER JOIN dbo.ORI_PreislisteImport AS Import
ON ORI_Preisliste.ArtikelNr = ORI_PreislisteImport.ArtikelNr
WHERE ORI_Preisliste.Preis <> Import.Preis

INSERT INTO ORI_Preisliste (...)
SELECT ...
FROM ORI_PreislisteImport -- die Import-Tabelle
WHERE NOT EXISTS(SELECT * FROM ORI_Preisliste
WHERE ORI_Preisliste.ArtikelNr ORI_PreislisteImport.ArtikelNr)

COMMIT

Das kannst Du als SqlCommand Objekte via ExecuteNonQuery
absetzen. BEGIN TRAN ... COMMIT wàre hier eine SqlTransaction
bzw. ein TransactionScope, so dass bei Fehler die Aktion
zurückgesetzt werden kann.

Damit sollte sich die Sache insgesamt beschleunigen.

Ähnliches könnte man auch mit den Integration Services
erreichen, aber wenn Du bisher nichts damit gemacht hast,
wàre der Einarbeitungsaufwand dafür vermutlich höher.

Gruß Elmar

Ähnliche fragen