# Excel VBA Filtering: AODB vs Autofilter vs SQLite

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!

• 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. – user109261 Jan 21 '18 at 16:19