• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

Access Degrading Performance While Programing

This is a long explanation, please bear with me.

Overview: I have been working on a split database since 1998.  It is a A2003 version and I am now using A2007.  It has a front end DB that connects to 4 back end DBs.  I have it setup such that whenever I use/edit the front end, it opens a hidden form to lock records to the backend databases.  The front end database is distributed (via script) on boot up on each user's workstation.  There are on the average 8-10 users connected at a time to the back end DB with a maximum of 16 users.  The front end DB is huge it now has 104 linked tables, 303 queries, 256 forms, 106 reports, and several modules.

Problem:  I have noticed over the last 1-2 years that the performance has been degrading while I am in the database programming updates or changes to the Front End DB.  It is now to the point where it is extremely slow to make changes to the database.  For example, when I am in a form that has a subform, and I try to move a field in the subform, it takes 20-30 seconds to select the subform's field and move it.  Furthermore, it takes 30-45 seconds to save some of the forms.  The front end DB that I work on is not the same version that the users are working on, but it is located in a BETA folder until I publish it to be copied the next morning to the workstations.

Interesting Notes:
-The performance of the overall database in general use is not affected by this.  IT IS ONLY WHEN I AM EDITING/PROGRAMING.
-  It seems to be much slower only during the day when users are connected to the BACK END databases.  Then everybody is gone for the day, it is much quicker and tolerable.
-  It seemed to get slower when I decided to "clean out" (delete) older unused forms / queries and reports.
-  I do have user security and permissions enabled and have had several users added and removed over the years.

Here is what I have done so far to fix this:
-  Compact & Repair (I do this everyday before I deploy the new version of the Front End)
-  Decompiled and Compiled
-  Encode and Decoded
-  Used the analyze performance tools (in A2003) did not really find anything

I need new suggestions.  This is getting crazy slow.
0
shrimpfork
Asked:
shrimpfork
  • 17
  • 8
  • 4
  • +3
5 Solutions
 
Rey Obrero (Capricorn1)Commented:
after

-  Decompiled and Compiled

create a blank db and import all objects, this will give you fresh container

save the original as a backup copy

0
 
shrimpforkAuthor Commented:
What do I do with the user permissions and security?  I don't think it transfers.
0
 
Jon500Commented:
In addition, run NETSTAT from DOS to determine if there might be a network issue (look for delays and/or timeouts); also, be sure all drives used by the MDB (front-end and back-end drives) are defragged and are not near-capacity.

Regards,
Jon
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
ULS doesn't transfer - you must move it over manually. At one time there was a utility that helped with this, but I couldn't find it.

Have you turned OFF Name AutoCorrect? This can be very perfomance-hungry when developing, since it must track all changes to your objects. There's no reason to have it on, so turn it off, Compact and Repair, and see if this helps.

0
 
shrimpforkAuthor Commented:
Experts,
I can't really fully test until Monday, when the Backend DB has a full load.
When I import all objects to a new database will that remove any possible corruption?

I am curious to anybody thoughts why the load on the Backend DB (with 8-16) users connected affects the performance durring programing in the FrontEnd.  When there is nowbody connected to the Backend, it has little performance problems while I'm programing.  Is it a problem with having 4 Backend databases?  Or with a the record locking procedure?

LSMConsulting: Oh yes, I forgot to add that to teh description...AutoCorrect has been turned off.  (Good thought though.)

0
 
Rey Obrero (Capricorn1)Commented:
when you are going to make revisions to your frontend,
it will be best, to get a copy of the FE and BE place them in a folder in your desktop.

when done with the revisions replace the FE of the users.
use an autoupdater, see this link

