So steigern Sie die Leistung für bulk-INSERTs auf ODBC verknüpfte Tabellen in Access?

Diese situation ist nicht ungewöhnlich, dass beim Umgang mit bulk-INSERTs auf ODBC verknüpfte Tabellen in Access. Im Falle der folgenden Access-Abfrage

INSERT INTO METER_DATA (MPO_REFERENCE) 
SELECT MPO_REFERENCE FROM tblTempSmartSSP

wo [METER_DATA] ist einer ODBC-verknüpften Tabelle und [tblTempSmartSSP] ist eine lokale (native) Access-Tabelle, ODBC etwas begrenzt ist, wie klug es sein kann, denn es muss in der Lage sein, um eine Breite Palette von Ziel-Datenbanken, deren Funktionen stark variieren kann. Leider ist es oft bedeutet, dass trotz der einzelnen Access-SQL-statement, was tatsächlich gesendet wird, um den Remoteserver (verknüpften) Datenbank ist eine separate INSERT (oder gleichwertig) für jede Zeile in der lokalen Tabelle. Verständlicherweise, die nachweisen können, sehr langsam zu sein, wenn die lokale Tabelle enthält eine große Anzahl von Zeilen.

Option 1: Native bulk-Einfügungen in der entfernten Datenbank

Alle Datenbanken haben eine oder mehrere native Mechanismen für das Massenkopieren von Daten: Microsoft SQL-Server "bcp" und BULK INSERT, und Oracle hat mit "SQL*Loader". Diese Mechanismen sind optimiert für bulk-Operationen und bieten in der Regel erhebliche Geschwindigkeits-Vorteile. In der Tat, wenn die Daten in Access importiert und "massiert", bevor Sie übertragen werden, um die remote-Datenbank kann es immer noch schneller, sichern Sie die geänderten Daten wieder in eine text-Datei und dann " bulk import in die remote-Datenbank.

Option 2: Verwenden Sie eine pass-through-Abfrage in Access

Wenn die bulk-import-Mechanismen nicht durchführbar, dann eine weitere Möglichkeit ist, erstellen Sie eine oder mehrere pass-through-Abfragen in Access, um die Daten hochzuladen, die Verwendung von INSERT-Anweisungen einfügen können mehr als eine Zeile zu einem Zeitpunkt.

Zum Beispiel, wenn der remote-Datenbank wurde SQL Server (2008 oder höher) - dann könnten wir ausführen einer Access-pass-through - (T-SQL) - Abfrage, wie diese

INSERT INTO METER_DATA (MPO_REFERENCE) VALUES (1), (2), (3)

einfügen von drei Zeilen mit einer INSERT-Anweisung.

Laut einer Antwort auf eine andere frühere Frage hier die entsprechende syntax für die Oracle wäre

INSERT ALL
    INTO METER_DATA (MPO_REFERENCE) VALUES (1)
    INTO METER_DATA (MPO_REFERENCE) VALUES (2)
    INTO METER_DATA (MPO_REFERENCE) VALUES (3)
SELECT * FROM DUAL;

Getestet habe ich diese Vorgehensweise mit SQL Server (da ich don ' T haben Zugang zu einer Oracle-Datenbank) mit einem nativen [tblTempSmartSSP] Tabelle mit 10.000 Zeilen. Der code ...

Sub LinkedTableTest()
    Dim cdb As DAO.Database
    Dim t0 As Single

    t0 = Timer
    Set cdb = CurrentDb
    cdb.Execute _
            "INSERT INTO METER_DATA (MPO_REFERENCE) " & _
            "SELECT MPO_REFERENCE FROM tblTempSmartSSP", _
            dbFailOnError
    Set cdb = Nothing
    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

... dauerte ungefähr 100 Sekunden ausgeführt in meiner test-Umgebung.

Dagegen der folgende code, der baut multi-row INSERTs wie oben beschrieben (mit dem, was Microsoft ruft ein Tabellenwertkonstruktor) ...

Sub PtqTest()
    Dim cdb As DAO.Database, rst As DAO.Recordset
    Dim t0 As Single, i As Long, valueList As String, separator As String

    t0 = Timer
    Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE FROM tblTempSmartSSP", dbOpenSnapshot)
    i = 0
    valueList = ""
    separator = ""
    Do Until rst.EOF
        i = i + 1
        valueList = valueList & separator & "(" & rst!MPO_REFERENCE & ")"
        If i = 1 Then
            separator = ","
        End If
        If i = 1000 Then
            SendInsert valueList
            i = 0
            valueList = ""
            separator = ""
        End If
        rst.MoveNext
    Loop
    If i > 0 Then
        SendInsert valueList
    End If
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

Sub SendInsert(valueList As String)
    Dim cdb As DAO.Database, qdf As DAO.QueryDef

    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    qdf.Connect = cdb.TableDefs("METER_DATA").Connect
    qdf.ReturnsRecords = False
    qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE) VALUES " & valueList
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set cdb = Nothing
End Sub

... dauerte zwischen 1 und 2 Sekunden, um den gleichen Ergebnissen.

(T-SQL Tabelle mit dem Wert Konstruktoren beschränkt sich auf das einfügen von 1000 Zeilen in einer Zeit, so dass der obige code ist ein wenig komplizierter, als wäre es anders.)

Sorry, ich habe vergessen den code:

Option Compare Database
Option Explicit

Public Function Run_Safe_SQL(strSQL)
On Error GoTo Error_Handler
Dim db As DAO.Database

   Set db = CurrentDb()
   db.Execute strSQL, dbFailOnError
   DBEngine.Idle dbRefreshCache
'   DoEvents

Exit_Here:
   'Cleanup
   Set db = Nothing
   strSQL = ""
   Exit Function

Error_Handler:
    MsgBox Err.Description & " " & Err.Number

End Function