Performance issues (1 Viewer)

[BeeJayEff]

Background : Access 2013, database split into FE and BE, maximum of 8 users, whose desktop specifications vary from one to another.

There are significant discrepancies between the performance obtained on the various client machines.

I am using a particular user function as a benchmark. This is a significant task, involving copying a number of records to the clipboard. On two clients this is accomplished in 5 seconds (260 records). The others all take between 45 and 55 seconds for the same recordset if running their local FE, but only 5 seconds if using the FE on the server.
The two faster clients are faster for all database tasks.

At least one of the slower clients has a nominally better PC specification than the two "fast" ones in terms of processor speed and RAM. The network configurations all look to be the same.
So two questions :
1) where should I look to find out why some clients take an order of magnitude longer when running the FE locally than when running it on the server, when things are supposed to be the other way round ?
2) what can cause such a huge difference between machines when running local FEs ?

 

[gemma-the-husky]

are you conducting these tests with only a single use active, or are there multiple users concurrently active?

 

[BeeJayEff]

Possibly other users with the database open, but not doing anything with it. I don't think there's any correlation there.

 

[gemma-the-husky]

if you have multiple users connected, then it may be a persistent connection issue. Connecting to a backend with multiple users can involve significant delays in negotiating a connection through the OS. Maintaining a permanent (persistent) connection avoids this.

not sure about copying to the clipboard. you never know, though. Access needs to move chunks of data around. maybe it needs to move the whole file from the server to the PC, in order to do the copy test - which might be a longer process than what it does when it is all on the server

I have some code to store process timing when I am investigating bottlenecks, so I can see exactly which bit is causing delays.

 

[BeeJayEff]

Thanks - food for thought there. I'll try the persistent connection route. I only chose the copying to clipboard as it's a significant task with no user involvement; the same machines exhibit the same poor performance for other tasks.

Could you elaborate on your process timing code - what facilities/functions have you found useful in the past ?

 

[jdraw]

See the tips offered here and here and here. Also see this from Tom Wickerath.

Often, performance on LAN is the culprit rather than Access per se. If the FE is consistent across all machines, then it's partly the local hardware(processor, memory.....) but likely more the network.

 

[gemma-the-husky]

timing - simply variations of this. gettickcount returns the elapsed milliseconds on the processor. you can use repeated calls to this to determine process times, and therefore identify specific bottlenecks

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

dim mytimers(100) as long

sub process
mytimers(1)=gettickcount
do stuff
mytimers(2) = gettickcount
msgbox("elapsed time = " & (mytimers(2)-mytimers(1))/1000 & " seconds")
end sub

 

[BeeJayEff]

Thanks Dave, I'll use that approach when I'm looking for performance issues common to all clients

See the tips offered here and here and here. Also see this from Tom Wickerath.

Often, performance on LAN is the culprit rather than Access per se. If the FE is consistent across all machines, then it's partly the local hardware(processor, memory.....) but likely more the network.

Exactly, that's why I asked in another thread for specific things to look at with the hardware, rather than within Access. Your third link - here - identifies some things to look at; thanks.

 

[gemma-the-husky]

I would be very careful about changing some of the settings described in the link

record locking - no
automatic c &R - no
open exclusive - wouldn't bother
auto correct - design feature, can be useful
network refresh - wouldn't change

if a dbs does not run well, it is much more likely to be a design issue, I think.
normalisation, indexing, efficient coding.

 

[zooropa67]

I had the same problem on my split database with 10 users. Problem fixed by keeping connection persistent. Some forms would take over a minute to open or close and now that happens almost instantly.