http://www.autofeupdater.com/
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I need new suggestions.  This is getting crazy slow.>>
  Outside of AutoCorrect, I would also make sure you turn off the sub datasheet feature if your not using it (I've pasted in a routine to do that).  That's another CPU cycle sucker. After doing that, if you still have a problem, then  I would follow cap's advice and import everything into a new DB.  Decompile doesn't cure everything.  You also may have corruption in the VBA project.  If that is the case, you may have to save all objects as text and then load as text into a new DB.
  Somce developers on the Access D list (Database Advisors Access list) were working on a app called EatBloat, which was originaly authored by Max Wanadoo and does that nicely.  It really does a good job on cleaning up a database.  I can probably get you a copy of it if needed.
 Last, and I'm not sure if your doing this from what you said or not, but use a hidden form at app startup to keep each of the BE's open (ie. code that opens a global recordset for example).  This will speed up operations with the BE's because they will not be repeatedly be opening and closing, which is quite a bit of overhead, especially if the server is busy.
  Of course when developing, you'll probably need to open that form manually.
JimD.
 

Sub TurnOffSubDataSheets()

        Dim MyDB As DAO.Database
        Dim MyProperty As DAO.Property
        Dim propName As String, propVal As String, rplpropValue As String
        Dim propType As Integer, i As Integer
        Dim intCount As Integer

10      On Error GoTo tagError

20      Set MyDB = CurrentDb
30      propName = "SubDataSheetName"
40      propType = 10
50      propVal = "[None]"
60      rplpropValue = "[Auto]"
70      intCount = 0

80      For i = 0 To MyDB.TableDefs.count - 1
90        If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
100         If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
110           MyDB.TableDefs(i).Properties(propName).Value = propVal
120           intCount = intCount + 1
130         End If
140       End If
tagFromErrorHandling:
150     Next i

160     MyDB.Close

170     If intCount > 0 Then
180       MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
190     End If

200     Exit Sub

tagError:
210     If Err.Number = 3270 Then
220       Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
230       MyProperty.Type = propType
240       MyProperty.Value = propVal
250       MyDB.TableDefs(i).Properties.Append MyProperty
260       intCount = intCount + 1
270       Resume tagFromErrorHandling
280     Else
290       MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets RoutineName."
300     End If
End Sub

Open in new window

0
 
shrimpforkAuthor Commented:
JDettman,
I would be interested in the application EatBloat.  As you may have read, I have hundreds of objects in my database.  I'm planning on testing each suggestion next week.

-Shrimpfork
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Shrimpfork,
<<I would be interested in the application EatBloat.  As you may have read, I have hundreds of objects in my database.  I'm planning on testing each suggestion next week.>>
  Try the other suggestions first.  While EatBloat is quite workable, it needed some spit and polish that Max didn't have the time for.   A few developers on the Access-D list were working on a new release, but I'm not sure where it stands.
  I know Max used it on a regular basis with his apps, but he felt it was a bit to cumbersome to use and didn't have the time to put into it.
JimD.
0
 
shrimpforkAuthor Commented:
capricorn1,
I am going to start importing all objects into a new DB. Some questions....
Is there a good procedure for this? For example, should I do Tables first, then the Queries then the Forms....etc? Since I have security enabled on the current DB, should I use export rather than import since the security is not on the new DB yet?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you have properly implemented security, you will have to also implement security on the "receiving" database. You cannot export/import objects from a secured database to a non-secured database.

Access will determine the proper order in which to import items.
0
 
shrimpforkAuthor Commented:
I have all the objects imported in the new database, (still A2003 version). Before I move on, I need to figure out how to configure the user-level security. To be fair, I posted this in a secondary question. See the link below....

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25047790.html
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I have all the objects imported in the new database, (still A2003 version). Before I move on, I need to figure out how to configure the user-level security. To be fair, I posted this in a secondary question. See the link below....>>
  before you go through the time and energy of doing that, I would first see if the problem is resolved.  Security can always be applied latter.
JimD.
 
0
 
shrimpforkAuthor Commented:
Experts,
Ok I have been working on this today. I have imported all the objects into a new DB. I have also double checked that all the tables have the Subdataset Name is set to 'None'. Unfortunately there is little change with the performance.
I did notice that the biggest performance issue is when I have a Subform within a Main Form. When I click to select the subform, it is quick to select it. However, when I click on a subform object and enter inside the subform, it takes 20-30 seconds to select the subform object. When I click outside the subform, to get back to the main form, it take another 20-30 seconds to get back.
Any thoughts?
0
 
shrimpforkAuthor Commented:
<it will be best, to get a copy of the FE and BE place them in a folder in your desktop.>>

I forgot to ask how this would work if I have all my FE tables linking to the BE datbase all on a folder on the server.  When I move it to my desktop, I would have to point the FE to the new temporary BE location. Then when I get ready to publish, I would have to redirect the FE to the actual BE location. Is there an easy way to do this?  I was thinking about temporary drive remaping on my computer.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm assuming you turned Off Name AutoCorrect in the new database?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I was thinking about temporary drive remaping on my computer.>>
Temporary drive mapping would be one way to solve that, but re-pointing the table links is easy.  You can either use the linked table manager or some code.
I've attached the relink code I use.  Call RefreshJETTables() with the name of the BE database.  You'll be prompted for the location and the links for that location will then be updated.  If you have more then one BE, you'd need to call it several times or you can easily build a little code routine to do that.
 I normally would call it from IsJETTableAttached(), which checks that a given BE is connected by testing one table that comes from that BE.  I do that in the Autoexec macro.   If I have multiple BE's, I make multiple calls.
JimD.

Relink2.Zip
0
 
shrimpforkAuthor Commented:
LSMConsulting,
Yes I have had Name AutoCorrect off for a couple years now.

JDettman,
The linked table manager works well for 1-2 time uses. I might look into your code, but I have 104 tables linked to 4 BE databases, how efficient is this going to be?  I'm afraid of publishing and forgetting to redirect the links.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The linked table manager works well for 1-2 time uses. I might look into your code, but I have 104 tables linked to 4 BE databases, how efficient is this going to be?  I'm afraid of publishing and forgetting to redirect the links.>>
Well there are two approaches:
1. You do the refresh before you distribute - In this case, your user will need to do nothing.  If you do this, you may still wish to call IsJETTableAttached() from the Autoexec macro for each BE.  That way, if you forget to refresh the links, your user will get a prompt for the location of the BE.  This is the approach I would take.
2. Just let the users locate and refresh the links on there own.
  As far as efficiency, it only takes a few seconds for each BE to be re-linked.  With 104 tables, if it took more then 30 seconds or so total, I would be suprised.  It mainly depends on the number of BE's you need to refresh to.  Most of the overhead is in opening a DB.  Once opened, the refresh goes quickly.
JimD.
0
 
KprimeCommented:
I ran across this same problem.  It started after my database reached a moderate size.  I find that even if one other person is in the back end programming is very slow.

The suggestion to use a separate FE and BE on my local HD is a very workable solution.  Regardless of how big my FE is getting 70-80M (BE is about 30M) programming is still very fast as long as I am working locally.   I just use the built in link manager and update the links before programming.  Yes, I have to remember to reset the links before I distribute the new FE. However, it make programming easy and works as a great preventative against corrupting or screwing up the programming on the working copy.  Additionally if worse comes to worse it makes a plan B backup.

By the way, I simply drag the BE master from the server to my local machine, that way I am working with fairly fresh data but dont have to worry about deleting or changing that data when I am testing my programming.  Of course, I never drag the BE back to the server.  Works great and its simple.
0
 
shrimpforkAuthor Commented:
Currently I'm working on my FE DB located on a bata folder on the server.  Should I also look at moving this to my computer for programing?

(I do this because I have Continous Data Protection on the server, which will backup several versions of the my changes thorought the day.  This is nice if I make a bad mistake in programing, I will not loose an entire day's worth of work.)
0
 
Rey Obrero (Capricorn1)Commented:
< This is nice if I make a bad mistake in programing, I will not loose an entire day's worth of work.)

i don't depend on those backups.. i do the back up myself. At times i ended up with 4 -5 copies a day.
0
 
KprimeCommented:
Like capricorn, I make numerous version saves throughout the day.  Yes having the FE and BE both on a local HD speeds the whole programming process.  I've been using this process for several years and find it to be productive and simple.  When I reach a completion point I simply redistribute the FE to all stations on the network.
0
 
shrimpforkAuthor Commented:
Experts,
This has been all great information. This is what I have applied so far....

1) Imported all objects into a new FE DB (still A2003 version) - [No noticeable performance change but see below.]
2) Double checked that all tables Subdataset Name set to "None"
3) Made sure AutoCorrect feature was off the new DB.
3) Tested running with a copy of the BE on my local computer and the drive remapped to my local folder. - [This was lightening fast to work on programming. Very nice.]

