Having read some more of your replies, I think I would recommend just re-running the select every time. 10's of thousands of records really isn't that large of a result for a well-endowed SQL server to transfer around. The big question is how long it takes to generate them. Analyze your execution plans for starters and see if improvements can be made there.
As a last resort, I would suggest caching the data at a database level. Create a 'work file' table with columns to store everything the report needs, plus a columns for a unique session key and timestamp. When someone runs the report, dump their results in that table along with a key unique to their session and the current date. All subsequent sorts or filters THAT USER does on the data will come from that table. Create a job to clear out any old records every night. As long as you have some well-placed indexed on your work table, you could have a million records in that thing, and still get zippy results back from it. I've set up something similar to this as a proof of concept where I had a very expensive query I wanted to cache per user, and it worked pretty well. ~Brad -------- Original Message -------- Subject: Re: How to handle large ResultSet, so that it can also be reused? From: sandeep saini <[email protected]> Date: Thu, March 04, 2010 12:30 pm To: cf-talk <[email protected]> Also want to mention that we will have approx 200 concurrent users creating such big reports. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:331368 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
