Jet optimizer – used for VBA processing?

Topic

New Reply

Do “built on-the-fly” queries executed using VBA “db.execute sqlString” syntax use the Jet Optimizer, or do they examine every record in the underlying tables?

I’ve got a VBA process that uses a series of db.execute statements, and processing time has not been an issue with smaller databases. My current dataset has about 1.5 million and 0.7 million records in two underlying tables, and it is taking an absurdly long time to process. All the fields used in the queries are indexed. Would it benefit from writing Parameter Queries to replace the “built-on-the-fly” sql statements? I’ve never used Parameter Queries previously, so am not certain how to use them in my VBA code.

The backend is A2000 and the frontend is A2003.

Thanks in advance.

Reply | Quote

Replies

Might be exactly the opposite problem. Using the JETSHOWPLAN item in the Registry shows that the optimizer is being invoked every time the db.execute statement is run. Looks like it might be too many calls to the optimizer that is taking so much time.

Reply | Quote

Do “built on-the-fly” queries executed using VBA “db.execute sqlString” syntax use the Jet Optimizer, or do they examine every record in the underlying tables?

I’ve got a VBA process that uses a series of db.execute statements, and processing time has not been an issue with smaller databases. My current dataset has about 1.5 million and 0.7 million records in two underlying tables, and it is taking an absurdly long time to process. All the fields used in the queries are indexed. Would it benefit from writing Parameter Queries to replace the “built-on-the-fly” sql statements? I’ve never used Parameter Queries previously, so am not certain how to use them in my VBA code.

The backend is A2000 and the frontend is A2003.

Thanks in advance.

What is “an absurdly long time”? If you are sitting and watching the screen, anything over just a few seconds seems like a long time. And is there a chance it is proportionally longer than the smaller databases?

Personally, with tables that big, I’m starting to think SQL server backend.

Reply | Quote

It’s “wait till the end of the workday and set it up for overnight processing” absurd!
I’ll take your point about SQL Server under advisement, but this particular process is governed by a third-party application that uses MDB database. I have very limited experience with SQL server — perhaps exporting to SQL, doing this particular processing, then importing the results back to MDB might be faster.

Thanks for the input.

Reply | Quote

It’s “wait till the end of the workday and set it up for overnight processing” absurd!
I’ll take your point about SQL Server under advisement, but this particular process is governed by a third-party application that uses MDB database. I have very limited experience with SQL server — perhaps exporting to SQL, doing this particular processing, then importing the results back to MDB might be faster.

Thanks for the input.

Yeah, that’s absurdly long. BTW, are these Update queries, Append queries, or what?

One thing you might do is record the start/end times of each query you run during the process, to catch the likely situation in which 1 query is killing you.

Couple of other things to consider. Has the database been compacted recently? Those 2 big tables, are they basically permanent tables or ones that frequently get emptied and repopulated?

Reply | Quote

Mark
It is an update query. I’ve tried all sorts of stuff to speed it up, including exporting the critical data into new tables in a new, temporary MDB file so it is always compacted when the query is run. I am really starting to question the underlying logic of my query. Perhaps you would be willing to look at my logic.

The tables are generated automatically by data loggers installed on various heavy-duty mobile equipment. One table records GPS information and is typically characterized by a “TimeTag” field which records when the GPS point was acquired. A second table records a TimeLine — what the machine was doing at various times during the day. Each record is characterized by StartTime and StopTime fields. The two tables are not related in the original database, and that’s the purpose of this query — to populate the tables with the appropriate foreign key to the other table.

Here is the SQL of the query that is executed in a VBA loop that supplies the various parameters:

PARAMETERS CurrentMachineID Value, CurrentDayNo Value, CurrentZoneID Value;
UPDATE tmpTimelineSelected AS TL, tmpGPSPosSelected AS GPS SET GPS.ATIIDfk = tl.Recnum
WHERE (((GPS.TimeTag)>=[starttime] And (GPS.TimeTag)<[stoptime]) AND ((GPS.MachineID)=[CurrentMachineID]) AND tl.zoneID=[currentzoneid] and ((TL.MachineID)=[CurrentMachineID]) AND ((TL.DayNo)=[CurrentDayNo]));

The query updates the ATIIDfk field in the GPS table with the RecNum value from the appropriate record of the TimeLine table. It relates the two tables by finding the TimeLine interval that contains the TimeTag of each GPS record. The VBA loop cycles by day and by ZoneID to break the problem into more bite-sized pieces that provide visual feedback that the process has not stalled. All the fields are indexed, and I've used the JETSHOWPLAN routine to confirm that the indexes are being used.

The attached file shows the table structures and some sample data. The actual GPS table contains up to 250000 records and the TimeLine table contains up to 150000 records. Processing time is in the tens of hours.

