Odd performance issue with Access (1 Viewer)

[AlanC002]

Hi guys,

I'd appreciate your views on this odd thing...

I'm building a system for a transport firm, using MS Powerapps which integrates to an Access app in the office. The system uses a number of Sharepoint lists as the point of integration, using Office 365. The Access DB is not currently split during development but it will be.

In brief, the drivers can book on or off via the mobile app, they are given scheduled jobs for each day and can update with their progress as they arrive/depart each job. Back in the office, Access links the lists read/written by Powerapps and the info is written to local tables(s) so it can be displayed in near-real time to a number of agents, who can also pull up historical data.

Powerapps writes, for each of these updates, a record to a Sharepoint list called lstUpdates; marking the UpdateStatus field 'PENDING'.

The Access app in the office has a 'Dashboard' form that, every 30 seconds, polls the linked Sharepoint list with a SQL select query to see if there are any new updates i.e. any marked PENDING. If there are, it does whatever it needs to do such as writing them to a local table (LogEntries), and marks them as READ via an Update SQL query.

It all works absolutely fine except for one niggling issue!

Below I have reproduced the code from the Form_Timer event so you can see what is going on. You'll note that I am getting the system ticks from the Operating System (kernel32.dll) at the start and at the end, so that I can measure how long the whole poll and processing thing takes, and it writes that to debug.

Now here's the issue. When I first start Access and the app, the polling times come in at around 50-70mS. But after an hour, they have gradually increased to 300-400mS. After several hours, they are up in the seconds and the system is showing signs of stress e.g. unresponsive UI, endlessly twirling blue circle, forms disappearing from the screen, etc. Monitoring Access's memory usage, I can see that it starts at around 25MB but goes gradually up until after a couple of hours it's more than 100MB. And all this is under static conditions, i.e. no new updates coming in whatsoever- so only the select query on the SP list should be running every 30 seconds. The number of records in the SP List is static at about 12; there are only 40 records in the local table LogEntries.

What do you think is happening? I feel its some kind of resource leak; so in my code I am closing the recordset and database objects, is there anything else I should be doing? Are there better ways to access the data? Would it help do you think to isolate it to Access or Sharepoint by running a test using a local (Access) table for lstUpdates?

I'm not a huge Access programmer but I do a lot of work with other databases. I used to program VB6 many years ago so my VBA is fairly reasonable.

Private Sub Form_Timer()
    Dim t1 As Long
    Dim t2 As Long

    Dim strReadSQL As String
    Dim strWriteSQL As String
    
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim recordsAdded As Boolean
    
    'get updates from Sharepoint Lists and transfer to tables as appropriate
    t1 = GetTickCount()
    
    lblUpdates.Caption = "Checking for updates."
    recordsAdded = False
    
    strReadSQL = "select * from lstUpdates"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strReadSQL)
    
    If Not (rs.EOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            If rs!UpdateStatus = "PENDING" Then
                
                'get update type from the UpdateType field
                If rs!UpdateType = "BookOn" Then
                    'write to log entries
                    strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, LSTART,  KILOMETERS, TRUCK, TRAILER) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!DateTime & "', '" & rs!Kilometers & "', '" & rs!TRUCK & "', '" & rs!Trailer & "')"
                    db.Execute (strWriteSQL)
                    
                    'report to status label
                    lblUpdates.Caption = rs!Driver & " is booking on for " & rs!Journey & " with " & rs!TRUCK
                End If
                
                If rs!UpdateType = "BookOff" Then
                    'write to log entries
                    strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, FINISH, KILOMETERS, TRUCK, TRAILER) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!DateTime & "', '" & rs!Kilometers & "', '" & rs!TRUCK & "', '" & rs!Trailer & "')"
                    db.Execute (strWriteSQL)
                    
                    'report to status label
                    lblUpdates.Caption = rs!Driver & " is booking off."
                End If
                
                If rs!UpdateType = "Arrival" Then
                    'write to log entries
                    strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, COLLECT_DELIVER, ARR_TIME) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!UpdateText & "', '" & rs!DateTime & "')"
                    db.Execute (strWriteSQL)
                    
                    'report to status label
                    lblUpdates.Caption = rs!Driver & " has arrived at " & rs!UpdateText
                End If
                
                If rs!UpdateType = "Departure" Then
                    'write to log entries
                    strWriteSQL = "insert into LogEntries (LDATE, DRIVER, JOURNEY, COLLECT_DELIVER, DEP_TIME) values (NOW(), '" & rs!Driver & "', '" & rs!Journey & "', '" & rs!UpdateText & "', '" & rs!DateTime & "')"
                    db.Execute (strWriteSQL)
                    
                    'report to status label
                    lblUpdates.Caption = rs!Driver & " has departed " & rs!UpdateText
                End If
                
                'mark it read
                strWriteSQL = "update lstUpdates set UpdateStatus = 'READ' where id = " & rs!id
                db.Execute (strWriteSQL)
                recordsAdded = True
            
            Else
                'this one's already been done
                'Debug.Print ("Update is marked as read.")
            End If
            rs.MoveNext
        Loop

        If Not recordsAdded Then
            lblUpdates.Caption = "No new updates."
        End If
    Else
        lblUpdates.Caption = "No records found."
    End If

    'close and tidy up
    rs.Close
    db.Close
    
    t2 = GetTickCount()
    
    Debug.Print ("Time: " & CStr(t2 - t1) & "mS")
    
