Optimizing Access

[JK]

I'm running a series of 15 queries on some relatively large databases (~1.2
GB each with ~3 million records). I'm interested in any links or general hint
on how to optimize Access for speed. In particular, I note that Access is not
making much use of my RAM (I have 2 GB). Any way to trick Access into using
more RAM and accessing the disk less?

Thank you,

JK

 

[Guest]

Are you using queries inside the database, or linked
connections? Linked connections are slower.

If you are using linked connections, are you using exclusive
links? Exclusive linking is faster than shared linking.

If you are using stored queries, have you compacted
the databases to reset the query statistics?

If you are using MDB data, you can increase the size of the
jet cache:

application.dbengine.SetOption 8,1000000
(dao.dbMaxBufferSize, 1 GB)

But your default value would already be 497.5MB
((TotalRAM in MB - 12 MB) / 4) + 512 KB

Perhaps you should also try increasing the cache latency:
application.dbengine.SetOption 6, 120000
(dao.dbPageTimeOut, 2 minutes)

Are you using transactions? transactions are written
to a transaction file, then the database file is updated
in one action. This is sometimes faster.

Finally, why is Access using the disk at all? By default,
due to lack of support in the OS, the database primitives
no longer write back to disk: only to the file cache. If
you are seeing disk activity, perhaps you need to investigate
the file cache settings.

(david)