Зацикленный .Refresh, вызывающий медленный Excel

Question

Я пытаюсь использовать VBA в Excel для Mac для запроса базы данных. Простой запрос отлично работает, и я использую его уже много лет. Теперь я перехожу к более сложным запросам, где результаты одного запроса (около 1000 записей) последовательно используются для повторного запроса к базе данных (то есть около 1000 последовательных запросов). Результаты (около 5000 записей) возвращаются примерно через 2,5 минуты, но щелчок на листе Excel не отвечает в течение примерно минуты после этого. Это поведение продолжается еще около 2 минут, прежде чем щелчок становится практически мгновенным. Повторный запуск макроса дает аналогичные результаты, но медленнее. Третий раз еще медленнее. Подозреваю утечку памяти. Перезапуск Excel приводит к сбросу проблемы. Вот код фактического запроса:

          With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
    End With

Я попытался отправить только первый запрос (чтобы он получил данные, из которых можно построить другие запросы). Остальным я строю запрос, но не отправляю его. Итак, в этом эксперименте приведенный выше код использовался только один раз. Запустив его таким образом, он возвращается примерно через 8 секунд. Таким образом, остальные 2 минуты и 20+ секунд проходят между моим компьютером и базой данных. Что еще более важно, после запуска таким образом нет задержки после завершения работы. Таким образом, похоже, если это утечка памяти, утечка происходит в процессе запроса или, возможно, в фактической записи данных.

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

Мой компьютер — Mac mini (конец 2014 г.) Intel Core i7 3 ГГц с Office 2011, но я пытался запустить его на более новом M1 с новейшей версией Excel. Это было намного быстрее, но отставание после возврата результатов, хотя и меньшее, все же было проблемой. Мой компьютер представляет собой место, где электронная таблица будет работать в ближайшем будущем.

Последующее отставание действительно убьет эту часть проекта. Кто-нибудь видел эту проблему раньше? Есть ли что-то, что я могу сделать, чтобы отследить причину проблемы и есть ли способ ее обойти?

Answer 1

ChatGPT решил суть моей проблемы. Он указал, что я создаю отдельную таблицу запросов для каждого из моих 1000+ запросов, и они поглощают мою память, что приводит к замедлению работы моего приложения. В нем говорилось, что если я добавлю строку .Delete после блока With... End With, она сотрет предыдущую таблицу запросов и не израсходует всю эту память. Ответ был немного неточным. .Удалить нужно было войти внутрь блока (в конце). Во всяком случае, я попробовал это, и мой код стал несколько медленнее, но когда это было сделано, приложение не стало медленным. Это победа в моей книге.

Кроме того, было высказано предположение, что ADO может лучше справиться с этой задачей. Так что я думаю, что это что-то еще, чтобы учиться. Думал, что это может быть полезно для кого-то еще, сталкивающегося с аналогичной проблемой.

другое редактирование: я работаю на Mac, поэтому ADO мне не поможет.

Вот рабочий запрос:

      With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
        .Delete
End With