…posted by davidjmedlock:
I happened to notice this on Macromedia’s ColdFusion Developer Center today. I think the article has been out for a little while but I thought I’d post the link here for any who are interested.
Caching Queries to Disk or Memory With ColdFusion
This author also provides a custom tag to do all the dirty work for you. I was thinking about query caching in terms of a few things:
Let’s say you have 1,000 clients or users and you build a complex, dynamic report so that user 1 logs in and views the report. In there it has something like (WHERE UserID = #UserID#) to pull the report for that user.
Well, you can begin to see the problem: 1,000 users = 1,000 complex reports. If you cache those reports using cachedWithin then you’re going to murder your server quickly.
The disk based approach might work better, but first of all, you still have to run every query at least once. If your data changes frequently, then you’ll need to run it once every x hours, depending on how quickly your clients/users want or need the information.
At any rate, I’ve been dealing with a massive report at the office that pulls from all of our largest tables (some tables with a couple of million rows and growing rapidly by the second). This report causes quite a bit of strain on the system, so we’ve moved it to the backup server so that it doesn’t kill us on the live server. This works out okay because the backup server is updated every night.
Well, this is all well and fine except that the report is still a monster. So, in this situation I’m taking an approach that has worked well before: build a report into a single table overnight. Then all I have to do is query one table and dump the results out on screen. It can be tedious to build the report overnight. It’ll take a good knowledge of the DBMS you’re using and the structure of your database, but it can alleviate a lot of server load during the day when you’re busiest.
Anyway, enough of my blabber. I just thought I’d share that with you. It may make your job much easier, for those of you who deal with extremely large systems and amounts of data on a regular basis.
davidjmedlock