Problem:  I tested the new FE on a couple heavy used workstations and now the FE is extremely slow to pull up a few key reports. Before they would open on 4-5 seconds, and now they are taking 20-30 seconds.  Any thoughts?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Problem:  I tested the new FE on a couple heavy used workstations and now the FE is extremely slow to pull up a few key reports. Before they would open on 4-5 seconds, and now they are taking 20-30 seconds.  Any thoughts?>>
 Make sure it was compiled when distributed.  Also be aware that if you did a compact and repair, the first time you run a query, it will be re-costed as a C&R invalidates all the current costing plans.   This can work for you or against you.  It will not be re-costed again after that first run.
JimD.
0
 
shrimpforkAuthor Commented:
JDettman,
I did run a compact and repair immediately after I imported all objects. What do you mean by...

<<Also be aware that if you did a compact and repair, the first time you run a query, it will be re-costed as a C&R invalidates all the current costing plans.>>   ?????   I don't know if I understand.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
When you execute a query, JET will run a costing plan on the SQL statement. That is it will try to figure out the best possible way to execute a query (in terms of performance).  This is based on a number of factors; indexes available, number of records in the table, where clause, type of query, etc.  You can see this costing plan by using JET SHOWPLAN
A C&R invalidates all the existing costing plans.  So the first time you run a query, it will do this costing, which can take a considerable amount of time.  After that, the plan is saved with the query and when run next, will execute based on that plan.
So if you want the best possible performance when you distribute, you should be executing all the queries once.  I don't know of many people that bother to do that.
In some rare cases, JET will cost the query out incorrectly.  I've only tripped over two of these cases personally in the last 15 years.    In one case, if a certain table was empty when the costing was done, it would do it one way and if that table was full, would do it in the exact opposite way (this was a three table join with a sort).
The resulting query was fast one way (1-2 seconds) and slow the other (20+ Seconds) even if the table was filled after the costing was done.
 So the developer had a little routine to empty the table, execute the query, and then fill the table back up.  he did this every time he distributed the app.
