Как повысить производительность для массовых вставок в таблицы ODBC, связанные в Access?

Question

Как повысить производительность для массовых вставок в таблицы ODBC, связанные в Access?

У меня есть файлы CSV и TXT для импорта. Я импортирую файлы в Access, а затем вставляю записи в связанную таблицу Oracle. Каждый файл имеет около 3 миллионов строк, и процесс занимает много времени.

Импорт в Access очень быстрый, но вставка в связанную таблицу Oracle занимает очень много времени.

Вот процесс, который я сейчас использую:

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

tblTempSmartSSP таблица доступа и METER_DATA является связанной таблицей Oracle

Я также пробовал прямой импорт в связанную таблицу, и это тоже было очень медленно.

Как я могу ускорить процесс?

8

oracle ms-access access-vba odbc ms-access-2003

Источник

user4025886 16 сен '14 в 07:57

4 ответа

Решение

Другая возможность для SQL Server, еслиbcpилиBULK COPYне вариант:

METER_DATAпредставляет собой связанную таблицу ODBC в Access дляdbo.METER_DATAна SQL-сервере
tblTempSmartSSPэто локальная таблица Access, содержащая 10_000 строк

В моей (очень старой) тестовой сети этот код VBA выполняется примерно за 240 секунд (около 4 минут).

      Sub upload()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim t0 As Single
    t0 = Timer
    cdb.Execute "INSERT INTO METER_DATA (MPO_REFERENCE) SELECT MPO_REFERENCE FROM tblTempSmartSSP"
    Debug.Print Timer - t0
End Sub

Используя Python и pandas, этот код загружает те же 10_000 строк примерно за 5 секунд.

      from time import perf_counter

import pandas as pd
import sqlalchemy as sa

acc_engine = sa.create_engine(
    "access+pyodbc://@meter_data"
)
sql_engine = sa.create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql_199",
    fast_executemany=True,
)

t0 = perf_counter()
df = pd.read_sql_query("SELECT MPO_REFERENCE FROM tblTempSmartSSP", acc_engine)
df.to_sql("METER_DATA", sql_engine, schema="dbo", if_exists="append", index=False)
print(perf_counter() - t0)

Требования:

Питон
панды
sqlalchemy-access (Раскрытие информации: я являюсь сопровождающим этого проекта.)

Обратите внимание, что другие базы данных, поддерживаемые SQLAlchemy (например, PostgreSQL), также могут обеспечивать значительное повышение производительности по сравнению со связанной таблицей ODBC в Access.

1

Источник

user2144390 31 июл '22 в 21:56

Извините, я забыл включить код:

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

0

Источник

user2290801 16 сен '14 в 13:07

Вы должны импортировать все столбцы? Может быть, вы хотите оставить пустые столбцы, если таковые имеются; а также колонки, которые не являются абсолютно необходимыми для деловых целей

0

Источник

user2290801 16 сен '14 в 12:58

Другие вопросы по тегам oracle ms-access access-vba odbc ms-access-2003

user2144390 16 сен '14 в 22:14 2014-09-16 22:14 · Accepted Answer · 2014-09-16 22:14

Такая ситуация не редкость при работе с массивными INSERT для связанных таблиц ODBC в Access. В случае следующего запроса доступа

INSERT INTO METER_DATA (MPO_REFERENCE) 
SELECT MPO_REFERENCE FROM tblTempSmartSSP

где [METER_DATA] является связанной таблицей ODBC, а [tblTempSmartSSP] является локальной (собственной) таблицей доступа, ODBC несколько ограничен в том, насколько умным он может быть, поскольку он должен быть в состоянии разместить широкий спектр целевых баз данных, чьи возможности могут отличаться сильно. К сожалению, это часто означает, что, несмотря на один оператор SQL Access, то, что фактически отправляется в удаленную (связанную) базу данных, является отдельной INSERT (или эквивалентной) для каждой строки в локальной таблице. Понятно, что это может оказаться очень медленным, если локальная таблица содержит большое количество строк.

Вариант 1. Собственные массовые вставки в удаленную базу данных.

Все базы данных имеют один или несколько встроенных механизмов для массовой загрузки данных: Microsoft SQL Server имеет "bcp" и BULK INSERTи Oracle имеет "SQL*Loader". Эти механизмы оптимизированы для массовых операций и обычно дают значительные преимущества в скорости. Фактически, если данные необходимо импортировать в Access и "массировать" перед их передачей в удаленную базу данных, все равно может быть быстрее выгрузить измененные данные обратно в текстовый файл и затем массово импортировать их в удаленную базу данных.

Вариант 2. Использование сквозного запроса в Access

Если механизмы массового импорта не являются выполнимым вариантом, то другой возможностью является создание одного или нескольких сквозных запросов в Access для загрузки данных с помощью операторов INSERT, которые могут вставлять более одной строки за раз.

Например, если удаленной базой данных был SQL Server (2008 или более поздняя версия), то мы могли бы выполнить запрос сквозного доступа (T-SQL), например

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

вставить три строки одним оператором INSERT.

Согласно ответу на другой более ранний вопрос здесь соответствующий синтаксис для Oracle будет

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;

Я протестировал этот подход с SQL Server (поскольку у меня нет доступа к базе данных Oracle), используя собственную таблицу [tblTempSmartSSP] с 10000 строк. Код...

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

... потребовалось приблизительно 100 секунд для выполнения в моей тестовой среде.

В отличие от следующего кода, который создает многострочные INSERT, как описано выше (используя то, что Microsoft называет конструктором табличных значений)...

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

... потребовалось от 1 до 2 секунд, чтобы получить те же результаты.

(Конструкторы табличных значений T-SQL ограничены вставкой 1000 строк за раз, поэтому приведенный выше код немного сложнее, чем он был бы в противном случае.)