?
Solved

Access database with 800,000 records (small ones)

Posted on 2014-07-14
12
Medium Priority
?
274 Views
Last Modified: 2014-07-17
Hi,

I have a simple database of perhaps 10 tables.  
The tables are all small except one (tblBig) which has 10 fields and 800,000 rows.

When I use a form that accesses tblBig all works fine on my personal PC.
However, when my client puts the database on their server it is taking 3 minutes to read ONE record!

The table is indexed on the key field.
How can I make the system faster when reading this table?? Perhaps the solution is on their server and NOT my database??

Any suggestions?
0
Comment
Question by:Patrick O'Dea
  • 3
  • 3
  • 2
  • +4
12 Comments
 

Author Comment

by:Patrick O'Dea
ID: 40195893
Just a thought??
The primary index is a "text" field.
Would it be quicker if it was a numeric field.   (I might be able to change it to numeric)
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40195911
How do you select a row in the database? There are quick and slow ways to do that.


Kelvin
0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 400 total points
ID: 40195915
What you have to avoid is having the form load all 800,000 records to the form. I've had Access db' with 6 million records in a table and could load a record in a few milliseconds

Kelvin
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:Patrick O'Dea
ID: 40195948
Thanks Kelvin,

The users key in ONE "primary index" and the record is displayed.
i.e. One index is keyed and one record is displayed.

As mentioned it works instantly on my laptop but takes minutes on their network.

Do you think I should change the primary key to a numeric?
 (it is currently a text field but 99% of records are numeric).
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40195970
The data type is probably not the issue. What code is executed after you type in the primary index?
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 400 total points
ID: 40196031
1. Try to run the Performance Analyzer on the database.

     Database Tools Tab > Analyze > Analyze Performance Command

2. Try with a copy of the database, removing all records but a few. See if get any improvements.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40196034
So, they have to type in the entire primary key into a textbox?  How do they know what the primary keys are?
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 40196511
Is your Form bound to a table/query? If so, can you provide the SQL of that?

Are you dealing with any Memo or OLE fields? Those can cause slowdowns at times.

If you place a copy of the FE directly on the server and link it, and THEN run the application, does the application respond? If so, then network issues are in play, and those would have to be resolved.
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 400 total points
ID: 40196724
On your pc will be much faster because you're not going through a network.  I'm assuming these folks keep the tables somewhere on a shared folder.

Now, is your application split between the tables and everything else?  In other words, are your tables in their own accdb/mdb and you have a "front-end" that links to these tables?  That's the way it should be.

Further, make sure each of your users download the "front-end" to their own PC.  It's a simple setup - open up your front-end on their system and link to the tables on the server (use the \\servername\... method - not a mapped drive letter).
Now, simply copy the front-end to your user's PCs.

This still won't be as fast as having the tables on your own hard drive... but that's reality.

Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 40196729
Further.... if your tables get too large - split them up each in their own accdb/mdb.   Each table can then be 2 gig in size (Access size limit) and your front-end can link to as many of these as you need.

Of course, you can't use referential integrity if the tables are not in the same accdb/mdb, but (as I do) you can write a few queries to determine whether or not the user is allowed to delete a "parent" record.

Scott C
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 400 total points
ID: 40200586
You also need to compact the BE and FE regularly.  Compacting the BE reorganizes the tables and puts them back into primary key order as well as getting rid of deleted records.  Compacting the FE decompiles all the queries so the next time you run them, Access will calculate a new execution plan.  The most efficient way to actually retrieve the data could change over time if indexes are added/deleted and row counts change.  So, if a table starts out very small, Access may decide to load it into memory and work with it that way but as it grows, Access may choose to use an index instead.

If you don't have indexes on common search fields, add them.

Do not use Filters.  Instead, use queries with selection criteria.  It takes a little more work initially but is much more efficient and will give significantly better results if you upsize to SQL Server.  Always retrieve the minimum amount of data with each query.  No user is going to look at 800,000 rows so don't bring them across the network.

Switching  to SQL server could make the app even slower (Jet/ACE are really super fast) unless you have used proper client/server methods such as what I have already mentioned.  If you open a form bound to a table or a query without criteria, Jet/ACE will bring over 800,000 records and so will SQL server but Access will probably do it faster so always make sure you use queries with criteria.  Filters don't work because filters are applied locally.  The whole recordset is retrieved and then filtered once it is loaded on your computer whereas where clauses are sent to SQL server and ONLY the REQUESTED rows are returned so you have complete control over your network traffic.
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 40201831
Thanks all for help.

I have applied a variety of your suggestions and have worked a solution.

I learn every day!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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