Slow CF Server response

[wasmir]

We have a CF version 5.0 on an NT Web server and
the response is quite slow on roughly 6000 items of
MS Acces database query. Are there any methods or
other techniques to speed up the process? Please help
me.

[godzilla]

I'm not an expert in the field so I can't really understand what you mean by CF BUT as far as I know Access handling 6000 records is rather unsecure and uneffcient storage manager.

Note that Access was initially designed for non critical small size applications requiring fast deployment and thus is not suited for large data or fast access. Note also that the size of the data tends to increase geometricly under access without any particular reason.

Finally, I would suggest swithing to MySQL or any other prof. db that would allow fast and reliable access to data.

Hope I helped.

[wasmir]

Thanks. "CF" is Cold Fusion.

[rivux]

Access can handle a few million records without any real issues (depending on how many updates you are doing at a time).

As for your issue the two biggest drags on query times will be not having the proper indexes set and having unnecessary joins. 6000 records is nothing and CF should be able to fly through that, can you post the query(s) which is causing the issues and possibly some details on the database itself and the tables it using.

[godzilla]

accroding to http://www.mssqlcity.com/Articles/Co...QLvsAccess.htm I can make only 2 fields that would kill the database of Access in seconds. The only thing I need to do is to store just 2 movies of 700Mb each in the db OR I may want to store few thousand *.doc files each containing 10 and more pages of text and images and this will also kill the DB as the max size is 1GB...(unless you link it to some other files and thus grow the size indefinetly, but again at your own risk)

In general a simple search trough the web will give you lots of information conserning limitations of Access including really poor support for transaction handling and recovery from crash.
Finally I have personally tested Access db size grouth and I do not think that it is a wise use fo resources to have 20Mb of records that take up to 100Mb of space.

[fyamamoto]

Is your site slow under load, or is this just executing a simple query against 6000 rows of data.

Regardless of the database engine, 6000 rows should not cause a query to be slow (unless the server really is under quite a bit of load).

I'd suspect you have a slow running query. Can you paste the query with a short description of what the tables are in the query along with the approximate size of each of the tables in the query?