I have a single table which contains 47 fields and 460731 records (at the time of writing) The total number of records in the table increases by approx 500 each week via an append query. I have an update query which updates the table contents, and I run this update query once a week immediately after each weekly append. The problem is: the update query is getting slower and slower to run. It can take 50mins or more now to run the update query.
My computer's processor type is AMD ATHLON 3000+ Processor x86 Family 6 Model 10 Stepping 0 AuthenticAMD ~2171 Mhz
120GB Hard Drive with less than one quarter used.
Total Physical Memory 1,536.00 MB
Available Physical Memory 1.18 GB
Total Virtual Memory 5.10 GB
Available Virtual Memory 4.61 GB
Page File Space 3.61 GB
Page File C:\pagefile.sys
My OS Name is Microsoft Windows XP Home Edition
Version 5.1.2600 Service Pack 1 Build 2600
OS Manufacturer Microsoft Corporation
I am using Microsoft Access 2002 (although the my Database says it is Access 2000 file format) the database is 522mb in size.
On reliable advice I have the MaxLocksPerFile adjusted upwards from the default 9500 to a new figure of 250000 but that has yielded no noticeable improvement as yet.
I am a novice and my PC skills are limited but I am open to any suggestions.