Data Macro - Strange Performance Issue

[mellamokb]

Can you provide a repo accdb so we can try to investigate? It's hard to noodle out possible problems in the abstract.

GPGeorge - I apologize as I'm new to the forum so I'm trying my best to communicate, thanks for taking a look at this. On my previous reply from yesterday at 4:40 PM you should see an attached ZIP file with a test database once you unzip. This database provides repro if you do these two experiments.

1. Open the Immediate Window, and type "ReorderEe" and run - it will spit out a Timing of running the update loop, which on my dev machine is around 8 ms.
2. Then open the FrmTest screen, which has a button called "Command18". Click this button to run the same ReorderEe function. If you check Immediate window, you'll see a new timing line, which for me usually says something like 250 ms.

The button Command18 simply calls ReorderEe - so in principle calling the function from the immediate window, and calling it from a click event should be identical. But it seems that if a table is bound to an object on the form (the ListBox) and at the same time has a Data Macro, it creates some sort of constant refresh that slows down the process of updating records considerably. What's even more interesting is you can even have the FrmTest open while calling ReorderEe from the immediate window, and you'll still get the fast timing above - the difference seems to be purely whether the form was the source of the event or not.

Thanks.

 

[jdraw]

Is there a reason to have the listbox ---could you not just have the button?

 

[GPGeorge]

GPGeorge - I apologize as I'm new to the forum so I'm trying my best to communicate, thanks for taking a look at this. On my previous reply from yesterday at 4:40 PM you should see an attached ZIP file with a test database once you unzip. This database provides repro if you do these two experiments.

1. Open the Immediate Window, and type "ReorderEe" and run - it will spit out a Timing of running the update loop, which on my dev machine is around 8 ms.
2. Then open the FrmTest screen, which has a button called "Command18". Click this button to run the same ReorderEe function. If you check Immediate window, you'll see a new timing line, which for me usually says something like 250 ms.

The button Command18 simply calls ReorderEe - so in principle calling the function from the immediate window, and calling it from a click event should be identical. But it seems that if a table is bound to an object on the form (the ListBox) and at the same time has a Data Macro, it creates some sort of constant refresh that slows down the process of updating records considerably. What's even more interesting is you can even have the FrmTest open while calling ReorderEe from the immediate window, and you'll still get the fast timing above - the difference seems to be purely whether the form was the source of the event or not.

Thanks.

I missed that. I have a bad habit of not re-reading every post in a longer discussion.

 

[GPGeorge]

Okay, sorry about that.

I think, from a few trial runs, that what you are probably experiencing is that the listbox is updating itself as each of the records in its recordsource is being updated by the VBA procedure. That suggests it's primarily down to interface overhead, rather than the recordset itself.

That's very much in line with what others have previously suggested.

 

[mellamokb]

Is there a reason to have the listbox ---could you not just have the button?

The listbox is simulating the behavior of the real application, where we have a listbox on the side bar of the employee screen for selecting the current active employee. I was trying to construct the minimum repro.

 

[mellamokb]

Okay, sorry about that.

I think, from a few trial runs, that what you are probably experiencing is that the listbox is updating itself as each of the records in its recordsource is being updated by the VBA procedure. That suggests it's primarily down to interface overhead, rather than the recordset itself.

That's very much in line with what others have previously suggested.

Yeah, that's the conclusion I'm coming to as well. What's annoying is that the listbox doesn't have this behavior without the Data Macro. You can happily do a recordset loop and update every record in the listbox with no performance hit. The problem seems to only manifest in combination of a bound form element + an active Before Change Data Macro. I suspect Access' treatment of Data Macro side effects is unsophisticated, so the presence of a Data Macro causes overkill refresh semantics just in case.

 

[isladogs]

Did you see my question about trusted location vs trusted document? That could well explain the 20x speed difference as it did in my tests on another issue.

 

[GPGeorge]

Yeah, that's the conclusion I'm coming to as well. What's annoying is that the listbox doesn't have this behavior without the Data Macro. You can happily do a recordset loop and update every record in the listbox with no performance hit. The problem seems to only manifest in combination of a bound form element + an active Before Change Data Macro. I suspect Access' treatment of Data Macro side effects is unsophisticated, so the presence of a Data Macro causes overkill refresh semantics just in case.

That's pretty much how I see it. Each time a record gets updated by the DM, the recordset itself changes, so the listbox refreshes itself. Maybe Access has to validate whether there are changes to any of the records to do that? How that differs from a direct update in a recordset loop I don't know.

 

[mellamokb]

Did you see my question about trusted location vs trusted document? That could well explain the 20x speed difference as it did in my tests on another issue.

Hmm. I added the folder with my Test database as Trusted Locations and the behavior seems similar. I suspect because I'm comparing performance between two different function in the same database, not two different databases.

 

[isladogs]

My speed tests were also on the same code in the same database - see my linked article

 

[arnelgp]

do you always need to run the "ReorderEe" function everytime you add new record?
you can delay this process when you close the form and only then you call the sub/function
to re-order your "ListOrder" field (when done on the Close Event of the form, it takes 97-105 ms,
much less when done using the command button.

 

[mellamokb]

do you always need to run the "ReorderEe" function everytime you add new record?
you can delay this process when you close the form and only then you call the sub/function
to re-order your "ListOrder" field (when done on the Close Event of the form, it takes 97-105 ms,
much less when done using the command button.

Yeah, that is a good suggestion. When the user is done working with employee management it could probably do the reorder once afterward.

The general problem is I know there are numerous places in our production application where recordset loops are used to batch process and I was hoping to avoid having to tweak all of them to make a DM solution work - but it seems unavoidable at this point.

Thanks all!