Страницы: 1
RSS
ускорение получения данных из SQL, загрузка объёмных данных
 
Добрый день.
Уважаемые знатоки, прошу вашей помощи.
Сейчас для получения данных из БД использую следующий код:
Код
While Not rs.EOF
        For iCols = 0 To rs.Fields.Count - 1
            Sheets("Table").Cells(iRows, iCols + 1).Value = rs.Fields(iCols).Value
        Next
    rs.MoveNext
    iRows = iRows + 1
Wend

Время загрузки при этом 3-4 мин.

Как можно ускорить процесс?

Мои предположения такие:
1. поставить клиента и в фоновом режиме синхронить постоянно.
2. оптимизировать код.

Можете подсказать варианты решения задач?

 
Доброе время суток
Цитата
stasdi написал:
Можете подсказать варианты решения задач?
Конечно, он даже документирован Microsoft Range.CopyFromRecordset
 
Спасибо.
Я попробовал использовать Range.CopyFromRecordset, но времени этот метод занял столько же.
 
Да не может такого быть. Но конечно если сперва в цикле по ячейкам их заполнять значениями и зачем-то наводить Болд как в примере - тогда общее время ещё и дольше может быть... :)
Ещё может быть что после выгрузки рекордсета каждое значение обсчитывается формулами - это тоже может занять свой немалый процент времени, но это не связано собственно с выгрузкой.
 
stasdi,  Все что было ранее написано, тоже валидно, но добавлю от себя
1. что за сервер, что за объемы?
2. не выводя данные попробуйте замерить время выполнения rs.RecordsCount и rs.MoveLast

чисто по жизни For iCols = 0 To rs.Fields.Count - 1  0  rs.Fields.Count - 1 можно один раз перед основным циклом в переменную положить, а еще лучше запись в массив и его разом в строк, но это чуть ускорит,а раз Range.CopyFromRecordset также задумчив, то смотрите вопросы выше.
По вопросам из тем форума, личку не читаю.
 

Вот весь код:

Код
Sub DB_Read() ' читаем таблицу ТО
Call OpenConnect_db_User
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM TableTO", cn
' Скопировать набор записей на лист, начиная с ячейки A2
FN.Sheets("Table").Cells(2, 1).CopyFromRecordset rs
Call CleseConnect_db
End Sub
Изменено: stasdi - 11.09.2019 11:06:54 (не корректно отобразился код)
 
Цитата
БМВ написал: что за сервер...t
Сервер MS SQL. Таблица размером 1300 строк 88 столбцов. (текст)

Цитата
...время выполнения rs.RecordsCount и rs.MoveLast
3-4 сек.
 
Тогда смотрите что завязано на это изменений, калькуляция, прочие события,  о чем в №4 писалось. Другого предположить сложно , хотя 3-4 секунды для этого объема - тоже многовато.
По вопросам из тем форума, личку не читаю.
 
Цитата
stasdi написал:
Время загрузки при этом 3-4 мин.
Цитата
stasdi написал:
2. 3-4 сек.
Разрешите по интерсоваться, где истина?
Вот горшок пустой, он предмет простой...
 
:)
Цитата
PooHkrd написал: где истина?
In vino veritas
 
Цитата
PooHkrd написал:
Разрешите по интерсоваться, где истина?
ну разные ж операции rs.RecordsCount и выгрузка на лист.
По вопросам из тем форума, личку не читаю.
 
БМВ, не уверен что ТС так уж расстраивает получение массива за 3-4 секунды. Лично я бы так не парился.
stasdi, я в энтих VBA ни ухом ни рылом, выгрузку производите в умную таблицу или прямиком на лист?
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
прямиком на лист?
на лист.
 
Выполните макрос из #6 с записью данных в новую книгу
Код
Sub DB_Read() ' читаем таблицу ТО
Call OpenConnect_db_User
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM TableTO", cn
' Скопировать набор записей на лист, начиная с ячейки A2
Workbooks.Add
Range("A2").CopyFromRecordset rs
Call CleseConnect_db
End Sub
Каково время?
Владимир
 
3,5 мин
Изменено: stasdi - 12.09.2019 08:03:17
 
Бред какой -то. RS сфорсированный быстро долго выгружается. Можете показать пару строк результирующей таблички? Попробуйте не все поля, а несколько в запросе указать.
Изменено: БМВ - 12.09.2019 14:35:58
По вопросам из тем форума, личку не читаю.
 
Померьте время работы каждого шага - хоть выясните где конкретно тормозит.
 
Попробуем выбрать данные в массив.
Код
Sub DB_Read() ' читаем таблицу
Dim arr, t As Double
Call OpenConnect_db_User
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM TableTO", cn
' Копируем Recordset в массив
t = Timer
arr = rs.GetRows
Debug.Print "Время выборки данных в массив " & (Timer - t)
Call CleseConnect_db
End Sub
Каково время выполнения?
Владимир
 
Цитата
sokol92 написал:
Каково время выполнения?
Прошу прощения за долгий ответ
Изменено: stasdi - 13.09.2019 12:35:03
 
Добрый день! В тесте #19 Объекты Excel не задействованы, только VBA. Поскольку план выполнения запроса из #19 не может быть "плохим", то остается версия экстремально большого объема передаваемых данных. Проверяем:

Код
Sub DB_Read() ' читаем таблицу
Dim arr, v, t As Double, n As Double
Call OpenConnect_db_User
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM TableTO", cn
' Копируем Recordset в массив
t = Timer
arr = rs.GetRows
Debug.Print "Время выборки данных в массив " & (Timer - t)

For Each v In arr
  n = n + Len(v)
Next v
Debug.Print "Объем полученной информации " & n
Call CleseConnect_db
End Sub
Владимир
 
Время выборки данных в массив 143,8125
Объем полученной информации 689690
 
Не типовая ситуация. Для таких объемов на современном железе время выборки должно быть не более секунды. Далее можно только гадать: нехватка аппаратных ресурсов...
Владимир
 
Спасибо, буду долбить IT
 
Цитата
stasdi написал:
буду долбить IT
Кто кого будет долбить, это еще вопрос. ;)
Скрытый текст

Проверьте скорость сетки.
Вот горшок пустой, он предмет простой...
 
Цитата
stasdi написал:
буду долбить IT
Ой не советую :-)
По вопросам из тем форума, личку не читаю.
 
Добрый день.
Протестировал в разных сетях. Результат:
На оптоволокне: 7сек. [прямое подключение]
На смартфоне (в режиме модема): 15сек.  (провайдер МТС) [VPN через шлюз]
На роуторе-модеме 3-4мин. (провайдер МТС) [VPN через шлюз]

Всем спасибо за помощь.
 
stasdi, ага, мой хрустальный шар был прав!  :D
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх