ReSQL.ru
     
powered by simpleCommunicator - 2.0.36     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / ADODB.Recordset медленно работает c SQL Server 2016
7 сообщений из 7, страница 1 из 1
ADODB.Recordset медленно работает c SQL Server 2016
    #40016219
SpsaceMan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SpsaceMan
Гость
Коллеги, нужна ваша помощь.
Не могу понять, почему Recordset отрабатывает очень медленно.

10 строк выводит за 5 секунд.
50 строк выводит за 25 секунд.
100 строк выводит за 60 секунд.
Надо вывести 4 000 строк. Окончания работы макроса так и не дождался.

Код: sql
1.
2.
SELECT @@VERSION
'Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64)   Dec 31 2019 22:39:35   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Home Single Language 10.0 <X64> (Build 18362: ) '



На работе почти такой же скрипт выводит несколько сот тысяч строк за 1-2 минут. т.е. дело в каких-то настройках?
На работе SQL Standard Edition 2014.

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
Sub Download_Order()

Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim ConnectionString As String

Dim i As Integer

Dim startTime As Variant
Dim endTime As Variant



Application.ScreenUpdating = False



'отсчет времени работы макроса
startTime = Time


   
'Удаляем старые данные на листе orders
    Sheets("orders").Activate
    Sheets("orders").Cells.Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Selection.ClearFormats



ConnectionString = "Provider = SQLOLEDB.1;Integrated Security=SSPI; Presist Security Info=False;Initial catalog=MAG_RAW;Data Source=ORION\SQLEXPRESS"
con.Open ConnectionString
    
    

    Sheets("orders").Cells(1, 1) = "Номер заказа"
    Sheets("orders").Cells(1, 2) = "Номер отправления"
    Sheets("orders").Cells(1, 3) = "Принят в обработку"
    Sheets("orders").Cells(1, 4) = "Дата отгрузки"
    Sheets("orders").Cells(1, 5) = "Статус"
    Sheets("orders").Cells(1, 6) = "Сумма отправления"
    Sheets("orders").Cells(1, 7) = "Наименование товара"
    Sheets("orders").Cells(1, 8) = "id"
    Sheets("orders").Cells(1, 9) = "Артикул"
    Sheets("orders").Cells(1, 10) = "Итоговая стоимость товара"
    Sheets("orders").Cells(1, 11) = "Количество"
    Sheets("orders").Cells(1, 12) = "Склад отгрузки"
    Sheets("orders").Cells(1, 13) = "Регион доставки"
    Sheets("orders").Cells(1, 14) = "Город доставки"
    Sheets("orders").Cells(1, 15) = "Способ доставки"
    Sheets("orders").Cells(1, 16) = "Сегмент клиента"
    Sheets("orders").Cells(1, 17) = "Способ оплаты"
    Sheets("orders").Cells(1, 18) = "Changed"



rec.Open ("SELECT * FROM [MAG_RAW].[dbo].[Order] WHERE Changed = (SELECT MAX(Changed) FROM [MAG_RAW].[dbo].[Order])"), con
i = 2
While Not rec.EOF
    Sheets("orders").Cells(i, 1) = rec.Fields("Номер заказа")
    Sheets("orders").Cells(i, 2) = rec.Fields("Номер отправления")
    Sheets("orders").Cells(i, 3) = rec.Fields("Принят в обработку")
    Sheets("orders").Cells(i, 4) = rec.Fields("Дата отгрузки")
    Sheets("orders").Cells(i, 5) = rec.Fields("Статус")
    Sheets("orders").Cells(i, 6) = rec.Fields("Сумма отправления")
    Sheets("orders").Cells(i, 7) = rec.Fields("Наименование товара")
    Sheets("orders").Cells(i, 8) = rec.Fields("id")
    Sheets("orders").Cells(i, 9) = rec.Fields("Артикул")
    Sheets("orders").Cells(i, 10) = rec.Fields("Итоговая стоимость товара")
    Sheets("orders").Cells(i, 11) = rec.Fields("Количество")
    Sheets("orders").Cells(i, 12) = rec.Fields("Склад отгрузки")
    Sheets("orders").Cells(i, 13) = rec.Fields("Регион доставки")
    Sheets("orders").Cells(i, 14) = rec.Fields("Город доставки")
    Sheets("orders").Cells(i, 15) = rec.Fields("Способ доставки")
    Sheets("orders").Cells(i, 16) = rec.Fields("Сегмент клиента")
    Sheets("orders").Cells(i, 17) = rec.Fields("Способ оплаты")
    Sheets("orders").Cells(i, 18) = rec.Fields("Changed")
    i = i + 1
    rec.MoveNext
