Three main questions you need to ask yourself are: 1) What is the cost of retrieving the data from the database (time, CPU, I/O) 2) What is the cost to cache it (memory mostly, speed of retrieval, cache management overhead) 3) How re-usable will it be/how soon will it need to be invalidated
I worked on a security system once that stored a 700,000 record result set in the application scope and then use query of queries on each page to get results. In the end, it wasn't any faster than a hit to the database (in-memory results have no indexes) but we left it there just to take some load off the database. Sometimes people try to cache too much when a hit to a well-indexed database can be rather inexpensive. Other times caching can be very handy if the database call is a very expensive operation. Of course, the benefits to storing a result set are inversely proportional to the diversity of the result set. If every user of the site gets a different result, you might fill up your memory very quickly. ~Brad -----Original Message----- From: sandeep saini [mailto:[email protected]] Sent: Thursday, March 04, 2010 11:22 AM To: cf-talk Subject: How to handle large ResultSet, so that it can also be reused? Hey Guys! I am creating a Reporting website in which we may get huge(ten of thousands of records) data in query ResultSet. I may want to re-use this data as well. So what is the best and efficient way to handle(store and retrive?) such a resultset? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331367 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