JimD.
0
 
Rey Obrero (Capricorn1)Commented:
<So if you want the best possible performance when you distribute, you should be executing all the queries once.  I don't know of many people that bother to do that.>

I always do this before rolling out a new FE version.
0
 
shrimpforkAuthor Commented:
JDettman,
Wow this is all new info for me.  Will a compact and repair invalidate the existing costing plans everytime I do a compact and repair?  How do I access JET SHOWPLAN?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Wow this is all new info for me.  Will a compact and repair invalidate the existing costing plans everytime I do a compact and repair?> >
Yes.

<< How do I access JET SHOWPLAN?>>
Here's a link to an excerpt from the Access Developers Handbook covering queries, costing, and SHOWPLAN:
http://msdn.microsoft.com/en-us/library/aa188211(office.10).aspx 
I'm also attaching a couple of files you can use to switch SHOWPLAN on and off.
JimD.

TurnJetShowPlanON.reg
TurnJetShowPlanOFF.reg
0
 
shrimpforkAuthor Commented:
You guys are awesome!  Thank you.  I'll take a look at this stuff.
0
 
shrimpforkAuthor Commented:
<<So if you want the best possible performance when you distribute, you should be executing all the queries once.  I don't know of many people that bother to do that.>>

I don't know if this is helping.  I have run all the queries that affect this particular form and it has not made a noticeable difference in the performance.
0
 
shrimpforkAuthor Commented:
Experts,
Ignore my last comment.  I think it is working.  

As you may have read, I have 303 queries.  Some of these are for modules and some refer to fields in open forms to narrow the query.  How can I quickly run these with seeing the popups asking to "Enter Parameter Value"?
0
 
shrimpforkAuthor Commented:
There were multiple solutions that all contribute to the final result.  The best result was remapping the BE to my local computer.
0
 
shrimpforkAuthor Commented:
Thank you experts for the valuable information. I had a very hard time assigning the points on this one.  If is short changed somebody, I'm sorry.  (I wish they had an option to change it to 1000 points for when 1 questions turns into 2-3 more.)  I'm going to look at posting my previous comment in another question.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 17
  • 8
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now