Retain Query Resultset while paging

[Jush1]

I have a query brings back thouands of records. In order not to impact  performance of display and paging, what would be the best way  without using query caching? Is it possible to keep the recordset first  time query it and using it while paging?

[ilssac]

Sure, a record set variable returned by a <cfquery...> block is no different then any other variable.  It is perfectly possible to store this variable in a shared scope such as Session, Applicaiton or Server.  Just as long as the scope accepts complex variables such as those three.  Just be aware of just how large this record set is and how many times it may be copied into a shared scope.  It is possible to full up one's server's memory with overly large record sets that hang around to long after being done.

But the code is as simple as this.

<cfquery name="application.myQuery"...>

<cfoutput query="application.myQuery"...>

[Jush1]

Thanks for the response Ian.

That is root of the problem. Because of load balance, when using query caching, I got inconsistant result. This happens after updating a record and using <cfobjectcache action="clear"> tag clear cache.  You may get an updated record count one time and cached record count the next. That is why I am looking any other options other than query caching.

Because of load balance, session variable is not an option. If I use application variable, that means I have to remove this variable everytime doing update.

I will give it a try.

[ilssac · Accepted solution]

Load balancing definitely throws a wrinkle in using shared memory.  There are ways to address the problems, but they all come with pros and cons, you basically just need to choose which pros you like and which cons you can live with.

I.E. One can use sticky sessions.  Thus when a user first access your application, all future requests are sent to the same server that handled the first request.  But that means that if that server goes bye bye, all users currently stuck to it are s.o.l.

I've read of others using databases for the persistant memory.  But then you have the lag involved in getting data from the database every request, though it should be fast easy to execute queries to do so.  On the pro side, it does not matter which server handles the request.

This would be different then just re-running the original query every time, but rather storing the results of the original query in a tempory table space so that any complexity that might exist does not need to be repeated.

[Jush1]

I know in MS SQL, Oracle you can use temp table, not sure DB2 can do the same thing. I have to check into it. I tried using Application variable instead of Session variable, it seems working. Thanks for your help.