Access limitations...

[Ime532]

I have an application that I believe to be growing beyond the capabilities of Access. There is a table with about 5.9 million records that my application is having speed issues accessing after compacting. Here's the deal:

DB size (no compact): 700mb
Records in table: 5.9 million
Simultaneous users: 9
Time of report without compact on my machine (no simultaneous users): 50 seconds
Time of report on file server that is sharing this db: 12 minutes

If I compact and repair, it actually INCREASES the time it takes to run the report on my machine to about 24 minutes!!!!! I can't seem to find an explanation for this. I tried defragging and then compacting/repairing and the same thing happens. If after the compact/repair I get the number of records down to about 3.1 million, the report only takes 50 seconds to run again.

Can anyone explain why compacting/repairing, even after a defrag causes the report to take 30 times as long to run?

 

[mp9]

Two thoughts:

1. Is the table indexed? If so, on how many fields? And do the indexes match nicely with the fields you are using to select/order by in your query?

2. Have you used the smallest possible data types in your tables? I.e. for number fields that are never going to be above 255 you can use a Byte type (1 byte in size) rather than a Long Integer (more bytes). Efficient data types can help to reduce your db size and speed data access (inc. queries), esepcially when you're delaing with this many records.

http://www22.brinkster.com/accessory/

 

[ZmrAbdulla]

Another thing to do
TOOLS > OPTIONS > GENERAL > uncheck TRACK NAME AUTO CORRECT INFO

Still your data is something Acess can't handle

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[Ime532]

Table is indexed on the primary key. All fields are as small as possible.

I think we've just found access' limit. I reduced the number of records by a lot in the one table and now the database is back to flying, even after a compact and repair. We're just going to move these bigger accounts to MDSE. Thanks for the responses!

 

[ZmrAbdulla]

You can keep old records into a separate db and link them into the working one. You need to use Union queries to get all the records.

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials