Excel VBA Filtering: AODB vs Autofilter vs SQLite

Question

I have an Excel sheet with data in about 100,000 rows, 16 columns. I have to extract data (one value from the 1 row I find) from this table a few thousand times. I have been using Autofilter to extract this value, but have also tried AODB/SQL statements to see if I can improve the speed of the queries.

The data is sorted by column 1. When I use Autofilter, it takes on average 0.3 seconds to get my value, no matter the location of the value. AODB takes between 0.1 to 0.6 seconds to find the value, depending on where the value is in the table (if the value is near the top of the table, it takes 0.1 seconds, while if the data is near the end of the table it can take up to 0.6 seconds). I had expected the AODB to be much faster than Autofilter, but it seems that on average they are about the same.

When I import this data into SQLite and use a tool such as SQLiteStudio, the query returns the value in less than 0.01s!

Can I change anything in my code to to speed up the Queries in VBA?

Codes are below. AODB method:

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

Autofilter method:

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

Some Notes:

• I am using Excel 2013. The Jet OLEDB provider seemed faster but I can't get the value to be saved in variable vv (I get an error).

• When Wind is -150 (Column1, -150 is near the top of the table), the AODB query runs in 0.1s, while a wind value of +150 takes 0.6 seconds.

Thanks for your help!

• user109261: I don't think that you can do much about the performance per call. However, you may be able to improve the overall time that it takes to complete the task. You could try querying the whole data-set and using the ADODB Recordset Filter.

Answer 1

For anyone reading, I ended up moving my spreadsheets to Microsoft Access, and used ADODB/SQL Queries from Excel to retrieve values from the Access database. This helped speed up my code by approx 10x, so definitely worth the move.