VBA: запрос доступа с помощью Excel. Почему так медленно?

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

Set RST = Con.Execute(SQLQuery)

I

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Recordset Object (ADO)

Я использовал ваш код и вытащил таблицу из 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 или около того) изменит производительность.

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

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

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

Он извлекал записи молниеносно (‹5 секунд), но затем внезапно стал ужасно медленным (15 секунд для извлечения одной записи). Это то, что привело меня к вашему посту.

Я понял, что случайно открыл файл Excel (я редактировал его).

Как только я закрыл его, все снова стало молниеносно.

Проблема в 9 случаях из 10 связана с типом/местоположением курсора, который вы используете.

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

ЕСЛИ вы хотите очень быстро получать большие объемы данных, вам нужно использовать CursorLocation = adUseClient в вашем соединении. Это означает, что у вас будет только статический локальный курсор, поэтому вы не будете получать живые обновления от других пользователей.

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

Недавно я изменил это, так как у меня был простой цикл, заполняющий элемент списка, и каждый цикл занимал около 0,3 с. Не для замедления, но даже для 1000 записей это 30 секунд! Изменение только местоположения курсора позволяет завершить весь процесс менее чем за 1 секунду.