Wend
rec.Close


Sheets("orders").Range("A2:A2").Select
        

endTime = Time

MsgBox vbTab & " " & "ГОТОВО !" & vbCrLf & vbCrLf & vbCrLf & "Работал (мин.): " & vbTab & Round(DateDiff("s", startTime, endTime) / 60, 0) & vbCrLf & "Работал (сек.): " & vbTab & DateDiff("s", startTime, endTime) & vbCrLf & "Начало: " & vbTab & vbTab & startTime & vbCrLf & "Конец: " & vbTab & vbTab & endTime
    
    
End Sub
...
Рейтинг: 0 / 0
07.11.2020, 15:26
| Ответить | Цитировать | Написать  
ADODB.Recordset медленно работает c SQL Server 2016
    #40016257
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
SpsaceMan,

Вариантов много:

1) Включен пересчёт листа.

Если на листе есть формулы, то после изменения ячеек, может быть долгий пересчёт. Рекомендую на время работы цикла установить ручной пересчёт листа и отключать перерисовку ячеек (что уже сделано, это хорошо, но обратно не включили).

Если данные переносятся без изменения на лист, можно попробовать применить метод CopyFromRecordset и отказаться от чтения данных в цикле

2) Медленная сеть. Пробуйте читать данные в переменные/массив без вывода на лист, тогда увидим скорость чтения из сети, исключая проблемы с отрисовкой
...
Рейтинг: 0 / 0
07.11.2020, 22:37
| Ответить | Цитировать | Написать  
ADODB.Recordset медленно работает c SQL Server 2016
    #40016268
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
Вместо вот всего этого нужно использовать CopyFromRecordset, это быстрее на несколько порядков
Доступ к каждой отдельной ячейке экселя - это ооооооочеь медленно
...
Рейтинг: 0 / 0
08.11.2020, 00:13
| Ответить | Цитировать | Написать  
ADODB.Recordset медленно работает c SQL Server 2016
    #40016270
SpsaceMan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SpsaceMan
Гость
VSVLAD, спасибо!

Все взлетело после:
Код: vbnet
1.
Application.Calculation = xlCalculationManual


4 000 строк за 5 секунд.
...
Рейтинг: 0 / 0
08.11.2020, 01:12
| Ответить | Цитировать | Написать  
ADODB.Recordset медленно работает c SQL Server 2016
    #40016783
Roman Mejtes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Roman Mejtes
Участник
SpsaceMan,

ппц как медленно, это какого порядка сложность, что при 72000 ячеек целых 5 сек, по моему, это жопа я бы не дождался :)
...
Рейтинг: 0 / 0
10.11.2020, 12:57
| Ответить | Цитировать | Написать  
ADODB.Recordset медленно работает c SQL Server 2016
    #40016834
