[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS ACCESS Databases too slow

Posted on 2007-11-29
6
Medium Priority
?
505 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

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!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

872 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