Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-24-2018, 04:33 AM   #1
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Sudden performance degradation

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 ?

BeeJayEff is offline   Reply With Quote
Old 08-24-2018, 04:48 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,988
Thanks: 75
Thanked 413 Times in 373 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Sudden performance degradation

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.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 08-24-2018, 04:53 AM   #3
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Sudden performance degradation

No,no,no - all FEs are run locally, on the user's own machine.

BeeJayEff is offline   Reply With Quote
Old 08-29-2018, 11:22 PM   #4
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Sudden performance degradation

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.
BeeJayEff is offline   Reply With Quote
Old 08-30-2018, 05:05 AM   #5
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,988
Thanks: 75
Thanked 413 Times in 373 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Sudden performance degradation

How very odd. Without a lot more investigation, I couldn't begin to tell you how that might have happened.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 08-30-2018, 05:43 AM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,568
Thanks: 62
Thanked 1,196 Times in 1,096 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Sudden performance degradation

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?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-30-2018, 09:01 AM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Sudden performance degradation

Quote:
Originally Posted by BeeJayEff View Post
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?

Mark_ is offline   Reply With Quote
Old 08-30-2018, 10:51 AM   #8
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Sudden performance degradation

Quote:
Originally Posted by The_Doc_Man View Post
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).

Quote:
Originally Posted by Mark_ View Post
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.
BeeJayEff is offline   Reply With Quote
Old 08-30-2018, 11:05 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,350
Thanks: 92
Thanked 1,811 Times in 1,686 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Sudden performance degradation

Recommend strongly you disable compact on close.
That can be a source of problems.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-30-2018, 11:10 AM   #10
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Sudden performance degradation

Quote:
Originally Posted by ridders View Post
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) ?
BeeJayEff is offline   Reply With Quote
Old 08-30-2018, 11:20 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,350
Thanks: 92
Thanked 1,811 Times in 1,686 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Sudden performance degradation

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-30-2018, 11:27 AM   #12
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Sudden performance degradation

Quote:
Originally Posted by ridders View Post
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.
BeeJayEff is offline   Reply With Quote
Old 08-30-2018, 02:47 PM   #13
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,988
Thanks: 75
Thanked 413 Times in 373 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Sudden performance degradation

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.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
BeeJayEff (09-13-2018)
Old 08-30-2018, 03:00 PM   #14
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Sudden performance degradation

Quote:
Originally Posted by BeeJayEff View Post
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.
Mark_ is offline   Reply With Quote
Old 08-30-2018, 03:32 PM   #15
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Sudden performance degradation

Quote:
Originally Posted by Mark_ View Post
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.

BeeJayEff is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
sudden db problems 2 Albilly General 2 01-12-2016 11:40 AM
Image degradation Access '07 John Big Booty General 2 03-22-2009 12:53 PM
Sudden change in query performance Crusado General 3 07-18-2007 06:22 AM
Sudden poor performance bdaub General 2 01-11-2006 09:33 AM
[SOLVED] Help sudden errors! Teraesa Forms 1 06-14-2000 10:59 AM




All times are GMT -8. The time now is 08:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World