Solved

MS ACCESS Databases too slow

Posted on 2007-11-29
6
499 Views
Last Modified: 2008-02-01
Hello Experts

I have a software that utilizes two Access Databases. One of them is a little over 1 MB in size and the other one is a little less than 1 MB. It takes a considerably long time to add/remove or even modify records. I tried the msacess/decompile on them with no success. Any ideas?

Thanks!

Tairo
0
Comment
Question by:Tairo
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 20376876
Do you know what method is used by the software to access the two database, establish connection?
Is this a recent problem?
0
 

Author Comment

by:Tairo
ID: 20376927
Unfortunately i do not. Which is kind of problematic. The software itself is deveopled by another company so i can not really access their code. All i can play with is the databases. And i actually misread the size of one of them, it was not 1 MB , it is 23 MB. Do you believe the software is using inefficient queries?

And this is a problem that seems to be assoiciated with the databases growing in size.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 20377009
It is entirely possible.  I had a programmer who was doing deleting all records from a table with an external program. He did not know SQL syntax of "Delete * from Table", which turned out to be a 100 time faster than whatever method he was using.
Since all of the data manipulation is being done by the 3d party software, unless there are network conenction issues, I would say it was that software.
You might examine your tables for complex indexes, which can both slow things down, and cause bloat.  Otherwise, ask the software developer if there are ways to tune the database.
0
Independent Software Vendors: 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!

 

Author Comment

by:Tairo
ID: 20377059
Great suggestion. Now my question to you is: What do you mean by complex indexes?

I am actually manipulating the databases locally on my laptop and they are being sluggish. So i guess that rules out network issues.
0
 
LVL 14

Accepted Solution

by:
ldunscombe earned 125 total points
ID: 20377479
Access databases are renowned for "Bloat" ie becoming much larger than they need to be, especially when performing deletes. ie "The space is not recovered"  Microsoft however have a tool for this within access itself
Tools > Database Utilities > Compact and repair Database
It is generally a very quick procedure and should be done on a regular basis.  I'm not saying that this is the answer to your problems but you may be pleasently surprized by the size difference and performance after you run it.

Leigh
0
 

Author Comment

by:Tairo
ID: 20378872
Excellent. Thanks a lot for your help Leigh!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question