VBA: Запросы Доступа с Excel. Почему настолько медленный?

Я не эксперт, но я запускаю почти такой же код с хорошими результатами. Одно отличие состоит в том, что я использую объект Command , а также объект Connection . Где вы

Set RST = Con.Execute(SQLQuery)

Я

Dim cmd As ADODB.Command
Set cmd.ActiveConnection = con
cmd.CommandText = SQLQuery
Set RST = cmd.Execute

Я не знаю, может ли это помочь и почему, но, может быть, поможет? : -)

Я использовал ваш код и вытащил таблицу из 38 столбцов и 63780 строк менее чем за 7 секунд - примерно то, что я ожидал - и меньшие наборы записей завершились почти мгновенно.

Это какую производительность вы испытываете? Если так, то это согласуется с тем, что я ожидал от ADO-соединения Excel с серверной частью MDB.

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

Если вы извлекаете много записей, это объясняет, почему Range (CellPaste) занимает так много времени. (Если вы выполните запрос в Access, он не получит все записи, но если вы выполните CopyFromRecordset, он потребует все записи.)

Для CopyFromRecordset есть параметр MaxRows:

Public Function CopyFromRecordset ( _
    Data As Object, _
    <OptionalAttribute> MaxRows As Object, _
    <OptionalAttribute> MaxColumns As Object _
) As Integer

Попробуйте установить для него значение низкое значение (примерно 10) изменяет производительность.

Я бы порекомендовал вам создать Recordset явно, а не неявно, используя Выполнить метод . При явном создании вы можете установить его свойства CursorType и LockType, которые влияют на производительность.

Как я вижу, вы загружаете данные в Excel, а затем закрываете набор записей. Вам не нужно обновлять, подсчитывать записи и т. Д. Поэтому я бы посоветовал создать Recordset с CursorType = adOpenForwardOnly & LockType = adLockReadOnly :

...
RST.Open SQLQuery, Con, adOpenForwardOnly, adLockReadOnly
Range(CellPaste).CopyFromRecordset RST
...

Объект набора записей (ADO)

Поскольку вы используете Access 2003, используйте вместо этого DAO, это будет быстрее с движком Jet.

См. http://www.erlandsendata.no/english/ index.php? d = envbadacexportdao для примера кода.

Обратите внимание, что вы никогда не должны использовать ключевое слово «As New», так как это приведет к неожиданным результатам.

А как насчет следующих изменений или улучшений:

1. После открытия сохраните набор записей как файл xml (rst.saveToFile xxx), а затем попросите Excel снова открыть его.
2. После открытия поместите данные набора записей в массиве (rst.getRows xxx) и скопируйте массив на активный лист
3. И в любое время минимизируйте все требования к памяти / доступу: откройте набор записей как доступный только для чтения, только для пересылки, закройте соединение когда данные находятся на вашей стороне, и т. д.

Я не думаю, что вы сравниваете подобное с подобным.

В Access, когда вы просматриваете представление данных запроса, происходит следующее:

• используется существующее открытое соединение (и остается открытым);
• набор записей частично заполнен только с первыми несколькими строками (и остается открытым);
• частичный набор результатов показан в сетка, посвященная задаче и оптимизирован для нативного доступа к данным метод Access использует (прямое использование библиотеки DLL ядра СУБД Access, возможно).

В вашем коде VBA:

• открывается новое соединение (затем позже закрыт и выпущен);
• набор записей полностью заполнен используя все строки (затем закрытые и выпущен);
• весь набор результатов считывается в Общий пользовательский интерфейс Excel с использованием неродного компоненты доступа к данным.

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

Вторым по значимости будет время, затрачиваемое на чтение выбранных строк (при условии полного набора результатов) в элемент пользовательского интерфейса, а также тот факт, что Excel не оптимизирован для работы.

Открытие, закрытие и освобождение соединений и наборов записей должно быть незначительным, но все же является фактором.

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