I have a one table MS Jet database that has 6 text fields (25 char size for each), that is created and managed by a VB6 application using DAO 2.5/3.51 Compatibility Library. Over the years, this database has increased in the record count (25MD database with 420,000 at this time) and is expected to continue to grow for some time. All was well until recently, where the very simple query (SELECT * FROM mytable WHERE myfieldx = 'the data', normally taking about 250 millisec or less to run, now has random periods of time where it take seconds (in some cases minutes) to run and hangs the VB app during that time. I have found that the delay occurs at the db.openrecordset(sql) statement in the VB app. I can't understand what is causing the query to sometimes become delayed.
I put together a small test app to repeat the process over 500 loops against a copy of the database where it does not change. With the same exact process, open the database, call the same db.openrecordset(sql), loop through all the resulting records (same expected records are always returned), close the table and database, set database to nothing, and then repeat this 500 times, I usually end up with a delay on several of the loops, where all others were less than 250ms. The delay is always occurring at the db.openrecordset(sql) call. If I reduce the database to 12MB with 210,000 records in the table, I never see get any delays.
This seems to occur on Windows 7, XP, Windows 2003 Server, and in test conditions, where there are no other apps running on the PC and where Anti-virus is disabled, there are no delays in 99.5% of the time, but it's this 0.5% that is troubling me. Is the Windows OS or MS Jet doing something on occasion to interfere where some registry setting may be involved?
I looked for other forum threads but found none having similar situations and I'm not sure if this is a normal Jet behavior. My next step will be to break this database into multiple smaller databases, or convert to SQL Server, unless there are some further suggestions as to what I might look into. I can post my test loop code if needed.
Regards,
jjt