G
Guest
Hay all!
Can anyone give me some ideas on the problem I am experiencing?
I have an empty and compacted Access 2002 database. This database is copied
each morning to a user's computer, and the program starts using this empty
database.
On most of the days, the program has excellent performance, as the database
doesn't have much data. Sometimes, however, the user is experiencing
tremendous slowdown when executing some SQL update or insert statements -
updating or inserting a dozen records in a single statement can take over
half a minute.
If the program is closed, and database compacted, this problem goes away.
Note that (a) there is only one computer/user accessing the database, (b)
the database starts empty each morning by coping the 'template' that is not
modified in any way, (c) the database is accessed locally, not over the
network, (d) the database is accessed using DAO (not an Access application,
but a VB application).
This can happen on any computer (there are 14 of them in total).
I suppose the cause might be some sort recurring database corruption. It is
also worth nothing that the database file shrinks considerably after being
compacted. I also suspect a possible design issue which causes the database
to get quite sparse (many data pages that are mostly empty of records).
Did anyone have similar issue? Can anyone give me some advice on how to
determine what actually happens inside the database file?
Thank you very much for any help with this!
Tom
Can anyone give me some ideas on the problem I am experiencing?
I have an empty and compacted Access 2002 database. This database is copied
each morning to a user's computer, and the program starts using this empty
database.
On most of the days, the program has excellent performance, as the database
doesn't have much data. Sometimes, however, the user is experiencing
tremendous slowdown when executing some SQL update or insert statements -
updating or inserting a dozen records in a single statement can take over
half a minute.
If the program is closed, and database compacted, this problem goes away.
Note that (a) there is only one computer/user accessing the database, (b)
the database starts empty each morning by coping the 'template' that is not
modified in any way, (c) the database is accessed locally, not over the
network, (d) the database is accessed using DAO (not an Access application,
but a VB application).
This can happen on any computer (there are 14 of them in total).
I suppose the cause might be some sort recurring database corruption. It is
also worth nothing that the database file shrinks considerably after being
compacted. I also suspect a possible design issue which causes the database
to get quite sparse (many data pages that are mostly empty of records).
Did anyone have similar issue? Can anyone give me some advice on how to
determine what actually happens inside the database file?
Thank you very much for any help with this!
Tom