Skip to main content
Jush1
Jush1
Known Participant
July 6, 2010
Answered

Retain Query Resultset while paging

  • July 6, 2010
  • 1 reply
  • 1038 views

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?

This topic has been closed for replies.
Correct answer ilssac

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.

1 reply

ilssac
ilssac
Inspiring
July 6, 2010

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
Jush1Author
Known Participant
July 6, 2010

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
ilssacCorrect answer
Inspiring
July 6, 2010

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices