Performance nach Upsizing
Joachim Granzow
In einer ACCESS-Datenbank hatte ich eine kleine Sub programmiert, die alle
17.000 Datensätze in ca. 30 Sekunden einliest.
Im ACCESS-Project läuft dieselbe Sub etwa 100 Mal solange. Stundenlang.
Völllig unbrauchbar. Woran kann das liegen? So habe ich die Verbindung
gebastelt:
Dim Rst As ADODB.RecordSet, cnn As ADODB.Connection
DoCmd.RunSQL "DELETE FROM dbo.Adressen"
Set cnn = New ADODB.Connection
Set Rst = New ADODB.RecordSet
cnn.Open CurrentProject.Connection.ConnectionString
With Rst
.Open "Adressen", cnn, adOpenForwardOnly, adLockBatchOptimistic
.... hier kommt die alte Routine ...
End with
Ich habe auch alle möglichen anderen Parameter ausprobiert, und habe am
Primärindex herumgetestet (mit und ohne usw), doch es hilt alles nichts
Jochen
Elmar Boye
"Joachim Granzow" <gra...@carlos-claussen.de> schrieb ...
> Ich muss alte DOS-Dateien auswerten und in SQL-Server-Tabellen umwandeln.
>
> In einer ACCESS-Datenbank hatte ich eine kleine Sub programmiert, die alle
> 17.000 Datensätze in ca. 30 Sekunden einliest.
>
> Im ACCESS-Project läuft dieselbe Sub etwa 100 Mal solange. Stundenlang.
> Völllig unbrauchbar. Woran kann das liegen? So habe ich die Verbindung
> gebastelt:
hängt zwar etwas davon ab, wie die Daten aufgebaut sind.
> With Rst
> .Open "Adressen", cnn, adOpenForwardOnly, adLockBatchOptimistic
>
adOpenForwardOnly stimmt bei ClientCursor eh nicht, ist adOpenStatic.
(korrigiert der Treiber, aber besser gleich richtig schreiben).
Auch solltest Du nur die Spalten angeben, die Du importierst.
Dann koppele für das Übertragen die Connection ab und übertrage
sie in einem Block.
Set .ActiveConnection = Nothing
> .... hier kommt die alte Routine ...
>
Set .ActiveConnection = cnn
.UpdateBatch
> End with
Wenn Du keine Aktualisierungen vornehmen musst (also nur .AddNew)
so kann ein entsprechende parameterisiertes Command Objekt etwas
schneller sein. Das Verwenden einer Prozedur bringt dann noch mal
(mehr oder weniger) was.
Unten dran findest Du mal ein (nicht behübschtes weil abgebrochenes)
Beispiel aus alten Tagen, das die verschiedenen Methoden ansatzweise
illustriert.
Am schnellsten geht das reine Importieren jedoch immer mit einem
BULK INSERT (oder DTS), was einen entsprechenden Aufbau für die
Datei voraussetzt (z. B. CSV Format aus Access heraus).
Gruss
Elmar
--
Sub InsertTest()
' Vergleich von Insert Methoden
' Teilweise auch für JET verwendbar
Dim cnn As ADODB.Connection
Dim nIndex As Long
Dim sTimer As Single
Dim bSQLServer As Boolean
' Verwendet einfache Strings
Dim varFeld1 As String
Dim varFeld2 As String
Dim varFeld3 As String
Dim varFeld4 As String
Dim varFeld5 As String
varFeld1 = "Wert1"
varFeld2 = "Wert2"
varFeld3 = "Wert3"
varFeld4 = "Wert4"
varFeld5 = "Wert5"
Set cnn = New ADODB.Connection
With cnn
' adUseServer für direkte Serverbibliothek
' ist bei einigen Optionen schneller, erlaubt
' jedoch kein BatchOptimistic
.CursorLocation = adUseServer ' adUseClient
.ConnectionString = CurrentProject.BaseConnectionString
.Open
End With
bSQLServer = (UCase(Left(cnn.Provider, 8)) = "SQLOLEDB")
#If False Then
' Hier mehr zur Illustration des Aufbaus (ungetestet):
' Erzeugen der Tabelle beim ersten Male
cnn.Execute "CREATE TABLE InsertTest ( " & _
"ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY, " & _
"Feld1 varchar (40) NULL, " & _
"Feld2 varchar (40) NULL, " & _
"Feld3 varchar (40) NULL, " & _
"Feld4 varchar (40) NULL, " & _
"Feld5 varchar (40) NULL )"
' Verwendete Prozedur
If bSQLServer Then
cnn.Execute "CREATE PROCEDURE dbo.spInsertTest( " & _
"( @Feld1 varchar(40), @Feld2 varchar(40), " & _
" @Feld3 varchar(40), @Feld4 varchar(40), " & _
" @Feld5 varchar(40) ) AS " & _
"SET NOCOUNT ON " & _
"INSERT INTO dbo.InsertTest (Feld1, Feld2, Feld3, Feld4, Feld5) " & _
"VALUES (@Feld1, @Feld2, @Feld3, @Feld4, @Feld5) " & _
"RETURN @@ROWCOUNT"
End If
#End If
cnn.Execute "DELETE FROM InsertTest", , adExecuteNoRecords
sTimer = Timer
' Ausführen von direkten INSERTS
For nIndex = 1 To 1000
cnn.Execute "INSERT INTO InsertTest (Feld1, Feld2, Feld3, Feld4, Feld5) " & _
"VALUES( '" & varFeld1 & "', '" & varFeld2 & "', '" & varFeld3 & "', '" & varFeld4 & "', '" & varFeld5 & "') ", _
, adExecuteNoRecords
Next nIndex
Debug.Print "Direkt: " & Timer - sTimer
' verwenden eines Stapels (bei JET nicht möglich)
If bSQLServer = True Then
Dim strSQLInsert As String
' Wenn die Schleife mit gezählt wird, bei weitem das langsamste,
' sonst das schnellste
' Zeigt somit die Schwäche der VBA-Stringverwaltung
' Ein konstantes String * 32000 ist deutlich schneller (nicht demonstriert)
sTimer = Timer
For nIndex = 1 To 1000
strSQLInsert = strSQLInsert & "EXEC spInsertTest " & _
"'" & varFeld1 & "', '" & varFeld2 & "', '" & varFeld3 & "', '" & varFeld4 & "', '" & varFeld5 & "'"
' strSQLInsert = strSQLInsert & "INSERT INTO InsertTest (Feld1, Feld2, Feld3, Feld4, Feld5) VALUES( " & _
') "
Next nIndex
' Auskommentieren um nur die Zeit des INSERTS zu messen
' sTimer = Timer
cnn.Execute "SET NOCOUNT ON", , adExecuteNoRecords
cnn.Execute strSQLInsert, , adExecuteNoRecords
cnn.Execute "SET NOCOUNT OFF", , adExecuteNoRecords
Debug.Print "Stapel: " & Timer - sTimer
strSQLInsert = ""
End If
' Verwenden eines Vorbereiteten Command Objekts
Dim cmdInsert As ADODB.Command
Set cmdInsert = New ADODB.Command
sTimer = Timer
With cmdInsert
' Hängt vom Treiberstand ab eher ineffizient
.Prepared = True
.CommandType = adCmdText
.CommandText = "INSERT INTO InsertTest (Feld1, Feld2, Feld3, Feld4, Feld5) " & _
"VALUES(?, ?, ?, ?, ?)"
For nIndex = 1 To 5
.Parameters.Append .CreateParameter("Feld" & nIndex, adVarChar, adParamInput, 40)
Next nIndex
Set .ActiveConnection = cnn
End With
For nIndex = 1 To 1000
cmdInsert.Execute , Array(varFeld1, varFeld2, varFeld3, varFeld4, varFeld5), adExecuteNoRecords
Next nIndex
Debug.Print "Command: " & Timer - sTimer
Set cmdInsert = Nothing
' Verwenden einer gepspeicherten Prozedur
' deutlich schneller wird wenns viel ist oder komplexer wird
If bSQLServer = True Then
Dim cmdInsertSP As ADODB.Command
Set cmdInsertSP = New ADODB.Command
sTimer = Timer
With cmdInsertSP
.CommandType = adCmdStoredProc
.CommandText = "spInsertTest"
For nIndex = 1 To 5
.Parameters.Append .CreateParameter("Feld" & nIndex, adVarChar, adParamInput, 40)
Next nIndex
Set .ActiveConnection = cnn
End With
For nIndex = 1 To 1000
cmdInsertSP.Execute , Array(varFeld1, varFeld2, varFeld3, varFeld4, varFeld5), adExecuteNoRecords
Next nIndex
Debug.Print "Procedure: " & Timer - sTimer
Set cmdInsertSP = Nothing
End If
' Mit BatchOptimistic Cursor (nur Client)
If cnn.CursorLocation = adUseClient Then
Dim rstInsert As New ADODB.Recordset
sTimer = Timer
With rstInsert
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = "SELECT Feld1, Feld2, Feld3, Feld4, Feld5 FROM InsertTest WHERE 0=1"
Set .ActiveConnection = cnn
.Open
Set .ActiveConnection = Nothing
End With
For nIndex = 1 To 1000
rstInsert.AddNew Array("Feld1", "Feld2", "Feld3", "Feld4", "Feld5"), _
Array(varFeld1, varFeld2, varFeld3, varFeld4, varFeld5)
Next nIndex
Set rstInsert.ActiveConnection = cnn
rstInsert.UpdateBatch adAffectAll
Debug.Print "Recordset: " & Timer - sTimer
rstInsert.Close
Set rstInsert = Nothing
End If
cnn.Execute "DELETE FROM InsertTest", , adExecuteNoRecords
cnn.Close
End Sub
Joachim Granzow
cnn.CursorLocation = adUseClient hat's gebracht.
Jochen
www.joachim-granzow.de
P.S.: jetzt muss ich nur noch den SQL-Denksport begreifen - jedenfalls schon
einmal auch dafür viele Dank!