Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / ADODB.Recordset медленно работает c SQL Server 2016 / 7 сообщений из 7, страница 1 из 1
07.11.2020, 15:26
    #40016219
SpsaceMan
SpsaceMan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ADODB.Recordset медленно работает c SQL Server 2016
Коллеги, нужна ваша помощь.
Не могу понять, почему 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, 22:37
    #40016257
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ADODB.Recordset медленно работает c SQL Server 2016
SpsaceMan,

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

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

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

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

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

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


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

ппц как медленно, это какого порядка сложность, что при 72000 ячеек целых 5 сек, по моему, это жопа я бы не дождался :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2020, 14:59
    #40016834
The_Prist
The_Prist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ADODB.Recordset медленно работает c SQL Server 2016
Вот правильно пишут же - вместо всего вот этого:
Код: 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
| Ответить | Цитировать | Написать  
12.11.2020, 11:24
    #40017454
big-duke
big-duke
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ADODB.Recordset медленно работает c SQL Server 2016
В плюс к вышесказанному

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
| Ответить | Цитировать | Написать  
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / ADODB.Recordset медленно работает c SQL Server 2016 / 7 сообщений из 7, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 399ms
Закрыть
start [/forum/topic.php?fid=60&mobile=1&tid=2154764]:
 0ms
get settings:
 11ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 33ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 272ms
total:  399ms
x
x
Закрыть


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