Access dB is very slow

[joacim]

Hi!

I have an application that uses a Access database. The dB stores stock data and becomes quite big after a while. One years data takes ~ 6 MB.

When dB is empty the application loads data quickly but while it grows it becomes slower and slower.

How can I increase performance?

Regards
Joacim

[bimmer boy]

switch to a different DBMS...like SQL Server...then add indexes...that'll help lot

miked

Are you accessing databases from network? Is this access database shared by different workstation? Are using ODBC?
If yes to above questions, Increase the Page timeout value to big number and check the performance.

[joacim]

No network and only one user.
I am using DAO.

I download a text file from Internet (~25 kB each day) and it is tab separated. After all files are downloaded I parse out the values and put it into dB. I also make a check if it already exist (this could take some time).


Regards
Joacim

Check your query logic? and Indexing?
Some times browse(move next...) will take long time.

[Dmitriy]

Divide you DB on month basis, for example. If you
have not so much size it works good. From my expirience Access works not so bad unless it has
more 4 MB

[joacim]

Hi again!

I got up the speed when I remove some checking and have to make an cleanup if user cancel the dB update.

Thanks for all tip!

Regards
Joacim

Hi,
Firstly make sure you have indexs set up on the relevant search fields (but not every field as this takes an age to insert a new record).
If want to insert only new items from a batch you downloaded, the quickest way I have found is to create a temp table, insert all items from the batch into this table then do a block update into the live table, make sure you have a key setup on the live that will only allow none duplicate entries to be inserted.

I am currenty running an order entry system using Jet 3.5 with 12 users, 200-300 orders per day, and it is currently 178mb in size, but speed is fine.

One word of advise, Compact, Compact, Compact (well three realy) every night.
One last word of advise, MAKE SURE YOUR INDEXES ARE SETUP CORRECTLY, there is a fine line in access between indexes being useful for just plain dragging performance down.

Hope this helps
Mark.