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