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

Я нашел, что этот код онлайн запросил Доступ и ввел данные в Excel (2003), но это намного медленнее, чем это должно быть:

Sub DataPull(SQLQuery, CellPaste)
Dim Con As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim DBlocation As String, DBName As String
Dim ContractingQuery As String

If SQLQuery = "" Then

Else
    DBName = Range("DBName")
    If Right(DBName, 4) <> ".mdb" Then DBName = DBName + ".mdb"

    DBlocation = ActiveWorkbook.Path
    If Right(DBlocation, 1) <> "\" Then DBlocation = DBlocation + "\"

    Con.ConnectionString = DBlocation + DBName
    Con.Provider = "Microsoft.Jet.OLEDB.4.0"
    Con.Open

    Set RST = Con.Execute(SQLQuery)
    Range(CellPaste).CopyFromRecordset RST

    Con.Close
End If

End Sub

Проблема состоит в том, что этот код берет очень долго. Если я открываю Доступ и просто выполняю запрос туда, он сопровождает 1/10-й время. Там должен так или иначе ускорить это? Или какая-либо причина это могло бы занимать много времени? Все мои запросы являются простыми запросами Select с простым где операторы и никакие соединения. Даже a select * from [test] запрос берет намного дольше, чем он должен.

Править: Я должен указать что строка

Range(CellPaste).CopyFromRecordset RST

был занимающий много времени тот.

6
задан Teamothy 3 October 2019 в 13:16
поделиться

8 ответов

Lots of formulas may reference the query. Try temporarially turning on manual calculate in the macro and turning it off when all of your queries are done updating.

This should speed it up a bit, but still doesn't fix the underlying problem.

1
ответ дан 10 December 2019 в 02:50
поделиться

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

Set RST = Con.Execute(SQLQuery)

Я

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

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

3
ответ дан 10 December 2019 в 02:50
поделиться

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

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

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

1
ответ дан 10 December 2019 в 02:50
поделиться

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

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

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

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

0
ответ дан 10 December 2019 в 02:50
поделиться

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

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

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

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

1
ответ дан 10 December 2019 в 02:50
поделиться

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

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

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

1
ответ дан 10 December 2019 в 02:50
поделиться

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

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

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

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

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

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

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

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

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

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

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

2
ответ дан 10 December 2019 в 02:50
поделиться
Другие вопросы по тегам:

Похожие вопросы: