[AccessD] Speed Issues on network just with Access App

Mon Nov 7 17:53:44 CST 2016

Firstly, you should be aware that I have , effectively as far as Access is
concerned, been retired for many years.
So my posts are from old experiences - 2003 version, with some 'home' work at
2010.

Consequently, I hope those with recent experience managing databases will either
confirm, or correct my understanding(s).  

So, my thinking goes along the lines :- 
Moving some data to a second MDB (or other database file) will mean that the
file will be faster to compact and to access
Also the amount of real memory required to hold the data for that file will be
reduced.
Considering that when the OS handling of the file - cache allocated for that I/O
buffering - then the OS will start paging the data out to storage 
So - data from the file to buffer cache, 
Then from cache to pagefile so that more data can be read into the cache.
Then - when all the required data for the query has been read from the mdb? File
- 
Start swapping it with that that was put onto the pagefile 

So - if a query needs more memory than is available - throughput will slow to
maybe 25% 

(My home PC system now has a MFT that is too large to fit into the available RAM
- and it can be faster to copy 5MB files to another device (over USB2 links) and
then bulk copy the files back to the new location, rather than to suffer the
pagefile thrashing involved in just renaming the files in the main storage
partition. And that is system overhead - not I/O allocated to any running
application!)

Now - if the DBMS is running queries for multiple front ends, the OS managing
the file will need much more memory 

Now - That is something to consider -  the .mdb? file size vs the available
memory and the number of processes requiring data from that server storage.
-------
Now re moving some data to a separate file - 
It may not have any other effect than to make compression faster -
I suspect the effect will depend to a large extent on the structure of the data
in that file - if data for each table is in contiguous blocks -
 then splitting the database may speed up initial access, it may actually slow
things down after that initial file load - 
Much depends on the amount of RAM wanted by the OS to handle the data requests,
and the amount available for the service processes.
Also, I suspect that more than a few insertions will cause fragmentation of the
tables data over the file space - as in new stuff at the end!

If that is happening then splitting off the archive data may help - but will
that be a daily - weekly, or monthly task - 
and the database files will probably need compacting for that to have any
beneficial effect.
If not the initiation, then certainly the checking it worked! And no users left
the system (locked?) open to their use.

Re data processing being done at the FE - maybe some views - or canned scripts
could be useful in reducing the data transferred to the FE.
But that would probably involve YOU doing a lot of WORK and ONGOING SUPPORT 

Yeuck!

JimB 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John
Bodin
Sent: Monday, November 7, 2016 8:11 PM
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Speed Issues on network just with Access App

Thanks Jim.  I had customer put an XP box back in play for someone that
constantly was having issues and I'll see how that goes over the next few days.
It may lean to the theory that a Windows Update(s) is causing the problem since
XP hasn't had any updates in over 2 years.

I will check the suggestions you have listed out, as best as I can.

One question you may or may not know and I hope I can explain it clearly (doubt
it).  I know that when you make a request for say All Salespeople working with
the company for more than 10 years, the entire set of tables referenced in that
query are brought from the database on the server to the local PC, where the FE
then filters out all the appropriate results.  Access is not a true
client/server product.  Given that, does it matter to the performance of the
database if you were to have a lot of tables in the BE database that only get
utilized every few weeks?  Would it be better to separate those out to a
separate database and link to them?  Meaning:

MDB1 has tables A, B, C & D.  Tables A, B & C are used all the time for input
and queries, etc.  Table D is an archive used for historical lookups which only
happens every few weeks.  Assuming each table is 25% of the total mdb size,
would moving Table D out to a new MDB2 and linking back to it provide any
benefit in performance for the every day work that happens on Tables A, B & C?
The database would in theory be 3/4 the size it was with table D removed.  I'm
thinking of how a code module on a local FE mdb would work, where if you had say
30 functions/procedures in ModuleA, but only a few were really used all the
time, calling one of those functions loads the entire module and can effect
performance.

Much appreciated.  John

John Bodin

sBOR Office Systems

jbodin at sbor.com<mailto:jbodin at sbor.com>

