Sudden performance degradation (1 Viewer)

BeeJayEff

Registered User.
Local time
Today, 05:23
Joined
Sep 10, 2013
Messages
198
For the last couple of days, all users have complained of poor system performance.

Background : maximum 6 or 7 users, split FE from BE, persistent connections. C&R on the BE done weekly, usually knocks the BE file size down from about 95MB to about 75MB. FE set to C&R automatically on close. A new FE was released a week ago with no performance hit noticed then but it is poor now, regardless of whether the new or an old FE version is run. I have also C&R'd the BE this morning, but performance is still noticeably poor.

Performance seems poor regardless of how many users are active or what they are doing. As an example a common function is for a user to print a label on their local Dymo printer. Last week the print would start immediately the control was selected, now a message saying "Now printing ..." appears for a significant time (3-4 seconds) before the print starts.

The db will be the main source of network traffic, and IT support claim that nothing has been done to the server or the network which could affect the db.

Showing my ignorance here - does an Access executable run on the server, as if so, would it be worth reinstalling it ? Any ideas what else I could be looking at ?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:23
Joined
Oct 17, 2012
Messages
3,276
An access executable will run on a server, but having multiple people access the same front end at the same time is a disaster waiting to happen. It changes from IF your file will get corrupted to WHEN it will get corrupted.

What were the changes to the FE? It's possible some queries are unoptimized or something.

In fact, what you're describing is the kind of behavior I sometimes see when an FE is run from a network location rather than locally.
 

BeeJayEff

Registered User.
Local time
Today, 05:23
Joined
Sep 10, 2013
Messages
198
No,no,no - all FEs are run locally, on the user's own machine.
 

BeeJayEff

Registered User.
Local time
Today, 05:23
Joined
Sep 10, 2013
Messages
198
Found the problem !

I happened to glance at the relationships diagram and noticed that one of the main tables in the design was duplicated and had lost most of its links to other tables ! Its primary key was also not identified as such. Once found, it didn't take long to sort out, and there was very little data loss but the question remains as to how such a corruption can have happened.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:23
Joined
Oct 17, 2012
Messages
3,276
How very odd. Without a lot more investigation, I couldn't begin to tell you how that might have happened.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:23
Joined
Feb 28, 2001
Messages
26,996
Duplicating a table in the Relationships panel is often done when a table becomes self-referential OR when one table references another table TWICE at one time. Did your recent changes attempt to implement something like either of those cases?
 

Mark_

Longboard on the internet
Local time
Today, 05:23
Joined
Sep 12, 2017
Messages
2,111
C&R on the BE done weekly, usually knocks the BE file size down from about 95MB to about 75MB.

Are you using a lot of temporary tables or doing a lot of deletes? I'm a bit surprised that you have such a dramatic change weekly.

If you DO have temporary tables you may want to create a 2nd back end just to hold them. That way you shouldn't need to C&R so much on your primary and can simply replace the "Temp tables" BE weekly.

Also, are you doing the C&R while the BE is still on the server OR are you moving it to a local drive first?
 

BeeJayEff

Registered User.
Local time
Today, 05:23
Joined
Sep 10, 2013
Messages
198
Duplicating a table in the Relationships panel is often done when a table becomes self-referential OR when one table references another table TWICE at one time. Did your recent changes attempt to implement something like either of those cases?
No, my recent changes were only to the FE - and I haven't had to do anything to the FE release to get it all working again. I do have a few instances where a table appears more than once in the Relationships diagram (e.g. Currencies, where there are more than one Currency field in a record - for example cost price and sale price currencies).

Are you using a lot of temporary tables or doing a lot of deletes? I'm a bit surprised that you have such a dramatic change weekly.

If you DO have temporary tables you may want to create a 2nd back end just to hold them. That way you shouldn't need to C&R so much on your primary and can simply replace the "Temp tables" BE weekly.

