Microsoft Access Team Blog

I was once brought in on a project to update an antiquated Access POS system used by a chain of 150 shops nationwide.

One of the main requirements was to fix a massive delay in printing the customer receipts.  I say 'massive' - that was the cashier’s words, but to me it seemed only maybe 10 seconds per receipt (a few seconds before printing, and a few seconds after).  It wasn't so much the delay that caused a headache for them, but it was that the application was locked up and unusable whilst waiting for the process to finish.  So after each sale the next cashier would have to wait a minimum of 10 seconds before being able to start a new sale.  Imagine that at a busy Christmas checkout.

I was most surprised to hear that the bug had existed for at least 7 years prior and the users complained constantly over that time, yet the in-house developer shrugged it off as being a waste of his time trying to fix it.

As soon as I came in on the project I fixed this on day one.  It turned out the developer was using some proprietary API that came with the printer, which was using synchronous Windows API calls to talk to the printer and causing the delays.  I changed to sending the raw printer commands asynchronously to the Windows buffer directly in VBA.  Bingo - the delay disappeared to zero seconds.

I was invited to speak about the new version of the POS software at the next shop managers conference, where I demoed the new features.  The _only_ feature they were interested in was the fix for the receipt delay - and for that I surprisingly got a standing ovation...

Sometimes, you've just got to listen to your end-users.

Wayne,

Was to in-house developer fired?  He/she should have been.  If nothing else, he/she should have been afraid for their life every moment.

Several projects where performance was an issue.  Some created by others, and a few of my own fault.  In the end the strategy is same.  Isolate the issue, don't assume it's something like network bandwidth or speed. And even if it is, you've got to find ways to creatively resolve the performance headache for the user even if your application isn't the root cause.

A few tips:

a)  Debug logging.  You create a generic logging function call that has a switch for debug and normal.  Then in the parameters of the function call, indicate whether the event is "normal" or "debug".  If the function's switch is set to "normal" and the incoming event is "debug," you just end the function.  If the parameter for "debug" is on, you log the event (either do a Debug.Print or actually write a log table record) with a timestamp.  Then introduce your logging function call throughout your code where you see the possibility for trouble (performance or otherwise).  Then at any time you can turn on debug logging, and watch each step of the processing and how long it takes.  If you do it in a table, you've got the extra advantage of being able to remotely debug as long as you can get your hands on the table.

b)  Minimize I/O.  Make your recordsets read-only/forward where possible.  If you've got a somewhat large data set on a file server you need to crunch, either put it on SQL Server, or bring down a subset of the data, process locally, then upload the results.

c)  Manage user expectations.  Progress bars or some sort of processing indication (beyond the hourglass mouse icon) can go a long way.  If you can provide an estimated time to process, that's even better.  

d)  An oldy but goody, if you've got a back end database on a file server, open a persistent recordset to the back end when your database opens.  Otherwise Access spends a bunch of time opening the back end database, closing it, checking for user conflicts, etc.  The back end mdb/ldb stay open until you close the persistent recordset or your database.

I had a set of users that swore processing took 10 hours and they left at night.  I checked the logging table and found the request took about 8 minutes.  Far longer than it should have, but definitely not 10 hours.  Taking all the steps above shrunk the processing down to 20 seconds, and they raved about the improvements for days.

Recenltly i need to fix/update one of my apps, it has work fine since 2002, but by some reason for this big customer, the code was taking more than reasonable to finish. . .

So, i just changed from DAO to ADO in some parts, other parts i left DAO, and it reduced the process for more than 50% . . .

What a great story. . .

My customer, almost kissed me!

One of the things I've found recently to speed up my applications is to create timed functions for any regular data you need.

If the data does not change on a regular basis, but is related to a query off a backend table, SQL DB, or Sharepoint list, it's often better to use static timers to keep the data in memory.

The following Example function allows a check only every 20 minutes to get the string "Hello World" from a DAO Recordset:

Function ExampleFunction() As String

 Static LastTimeChecked as Date

 Static ReturnString As String

 'Check to see if this was done less than 20 minutes ago

 If Now() >= DateAdd("m", 20, LastTimeChecked) Then

   'Perform all of your functions' tasks

   Dim rst As DAO.Recordset

   rst = CurrentDB.OpenRecordset("SELECT ""Hello World"" As RetStr")

   If Not (rst.EOF or rst.BOF) Then

      ReturnString = rst!RetStr

   End If

   rst.Close

   Set rst = Nothing

   LastTimeChecked = Now()

 End If

 ExampleFunction = ReturnString

End Function

whoops...  bad syntax in my example

"rst =" should be "Set rst =", though I hope most of us caught that :)