Today I worked on optimizing a query for a client. it wasn’t super long, about 2 seconds, but that was enough to make a noticeable delay for the users running the database. I wanted to make it a little snappier.

It ended up boiling down to one thing, but first I had to test the query from the top down and start extracting pieces of it to find out where it was slow. In this case, the query had a base query that was running on a SQL server and joining that base query with outer joins to other local tables in my Access database.

I was able to determine that the base query by itself would take almost that full 2 seconds to run. So, was this the issue? Not exactly.

The base query was doing some major number crunching AND pulling all order line item records totalling about 33k total records. At a higher level, the final query was being filtered to only a handful of records, maybe max 20 with other orders only having a line or two.

At some point, I tested the base query which was set up as an Access Passthrough Query in an Access named query. If I put the condition in this query, it would run in less than a tenth a second! So this would definitely improve the system speed if I could get the query to do that FIRST and only THEN join the few remaining records from my local table. The join wasn’t a problem at all, just that initial giant scan of the whole database.

I initially tried to rewrite the local Access Query to change the declaration of the Passthrough Query as a subselect doing something like: (SELECT * FROM qryPassThrough WHERE order_id=1234)

This actually did not change the speed of the query at all. Access was still doing a full read of the qryPassthrough and then filtering it’s records. This did absolutely nothing to change the speed. However, if I added the condition to the SQL of the Passthrough query itself, this would speed it up.

What I ended up doing here was writing code to update the Passthrough query to contain the filter in it, then I could just run the regular query and it was already superfast. This worked well for me because I had code in only one spot that used this query to create a new table. It is not used anywhere else so I don’t have to worry about collisions of multiple places trying to use the code at different times.

Here is the code I had written to update the SQL for passthrough query realtime:

Private Sub SetOrderFor_qryRptOrder_Detail(order_id As Long)
    Dim qdf As QueryDef, dbCurrent As DAO.Database
    Set dbCurrent = CurrentDb
    Set qdf = dbCurrent.QueryDefs("rptOrder_Detail_base")
    qdf.SQL = Left(qdf.SQL, InStrRev(qdf.SQL, "order_id") - 1)
    qdf.SQL = qdf.SQL & "order_id=" & order_id
    Set qdf = Nothing
    Set dbCurrent = Nothing
End Sub

Hope you are having a great week so far! I’ll be back tomorrow.