Also, are you doing the C&R while the BE is still on the server OR are you moving it to a local drive first?
The BE is typically 15-20% smaller when I compact it each week, has been like that for years. No temporary tables are created, and there are very few deletes. Further to a post elsewhere on here, I have just changed from doing the C&R in situ on the server to renaming it, taking a local copy, C&R'ing that and copying it back. Apart from anything else, it was a lot quicker ! My current suspicion is that there was a minor network glitch when I did the last C&R on the server.
 

isladogs

MVP / VIP
Local time
Today, 12:23
Joined
Jan 14, 2017
Messages
18,186
Recommend strongly you disable compact on close.
That can be a source of problems.
 

BeeJayEff

Registered User.
Local time
Today, 05:23
Joined
Sep 10, 2013
Messages
198
Recommend strongly you disable compact on close.
That can be a source of problems.

Very interesting. Presumably you mean on the BE - I'm not at work at the moment, so cannot check whether it is set, but I expect it is. But what does "close" mean for the BE - is it when the last user exits and releases the lock file, or is it only if I'm actively working on it (I'm the only one who does so) ?
 

isladogs

MVP / VIP
Local time
Today, 12:23
Joined
Jan 14, 2017
Messages
18,186
I was referring to the FE - back in post #1 you said it is set to compact on close.
I wouldn't compact the BE either. Just run an automatic backup e.g. daily.
 

BeeJayEff

Registered User.
Local time
Today, 05:23
Joined
Sep 10, 2013
Messages
198
I was referring to the FE - back in post #1 you said it is set to compact on close.
I wouldn't compact the BE either. Just run an automatic backup e.g. daily.
OK - I'll try switching if off for the next FE release, and see how both FE and BE grow without C&R.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:23
Joined
Oct 17, 2012
Messages
3,276
You can compensate for FE bloat by using a script or batch file to make users automatically download the most recent FE every time they run the program.

For the BE, just figure out how often you need to do a C&R to keep it from getting out of hand. I'd normally suggest either once a month or once a week, depending on how much data alteration is done, especially deletion.
 

Mark_

Longboard on the internet
Local time
Today, 05:23
Joined
Sep 12, 2017
Messages
2,111
My current suspicion is that there was a minor
network glitch when I did the last C&R on the server.

As it has come up recently in another thread, one of the most effective ways to do this is
1) Rename the BE on the server so that your FE cannot talk to it.
-IF you can't rename the BE, someone is still in the DB.
2) Move the BE to a "Backup" or "Archive" directory.
3) Copy your BE to a local drive.
4) Rename your BE back to its original name.
5) Do the C&R.
6) Copy or Move the BE back so that it is back in use.

This is, so far, the least likely to have issues.
 

BeeJayEff

Registered User.
Local time
Today, 05:23
Joined
Sep 10, 2013
Messages
198
As it has come up recently in another thread, one of the most effective ways to do this is
1) Rename the BE on the server so that your FE cannot talk to it.
-IF you can't rename the BE, someone is still in the DB.
2) Move the BE to a "Backup" or "Archive" directory.
3) Copy your BE to a local drive.
4) Rename your BE back to its original name.
5) Do the C&R.
6) Copy or Move the BE back so that it is back in use.

This is, so far, the least likely to have issues.

Yes, as I said above, that is what I have now done.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:23
Joined
Feb 28, 2001
Messages
26,996
Here's a thought. When you have a shot at some exclusive time, copy the BE to some place where you can play with it. Not a backup copy, just an ordinary file copy.

One table at a time, look at every index. Be sure that you haven't lost an index on any of your heavily used tables. If you have blown an index out, that would immediately lead you to sudden performance degradation.

You also asked a question about automatic C&R. If the FE has it set, it is the FE that undergoes the C&R. Frothingslosh's suggestion to just copy & launch your app from an icon that launches a script would be better than the C&R because (a) everyone would start with a good copy at each launch and (b) everyone would start with the latest, greatest copy at each launch.
 

Users who are viewing this thread

Top Bottom