End Sub

 

[NauticalGent]

AlanC002,

I am by no means an expert but I may be able to offer two suggestions. After you have checked UpdateStatus for "Pending" you are forcing Access to step through and evaluate 4 other IF statements when a Select Case procedure would be more efficient. I doubt that the performance would improve much, it is just a practice I have picked up from this forum.

The second bit of advice I can offer regards SP. I use SP as a BE myself and I have two exceptionally large lists. One is (records) 37k+, the other is 62k+ and growing.

To speed things up, I do Server-Side filtering - a trick I learned from this site:
https://accessexperts.com/blog/2011/07/07/sharepoint-lists-and-microsoftaccess/

By letting SP do the filtering before I link the table, it reduces my RS from 37k to about 600. You could do the same with yours and only return those records that are pending.

Let me know how you sort this out, I am ALWAYS interested SP issues and with PowerApps becoming the next big "thing", this thread may become a significant contribution in the future.

 

[Minty]

Might be worth opening the first recordset as dbOpenSnapshot as you are only reading through it at that time, and not updating it?
Caveat - I've not used sharepoint, so may well be talking complete bobbins.

 

[arnelgp]

You have lots of processing going on on your timer event. You need to disable it first at the beginning and reinstate it at the end( before the End Sub). Also after each dB.Execute, put on the nextline DoEvents, so that your application can response to any pending system events.:

Private Sub Firm_Timer()
'kill the timer
Me.TimerInterval=0
'rest if your code below
...
...
dB.Execute ....
DoEvents
...
'another long process
dB.Execute.....
DoEvents
...
...
'at the end
Me.TimerInterval= 'put here the original timer value you have
End Sub

’''''''''''''
sorry only typing on my cp

 

[arnelgp]

I don't know about SharePoint, but in my experience on Fe/be setup where the be is on shared network folder, you have to maintain an open linked table on the Fe and make it open till you close the fe. This will make Ur app a little faster since a connection between fe/be has already been established.

 

[The_Doc_Man]

Concur with ArnelGP. I got better (and more reliable) performance when I forced a persistent open connection to the BE file.

 

[AlanC002]

Hi Guys,

Thank you very much for all of your replies. The fix was, as suggested by arnelgp, implementing a persistent connection to the BE by creating a form containing a view of the Sharepoint list, and running this form first. I'll simply hide it in the production app.

I also implemented all of the other changes suggested; stopping and re-starting the timer, adding DoEvents, using Select Case rather than lots of if-endif code blocks to process the poll response.

So now the app has been running for hours and the times are in the range 31 to 47mS depending presumably on what else is happening in the box, but not increasing at all.

It's interesting that in addition to fixing the resource leak issue, the execution time of that code block is around half of the fastest time achieved before. Similarly, the memory usage of Access is static at 18MB which is less than before.

Thanks again, I'm delighted.
Alan