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!

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.