Hey, Jim, just putting some closure on this topic for archive's sake....I went with a CFC that stored the query in the application scope. Many many thanks to Mike D. for helping me out with that, and you for guiding me as to what was going wrong with my thinking.
Ray Jim Davis wrote: >>-----Original Message----- >>From: Ray Champagne [mailto:[EMAIL PROTECTED] >>Sent: Thursday, September 29, 2005 1:31 PM >>To: CF-Talk >>Subject: Re: ways to cache a query other than 'cachedwithin' >> >>Okay, Jim, using this and your last suggestion, I have made a test case >>that I think points to the same issue that I was having in the >>beginning. Of course, the code that I have will run correctly. I agree >>that just testing for the "qry_get_topcats" var wasn't the right way to >>go... > > > Yeah... this is where I was guessing. ;^) > > I wasn't at all sure that you could modify the cached query... but it looked > like you were so I went with it. > > I'm not sure what's happening... I assume that the modifications to the > query "work", right? > > My guess is that CF is not providing a pointer to the original query data > but rather duplicating it. So you're using (and modifying) the duplicate, > not the original. > > It's probably simplest to just NOT cache the query using the CFQUERY cache > options. You could just plain do the query and store it in an application > variable like so: > > <cfif NOT IsDefined("Application.MyCachedQuery")> > <cfquery name=" Application.MyCachedQuery" ... > > ... > </cfquery> > <cfmodule>... Make your changes to the application-scoped query ... > </cfif> > > However if the query is a long one and your site is busy you'll want to > thread-protect that a bit: > > <cfif NOT IsDefined("Application.MyCachedQuery")> > <cflock name="Guard_MyCachedQuery" ... > > <cfif NOT IsDefined("Application.MyCachedQuery")> > <cfquery name=" Application.MyCachedQuery" ... > > ... > </cfquery> > <cfmodule>... Make your changes to the application-scoped query ... > </cfif> > </cflock> > </cfif> > > The outer CFIF simply determines if the query exists. The CFLOCK prevents > multiple requests from running the query at the same time. The inner CFIF > prevents requests that were waiting for the lock to be released from running > the query again. > > The outer CFIF really isn't completely needed - but I think it's quicker to > check an application variable every request rather than acquiring a lock AND > checking the application variable. > > You can later in your other checks as needed. I suggest that you > compartmentalize your code however. For example don't check for your > application reset field in that block but rather before it like this: > > <cfif IsDefined("attributes.ResetCachedQuery")> > <cfset StructDelete(Application, "MyCachedQuery") /> > </cfif> > > <cfif NOT IsDefined("Application.MyCachedQuery")> > <cflock name="Guard_MyCachedQuery" ... > > <cfif NOT IsDefined("Application.MyCachedQuery")> > <cfquery name=" Application.MyCachedQuery" ... > > ... > </cfquery> > <cfmodule>... Make your changes to the application-scoped query ... > </cfif> > </cflock> > </cfif> > > The first block determines if your query needs to be refreshed and, if so, > deletes it. The second then runs to rebuild it. You could do all the > checks in the second block of code but things would start to get confusing > real fast. > > Lastly you can also set a "cache timeout" manually. Just store a timestamp > when you fetch the data and check it all the time. Something like: > > <cfif NOT IsDefined("Application.MyCachedQuery_Created ") OR DateDiff("n", > Application.MyCachedQuery_Created, Now()) GT 60> > <cfset StructDelete(Application, "MyCachedQuery") /> > </cfif> > > <cfif NOT IsDefined("Application.MyCachedQuery")> > <cflock name="Guard_MyCachedQuery" ... > > <cfif NOT IsDefined("Application.MyCachedQuery")> > <cfquery name=" Application.MyCachedQuery" ... > > ... > </cfquery> > <cfset Application.MyCachedQuery_Created = Now() /> > <cfmodule>... Make your changes to the application-scoped query ... > </cfif> > </cflock> > </cfif> > > You'll have to play with "DateDiff()" (I always seem to get it backwards) > and set it for the time you want (if I did it right it will update once an > hour or so). > > You might also just create a scheduled task (once an hour say) that calls > the page with the "ResetCachedQuery" attribute. > > But, putting it all together you might have something like this: > > <!--- Determine if the query refresh is being forced ---> > <cfif IsDefined("attributes.ResetCachedQuery")> > <cfset StructDelete(Application, "MyCachedQuery") /> > </cfif> > <!--- Determine if the query is stale ---> > <cfif NOT IsDefined("Application.MyCachedQuery_Created ") OR DateDiff("n", > Application.MyCachedQuery_Created, Now()) GT 60> > <cfset StructDelete(Application, "MyCachedQuery") /> > </cfif> > <!--- Determine if the query needs to be rebuilt ---> > <cfif NOT IsDefined("Application.MyCachedQuery")> > <!--- Lock the rebuilding of the query ---> > <cflock name="Guard_MyCachedQuery" ... > > <!--- Guard against unneeded duplication by checking again (stops > requests that were waiting for the lock from redoing the task) ---> > <cfif NOT IsDefined("Application.MyCachedQuery")> > <!--- Call the query ---> > <cfquery name=" Application.MyCachedQuery" ... > > ... > </cfquery> > <!--- Set the cache timeout ---> > <cfset Application.MyCachedQuery_Created = Now() /> > <!--- Update the query with your changes ---> > <cfmodule>... Make your changes to the application-scoped query ... > </cfif> > </cflock> > </cfif> > > A lot of this can become even more streamlined and cleaner by abstracting > the cache and query management into a persistent CFC. > > Jim Davis > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220003 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
