oracle - ¿Cómo aumentar el rendimiento de INSERT a granel en las tablas vinculadas de ODBC en Access?

ms-access access-vba

Question

Tengo archivos CSV y TXT para importar. Estoy importando los archivos en Access y luego inserto los registros en una tabla de Oracle vinculada. Cada archivo tiene alrededor de 3 millones de filas y el proceso tarda mucho tiempo en completarse.

Importar en Access es muy rápido, pero insertarlo en la tabla de Oracle vinculada lleva mucho tiempo.

Este es el proceso que estoy usando actualmente:

DoCmd.TransferText acImportFixed, "BUSSEP2014 Link Specification", "tblTempSmartSSP", strFName, False
db.Execute "INSERT INTO METER_DATA ([MPO_REFERENCE]) SELECT MPO_REFERENCE FROM tblTempSmartSSP;"`

tblTempSmartSSP es una tabla de acceso y METER_DATA es una tabla de Oracle vinculada

También intenté importar directamente a la tabla vinculada y eso también fue muy lento.

¿Cómo puedo acelerar el proceso?

Answer 1

¿Tienes que importar todas las columnas? Tal vez quieras omitir las columnas vacías, si hay alguna; y también columnas que no son absolutamente necesarias para fines comerciales

Answer 2

Esta situación no es poco común cuando se trata de INSERT a granel en tablas vinculadas a ODBC en Access. En el caso de la siguiente consulta de Access

INSERT INTO METER_DATA (MPO_REFERENCE) 
SELECT MPO_REFERENCE FROM tblTempSmartSSP

donde [METER_DATA] es una tabla vinculada a ODBC y [tblTempSmartSSP] es una tabla de acceso local (nativa), ODBC está algo limitado en lo inteligente que puede ser porque tiene que ser capaz de acomodar una amplia gama de bases de datos de destino cuyas capacidades pueden variar muy. Desafortunadamente, a menudo significa que, a pesar de la sentencia única de Access SQL, lo que en realidad se envía a la base de datos remota (vinculada) es un INSERT por separado (o equivalente) para cada fila en la tabla local . Es comprensible que pueda ser muy lento si la tabla local contiene una gran cantidad de filas.

Opción 1: inserciones nativas a granel en la base de datos remota

Todas las bases de datos tienen uno o más mecanismos nativos para la carga masiva de datos: Microsoft SQL Server tiene "bcp" y BULK INSERT , y Oracle tiene "SQL * Loader". Estos mecanismos están optimizados para operaciones masivas y generalmente ofrecerán ventajas de velocidad significativas. De hecho, si los datos deben importarse en Access y "masajearse" antes de ser transferidos a la base de datos remota, puede ser más rápido volcar los datos modificados a un archivo de texto y luego importarlos a granel en la base de datos remota.

Opción 2: usar una consulta de paso a través en Access

Si los mecanismos de importación masiva no son una opción factible, entonces otra posibilidad es construir una o más consultas de paso a través en Access para cargar los datos usando instrucciones INSERT que pueden insertar más de una fila a la vez.

Por ejemplo, si la base de datos remota era SQL Server (2008 o posterior), entonces podríamos ejecutar una consulta de acceso de acceso (T-SQL) como esta

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

para insertar tres filas con una instrucción INSERT.

Según una respuesta a otra pregunta anterior aquí, la sintaxis correspondiente para Oracle sería

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;

Probé este enfoque con SQL Server (ya que no tengo acceso a una base de datos Oracle) usando una tabla nativa [tblTempSmartSSP] con 10,000 filas. El código ...

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

... demoré aproximadamente 100 segundos en ejecutar en mi entorno de prueba.

Por el contrario, el siguiente código, que crea INSERTs de varias filas como se describió anteriormente (usando lo que Microsoft llama un Constructor de valores de tabla ) ...

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

... tomó entre 1 y 2 segundos para producir los mismos resultados.

(Los constructores de valores de tabla T-SQL están limitados a insertar 1000 filas a la vez, por lo que el código anterior es un poco más complicado de lo que sería de otra manera).

Answer 3

Lo siento, olvidé incluir el código:

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