Link to home
Start Free TrialLog in
Avatar of maximyshka
maximyshka

asked on

Increase performance of the database in Access 2010

Hi. I need to make some steps to decrease run time:

1) Time needed to save form
2) Time needed to go from the form design mode to the view mode
3) Time during to run make-table queries

One ways is to do it to create executable file, accde format.
What is the difference between Accdb and accde file type?

Do I constantly need to create executable type of databases to avoid run time issue in Access 2010?
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<One ways is to do it to create executable file, accde format.>>

You cannot create and executable file with Access.  An Access DB is more like a document that is read rather then a program.

<<What is the difference between Accdb and accde file type?>>

  a DE has the source code stripped out.  That's the only difference.  As a result, no code can be changed and nothing with code attached can be modified.

<<1) Time needed to save form
2) Time needed to go from the form design mode to the view mode
3) Time during to run make-table queries
>>

 Your covering a wide range there, but in general:

1. Turn off name autocorrect
2. Turn off subdatasheets on all tables.
3. If a JET Backend, keep a constant connection to the BE open.
4. Make sure your prorperly indexed.
5. Make sure your app is compiled (for a DE, it must be since only compiled code is in the DB).

As for #2, that won't decrease unless the tables are local (on the PC).  Move a copy of the BE to the PC and then re-link to it for development work.

Beyond all that for performance, it would go to how the app is developed. i.e. not using Domain Functions in queries, using Dlookup multiple times in place of opening a recordset, etc.

Jim.
Avatar of maximyshka
maximyshka

ASKER

Hi Jim.  Thanks for replies.  Regarding split between front and back end. I'm not doing anything special  (simple queries and front end interface).  
One major issue: a slow network, therefore having tables linked over slow network will not help in such case.
No tables have auto correct or datasheet.

Only approach to speed up is to create "accde" file.  This is strange. Any comments on this one?
Is your network a LAN or WAN?
<<Only approach to speed up is to create "accde" file.  This is strange. Any comments on this one? >>

 If your accdb is compiled, then there is no difference between the accdb and the accde for all practical purposes.

Jim.
To be clear "Speed" issues in a database is a very broad topic,...so without being intimately familiar with your entire database, all we can give is suggestions...

<One ways is to do it to create executable file, accde format.>
I have never heard of this as a "Fix" that will magically "speed" up a slow database...

What creating an .accde files does is compile the code and create a version of the database where you cannot make design changes
So perhaps you need to compile your code more often.

Also do not discount the value of running the compact/repair utility on a regular basis.

1) Time needed to save form
2) Time needed to go from the form design mode to the view mode
...It this for only one form or all forms?
If it is one form then the form itself may be corrupt, ...and may need to be rebuilt.
If it is All forms then see here:
https://www.experts-exchange.com/questions/27474500/MS-Access-2010-Slow-Switching-From-Form-View-To-Form-Design-REDUX.html
...as you never explicitly stated if the DB was split or not...

Other basic stuff is to Create a new database and copy all of the objects into it.
Decompiling may also be needed.

JeffCoachman
Monolithic applications are known to be unstable when opened simultaneously by multiple users especially over a wireless network.  They are slower to open and more prone to corruption.  The recommended solution is to split the database into FE and BE.  The FE contains everything except tables and the BE contains ONLY tables.  The FE links to the BE and you work with the linked tables as if they were local.  Then, the BE is moved to a shared network folder and EVERY user is given his OWN copy of the FE which is stored on his C: drive.  That minimizes some overhead since the FE is local and doesn't have to be dragged across the LAN.  The connection from the FE to the BE exists only for instants of time where Access is actually reading or writing data.  That is what makes this configuration more stable.  If one use has a problem, he doesn't lock out every one else or corrupt the data.

To speed up the application, I'm going to restate some of the suggestions made by other posters and add some of my own.
1. Compact the BE regularly.
2. Compact the FE regularly (compact on close is not generally recommended).
3. Make sure each table has a primary key and what ever indexes it needs to support searches and joins.  Don't go overboard.  Every index requires maintenance when a record is added or changed so there is a happy medium that will speed up the searches but not slow down the updates.
4. Make sure you have created relationships and enforced Referential Integrity to give the database engine some help.
5. Only select the columns you need in queries.  Don't just Select * because you're lazy.
6. Add criteria to all queries to limit the number of rows returned.  No user needs to work with a form bound to a table of 100,000 rows.

Although distributing as .accde/.accdr is recommended, it won't make any speed difference.  If you create an .accde you MUST, MUST, MUST remember to keep your .accdb safe because you will always need to go back to it to make any changes.  After you make the changes, you will recreate the .accde and distribute the new copy to the users.
Thanks a lot for all your advices.  The main issue that I have worked with Access 2010 before on my previous work.  Everything was fine, so I have reasons to believe that the main issue of the speed is a slow network.

Creating ACCDE file is just a finding a way to go around the problem.

Jeff: I have only only one form, a simple interface.

Additional thing to ask: I used the Access 2010 feature of embedded macro for convenience to run simple queries. I think it is like new feature of Access 2010.
Have anyone heard about errors associated with using embedded macros.
(I did not use VBA code to run simple queries.)
Thanks
It is still not clear if the DB is split or not...
Is the DB Split? Yes or No?

You never replied to Jim on his 5 suggestions...?

You keep saying that your network is "slow", ...please define "slow" in terms of hardware, OS, topology and real chronological terms.

fyed asked if this was a LAN or WAN
You never replied directly here either...

Are you running the FE on your local machine, or is in on the network?
see the link I posted.
You never stated if you tried creating a new database, or tried decompiling...

In other words you keep explaining your system setup, but you rarely state if you have tried all of our suggestions?

In short, you keep saying that it is your Network, with out quantifying this.

(I doubt that embedded macros have anything to do with this...)
Sorry, let me reply:

DB were not split. As I indicated I'm afraid to put back end on slow network.
I'm running Front End both ways on Local Machine and part of network which is not shared drive for testing purposes.

I created database: also, used compact and repair feature. Not sure what you mean about decompiling. I don't have any vba code.  Only embedded macros with simple queries
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks to all experts:

Your answers were that creating "accde" executable type file will not reduce the run time.

Other answers were about reducing run time by changing modifying tables (using indexes and work on make table queries. All these answer relevant and I will look into them.

BTW: I did "split" on database.  I put main tables on back end and left only queries, form interface and temp tables in front-end database.  This suggestion was listed in many answers.

The issue which I reported in step 1, 2 was not fixed:

1) Time needed to save form
2) Time needed to go from the form design mode to the view mode

I need between 1-2 min in order to save form, go between design to live mode.  Usually, this should take seconds, no matter how big the form is.  As I previously advised my form contains only buttons associated with simple queries. I'm not sure why saving form or going between different modes takes time.

Description of the problem: When I press "Save" or going between modes I see on status bar a note "Form Not responded".  My personal opinion that this somehow connected to locking.  Although, I'm not sure about that.  

I used "Form Operations" -> "Open Form" to navigate between main menu form and other forms for different segments of the population

Do you have any idea how can I improve performance on the form only?  It is very strange when saving form or going between modes takes around 2 min.

BTW: "fyed" thanks a lot for your article.  Very helpful

Regarding other answers: Queries which reflect 40000 records will take more amount of time then queries which take 10-200 records. No matter how simple queries are. This is the reason why I use make table queries.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just a note that you can exclude my posts for points consideration.
Your issue has been thoroughly discussed by the other experts.
;-)

JeffCoachman
Thanks to all experts.  Jim, you are right.  The issue was with network.  Follow your suggestions.