Solved

MS ACCESS Databases too slow

Posted on 2007-11-29
6
496 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now