Is there a better way to write the query? I would appreciate any feedback.

Reply | Quote

I find this more than a little confusing. As I see it, you are trying to find the unique record in the TimeLine table that corresponds to each record in the GPS table. Correct? But I don’t understand what the ZoneID and DayNo fields are, and how they factor in. In looking at the data, I don’t see any overlapping start/stop times.

What is wrong with this query:

UPDATE tmpTimelineSelected AS TL INNER JOIN tmpGPSPosSelected AS GPS ON TL.MachineID = GPS.MachineID SET GPS.ATIIDfk = tl.Recnum
WHERE GPS.ATIIDfk Is Not Null AND GPS.TimeTag>=[starttime] And GPS.TimeTag<[stoptime]

This will update every GPS record which has not yet been updated.

Reply | Quote

Sorry for the confusion. I forget how difficult it is to look at someone else’s work.

Yes, you are correct about the objective.

The ZoneID and DayNo fields are used to pare down the list to a more manageable size that processes more quickly. If all the records are processed at once, there is no feedback to the user that anything is happening. By running the query multiple times, at least there can be some on-screen confirmation provided by the VBA loop that the computer has not hung up. Through experimentation, I found that a tables with about 7500 records process at about 50-60 records per second, while the tables with 150000 records process at about 5 records per second. Huge difference by running the query multiple times on a subset of the data versus all of the data at once.

Other than the GPS.ATIIDfk Is Not Null clause that you added, your query is essentially the same as what I had in a previous version. Grasping at straws, I converted it from the INNER JOIN syntax to the WHERE syntax in case there was some weird interaction. However, both syntaxes work the same with the same processing speed.

I will try your query to see if it makes any difference, however, my similar query also took forever to run.

I really appreciate your looking at this.

Reply | Quote

G’Day,
We have had similar problems with updating issues taking forever in MDB files. We have had the luxury of being able to move tables permanently to an SQL Server, which in itself sped things up slightly. Our biggest performance increase came from converting the queries previously on the Access front-end to a Stored Procedure on the SQL Server. Then we just pass in any paramteres required and run the SP on the SQL server. In most cases we have seen routines that would take minutes (sometimes up to 30+ minutes) reduced to running in under a minute.

Not sure if you are able to that with your setup but you did mention maybe moving data from MDB to SQL to perform the updates so I assume you have a SQL Server running somewhere on the network that you could use. Of course, the issue doing this is the time it will take to move data to and from the MDB file. If you are going to proceed down this path, the code to call the SP and pass in parameter values would be similar to:

Code:

Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command

    cnn.Open "Provider=sqloledb;Data Source=;Initial Catalog=;Integrated Security=SSPI;"

    With cmd
        Set .ActiveConnection = cnn
       .CommandType = adCmdStoredProc
       .CommandText = ""
       .Parameters.Append .CreateParameter("@", adInteger, adParamInput, , )
    End With

    cmd.Execute
    cnn.Close
    
    Set cnn = Nothing
    Set cmd = Nothing

A rough estimate of creating the SP in SQL Server (I’m assuming your ID fields would be int types on the SQL Server):

Code:

CREATE PROCEDURE queryName
    @CurrentMachineID as int,
    @CurrentDayNo as int,
    @CurrentZoneID as int
AS
BEGIN
    UPDATE tmpTimelineSelected AS TL, tmpGPSPosSelected AS GPS 
    SET GPS.ATIIDfk = tl.Recnum
    WHERE (((GPS.TimeTag)>=[starttime] And (GPS.TimeTag)<[stoptime]) AND ((GPS.MachineID)=@CurrentMachineID) AND tl.zoneID=@CcurrentZoneID and ((TL.MachineID)=@CurrentMachineID) AND ((TL.DayNo)=@CurrentDayNo));
END
GO

Hope this helps.

Cheers

Reply | Quote

Back to this problem after putting it on the backburner for a while. Tried a number of different approaches, but nothing that I could devise using a conventional SQL and JOIN worked with acceptable performance. All were absurdly slow. So I resorted to stepping thru the one table with a DAO loop, setting some parameters in a parameter query, then executing the query to update the second table. It was contrary to my basic approach that an SQL approach was always better than brute-force DAO, but I was proven wrong. One process was ontrack to take multiple dozens of hours to complete. The DAO loop approach was completed in 20 minutes.

I also changed my tactic of building an SQL statement from scratch in every loop, and replaced it with the parameter query. Each time an SQL statement is executed, the Jet engine must compile it. By contrast, the parameter query is compiled once, then just executed within the loop. There is a significant reduction in processing time with the pre-defined query.

Anyway, the program works now with acceptable performance. Just thought I would wrap up this thread…

Reply | Quote

Thanks Jack – others should find that approach useful too.

Reply | Quote