Фотография The_Prist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
The_Prist
Участник
Вот правильно пишут же - вместо всего вот этого:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
i = 2
While Not rec.EOF
    Sheets("orders").Cells(i, 1) = rec.Fields("Номер заказа")
    Sheets("orders").Cells(i, 2) = rec.Fields("Номер отправления")
    Sheets("orders").Cells(i, 3) = rec.Fields("Принят в обработку")
    Sheets("orders").Cells(i, 4) = rec.Fields("Дата отгрузки")
    Sheets("orders").Cells(i, 5) = rec.Fields("Статус")
    Sheets("orders").Cells(i, 6) = rec.Fields("Сумма отправления")
    Sheets("orders").Cells(i, 7) = rec.Fields("Наименование товара")
    Sheets("orders").Cells(i, 8) = rec.Fields("id")
    Sheets("orders").Cells(i, 9) = rec.Fields("Артикул")
    Sheets("orders").Cells(i, 10) = rec.Fields("Итоговая стоимость товара")
    Sheets("orders").Cells(i, 11) = rec.Fields("Количество")
    Sheets("orders").Cells(i, 12) = rec.Fields("Склад отгрузки")
    Sheets("orders").Cells(i, 13) = rec.Fields("Регион доставки")
    Sheets("orders").Cells(i, 14) = rec.Fields("Город доставки")
    Sheets("orders").Cells(i, 15) = rec.Fields("Способ доставки")
    Sheets("orders").Cells(i, 16) = rec.Fields("Сегмент клиента")
    Sheets("orders").Cells(i, 17) = rec.Fields("Способ оплаты")
    Sheets("orders").Cells(i, 18) = rec.Fields("Changed")
    i = i + 1
    rec.MoveNext
Wend


проще и быстрее так:
Код: vbnet
1.
Sheets("orders").Cells(2, 1).CopyFromRecordset rec
...
Рейтинг: 0 / 0
10.11.2020, 14:59
| Ответить | Цитировать | Написать  
ADODB.Recordset медленно работает c SQL Server 2016
    #40017454
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-duke
Участник
В плюс к вышесказанному

Sheets("orders").Cells(1, 1) = "Номер заказа"
Sheets("orders").Cells(1, 2) = "Номер отправления"
Sheets("orders").Cells(1, 3) = "Принят в обработку"
Sheets("orders").Cells(1, 4) = "Дата отгрузки"
Sheets("orders").Cells(1, 5) = "Статус"
Sheets("orders").Cells(1, 6) = "Сумма отправления"
Sheets("orders").Cells(1, 7) = "Наименование товара"
Sheets("orders").Cells(1, 8) = "id"
Sheets("orders").Cells(1, 9) = "Артикул"
Sheets("orders").Cells(1, 10) = "Итоговая стоимость товара"
Sheets("orders").Cells(1, 11) = "Количество"
Sheets("orders").Cells(1, 12) = "Склад отгрузки"
Sheets("orders").Cells(1, 13) = "Регион доставки"
Sheets("orders").Cells(1, 14) = "Город доставки"
Sheets("orders").Cells(1, 15) = "Способ доставки"
Sheets("orders").Cells(1, 16) = "Сегмент клиента"
Sheets("orders").Cells(1, 17) = "Способ оплаты"
Sheets("orders").Cells(1, 18) = "Changed"

Можно попробовать заменить на
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
Dim Header, rngHeader As Range

Header = Array("Номер заказа", "Номер отправления", "Принят в обработку", "Дата отгрузки", "Статус", _
                "Сумма отправления", "Наименование товара", "id", "Артикул", "Итоговая стоимость товара", _
                "Количество", "Склад отгрузки", "Регион доставки", "Город доставки", "Способ доставки", _
                "Сегмент клиента", "Способ оплаты", "Changed")
Set rngHeader = Sheets("orders").Range("A1").Resize(1, UBound(Header) + 1)
                
rngHeader = Header




Использовать курсор брандспойт
Код: vbnet
1.
2.
3.
4.
5.
With rec
              .CursorLocation = adUseServer
              .CursorType = adOpenForwardOnly
              .LockType = adLockReadOnly
           End With
...
Рейтинг: 0 / 0
12.11.2020, 11:24
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / ADODB.Recordset медленно работает c SQL Server 2016
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=60&fpage=3&tid=2154764]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 26ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 44ms
get tp. blocked users:
 2ms
others: 265ms
total:  381ms
созд. / загр.: 381ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]