[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS ACCESS Databases too slow

Posted on 2007-11-29
6
Medium Priority
?
503 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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