У меня есть лист Excel с данными примерно в 100 000 строк, 16 столбцов. Мне нужно извлечь данные (одно значение из 1 строки, которую я нахожу) из этой таблицы несколько тысяч раз. Я использовал автофильтр для извлечения этого значения, но также пробовал операторы AODB/SQL, чтобы посмотреть, могу ли я улучшить скорость запросов.
Данные сортируются по столбцу 1. Когда я использую автофильтр, получение моего значения занимает в среднем 0,3 секунды, независимо от местоположения значения. AODB требуется от 0,1 до 0,6 секунды, чтобы найти значение, в зависимости от того, где значение находится в таблице (если значение находится в верхней части таблицы, это занимает 0,1 секунды, а если данные находятся в конце таблицы, это занимает 0,1 секунды. может занять до 0,6 секунды). Я ожидал, что AODB будет намного быстрее, чем Autofilter, но похоже, что в среднем они примерно одинаковы.
Когда я импортирую эти данные в SQLite и использую такой инструмент, как SQLiteStudio, запрос возвращает значение менее чем за 0,01 с!
Могу ли я что-нибудь изменить в своем коде, чтобы ускорить запросы в VBA?
Коды ниже. Метод АОБД:
Sub AODB_method()
'Freeze Screen and other options to run code faster
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim vv As Double
Dim Time2 As Double
Time2 = Timer
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With ThisWorkbook
If Application.Version < 12 Then
cn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & .FullName & ";" & _
"Extended Properties=Excel 8.0"
Else
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & .FullName & ";" & _
"Extended Properties=Excel 8.0"
End If
cn.Open
Set rs = cn.Execute("SELECT [Cl] FROM [Table1$] WHERE [Wind]=150 AND [Weight]=200000 AND [Altitude] = 20000 AND [ISA] = 0")
vv = rs.Fields(0).Value
rs.Close
cn.Close
End With
'Turn Options on again
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
Debug.Print "Connection Time: " & Timer - Time2
End Sub
Метод автофильтра:
Sub Autofilter_method()
'Freeze Screen and other options to run code faster
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim vv As Double
Dim Time2 As Double
Time2 = Timer
With ThisWorkbook.Worksheets("Table1")
.AutoFilterMode = False
With .Range("A1:H1")
.AutoFilter
.AutoFilter Field:=4, Criteria1:=0
.AutoFilter Field:=2, Criteria1:=200000
.AutoFilter Field:=3, Criteria1:=20000
.AutoFilter Field:=1, Criteria1:=-150
End With
End With
vv = Range("H" & finallastrow("Table1", "H")).Value
'Turn Options on again
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
Debug.Print "Connection Time: "; Timer - Time2
End Sub
Некоторые примечания:
Я использую Excel 2013. Поставщик Jet OLEDB оказался быстрее, но я не могу сохранить значение в переменной vv (я получаю сообщение об ошибке).
Когда ветер равен -150 (столбец 1, -150 находится в верхней части таблицы), запрос AODB выполняется за 0,1 с, а значение ветра +150 занимает 0,6 секунды.
Спасибо за вашу помощь!