Как увеличить производительность для массивных ВСТАВКИ для связанных таблиц 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

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

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

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

 INSERT INTO METER_DATA (MPO_REFERENCE) SELECT MPO_REFERENCE FROM tblTempSmartSSP 

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

Вариант 1: Встроенные вставки в удаленной базе данных

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

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

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

Например, если удаленной базой данных был SQL Server (2008 или позже), мы могли бы запустить запрос доступа Access (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] с 10 000 строк. Код …

 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 строк за раз, поэтому приведенный выше код немного сложнее, чем в противном случае).

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

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

 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 
  • Логическое поле в Oracle
  • OdbcConnection возвращает китайские символы как «?»
  • Как вызвать функцию или процедуру Oracle с помощью Hibernate (EntityManager) или JPA 2
  • ORA-01882: область часового пояса не найдена
  • Разница между пользователем и схемой в Oracle?
  • oracle вставить, если строка не существует
  • OracleParameter и пункт IN
  • Формат строки URL для подключения к базе данных Oracle с JDBC
  • Имеет ли Oracle эквивалент табличных переменных SQL Server?
  • Как подключиться к базе данных Oracle 11. сеть
  • Какова минимальная настройка, требуемая для развертывания приложения .NET с клиентом Oracle 11?
  • Interesting Posts

    Пакетное изменение размеров изображений в OS X до определенной высоты

    Как использовать Simple HTTP-клиент в Android?

    «Защищенные приложения» на телефонах Huawei и способы их устранения

    Понимание «случайности»

    Что такое NetBIOS? Нужно ли Windows открыть порты 137 и 138?

    Вход в сеанс HTTP JSF

    Может ли web.config читать из внешнего XML-файла?

    как создать собственный счетчик, как рамка вокруг спиннера с треугольником вниз с правой стороны?

    Rails Routes – ограничение доступных форматов ресурса

    Msgstr “wait_fences: не удалось получить ответ: 10004003”?

    Как использовать кнопку отключения звука клавиатуры для отключения только динамиков, а не наушников?

    Есть ли способ принять только числовые значения в JTextField?

    Обновление Microsoft (MSU) не находит обновлений для других продуктов Microsoft (например, Office) после обновления MSU

    Размер разделения входов Hadoop и размер блока

    Преобразование строк в дату mm / dd / yy до YYYY-MM-DD в java

    Давайте будем гением компьютера.