What may effect MS-Access response time

[Alibaba2003]

Hi,
i have 3 copies of the same web site running the same server. Each site is connected to a different copy of an access database. The sizes of the databases are 10, 20 and MGs. The ASP pages connected to the first 2 databases are loading fairly quick. The last one loads ok only in non peak hours. It was working fine a week ago. I am suspecting that the database file having a problem. Other than compacting and repairing, is there is anything i can do to the mdb file to enhance the performance of the ASP pages?
Is there is a ceiling that access databases hit that prevent pages from loading properly? And how can I tell how many peopel are connected to my database at the same time.

Thank you for your help
Regards
Tareq

It's Nice to Be Important But It's more Important to BE Nice

 

[DreXor]

well, access db's have issues with multiple simultaneous connections, also after the tables start getting bigger ( higher record counts ) they start to get a little sluggish.. there's not too much that can be done other than possibly converting it to a mde ( access/tools/convert ) and to compact it ( access/tools/compact ) these help optomise the db a little better, compacting is like a defrag of the db, and converting it turns it into more of a "workgroup" friendly version.

other than that if the website in contention is getting a good amount of traffic, is to scale up to sql.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Tarwn]

The other thing that may help is to optimize your access code as much as possible. Stay away from dynamic recordsets, close connections as soon as humanly possible. Use GetRows to push the data into an array and get rid of the Recordset and COnnection objects, etc. Minimizing your connection time and overhead will help the database live a little longer. I have a monster access db or two that actually provide data a lot swifter then the end user's browser can handle. It's pretty funny when the Access db and ASP pages process in 3.4 seconds, but the page itself doesn't show up for 30+ seconds because the rbowser is rendering the massive amount of data your sending

A good way to see where slowdowns are occurring in your code are to scatter timer calls throughout it and maybe write a method to throw those in another access db. You may be surprised at what portions of your code are causing the worst slowdowns.

-T