________________________________
From: AccessD <accessd-bounces at databaseadvisors.com> on behalf of James Button
<jamesbutton at blueyonder.co.uk>
Sent: Monday, November 7, 2016 11:39 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Speed Issues on network just with Access App

John,

Thanks for the reply -
Yes, does seem to be Access related rather than other processes -

You seem to have excluded be comms error handling, and most other processes.

Some possible sources of delays -
Server processes need more memory - is something else now using more of the
server memory
Database Locks within the facility
Index gone missing
Server level File locking ??
Some part of the processing has gone over a critical (to the memory management)
number of entries, or data file size
I have come across problems related to data keys being date based and the
randomising algorithm was grouping data causing locking problems, and then the
'fixed' version spreading data causing massively unexpected numbers of reads to
get the spread-out data.

Basically - all guesses at things to look at!

If you do identify a cause, please post the analysis !

JimB

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John
Bodin
Sent: Monday, November 7, 2016 1:45 AM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Speed Issues on network just with Access App

Darryl/Jim, thanks for replies and suggestions.  Here's the reason I believe the
problem is with the jet engine on the local pc's OR a Windows Update, and not
the network.

Everything else runs stellar on the network.  Internet Explorer/Chrome no
issues.  VOIP phone system, no problem.  Quickbooks w/6 simultaneous users, no
problem.  Opening files over network shares, no problem.  I was hoping I'd have
the slow network performance on word/excel files over the network as I have seen
that multiple times and have been able to change settings on the server to
correct.  Unfortunately, works just fine.  All Windows 7 Pro 64 bit with a few
older XP boxes which have never and still never experience the speed problem.

I eliminated the new gig switch I put in a little while back by getting an older
model that is much less sophisticated, and no issues w/anything except the
random slowdowns with Access.  That goes for both the new and the older switch
so I have to rule that out.  And rule out the wiring as I have moved
workstations around to see if maybe a bad cable was in play, but problem still
randomly happens on original location and moved location.

The server does not have Access installed, just has the MDB's on a shared drive.
Copy of FE on local PC's C drive.  I have some C drives that are SSD's, but
doesn't seem to matter.

What the norm has been for the 20+ years this A2K system has been around is that
if you are the only one in (say on a Saturday), everything is lightning fast.
Screens open in a second or less.  When 2 or more people get in, then the
screens slow a bit, but usually no more than 2 seconds to open any form, very
acceptable.  No complaints, even when 15+ are in at the same time, some running
multiple instances of Access (one instance runs a Dispatch Board on one monitor,
the other instances runs the rest of the program, like Work Order Entry,
Purchase Orders, Quotes, etc.)  The other thing I always do before putting an
update into the FE is to create a blank MDB and import all the objects from the
FE I'm working on and compile the FE.  This is what they run, a clean FE. There
was phenomenon back on their server in Windows NT days where if I made a change
to a report or form say on one machine, which would decompile that FE, if a
second person connected to the app, they would no!
 tice a 2 - 3 minute delay in opening any form, every time they clicked on a
button or other form.  As soon as everyone got out, one person was left, that
person would experience very fast performance again.  Creating blank mdb and
importing all objects and compiling took care of this, so I keep doing this
slightly pain in the tail procedure whenever making updates to the system.

Finishing up, not too many months ago the speed issue creeped in.  It was around
the time of both the addition of the new switch and introduction of Dell 3040
Optiplex Micro machines.  I have addressed removing network switch, but that
didn't help.  I can't remove all of the 3040's because of problem with old XP
boxes and the version of QB people are running.  There are maybe 4 or 5 3040's
in play.  Quite possible a Windows Update came across which would only effect
the Win7 boxes, that's a high possibility in my book.

And not being able to explain this one, I've seen a person being the only one in
the system on a Saturday morning and the system hung for minutes at a time
(white screen) just opening the Access App.  Not even the typical places which
are several of the forms/procedures.  That had never happened ever, so a gremlin
has snuck in somewhere and I just do not know where.  I do not know what tool(s)
to utilize that may help determine the hangup (maybe something like Wireshark, I
do not know.)

Thanks for listening.

John

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com