Re: ways to cache a query other than 'cachedwithin'
Hey, Jim, just putting some closure on this topic for archive's sakeI 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
Re: ways to cache a query other than 'cachedwithin'
Just to clarify - I'm assuming you mean that you're storing the instance of the CFC in the application scope - not that within the cfc you're storing the query into the application scope. Generally speaking, you don't want to touch persistent scopes from within a CFC. On 10/4/05, Ray Champagne [EMAIL PROTECTED] wrote: Hey, Jim, just putting some closure on this topic for archive's sakeI 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. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220004 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ways to cache a query other than 'cachedwithin'
Sorry, yes, this is true. Should have been a little more clear. Thanks! Deanna Schneider wrote: Just to clarify - I'm assuming you mean that you're storing the instance of the CFC in the application scope - not that within the cfc you're storing the query into the application scope. Generally speaking, you don't want to touch persistent scopes from within a CFC. On 10/4/05, Ray Champagne [EMAIL PROTECTED] wrote: Hey, Jim, just putting some closure on this topic for archive's sakeI 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. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220005 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ways to cache a query other than 'cachedwithin'
Make a persisted CFC and put the query in it at a variables scope variable. As long as the CFC is persisted, the query is persisted. MUCH cleaner than cachedwithin/cachedafter. I have an article on it waiting to go which I can send you off list (it's pre-edit) I've got a simple query that will grab all my navigational menu items. pretty simple. Now, I've added Mike D's cf_maketree script to modify the query by adding another field, called maketreesortlevel, that stores the tree level so that I can output the menu items in the correct order and depth. this query gets rather big, since I've got over 1300 categories and subcategories, so I'd like to be able to cache it. i know how to use cachedwithin as a cfquery attribute, but this won't work here, since I am modifying that query. is there another way to cache a query? here's the code, if it helps: cfif NOT isdefined(qry_get_topcats) or isdefined(attributes.refresh) cfquery name=qry_get_topcats datasource=#Request.DS# username=#Request.user# password=#Request.pass# cachedwithin=#Request.Cache# SELECT Category_ID, Name, Parent_ID, ParentIDs FROM Categories WHERE Categories.Display = 1 ORDER BY Priority, Name /cfquery !--- this script modifies the query to add 'maketreesortlevel' --- cfmodule template=make_tree.cfm Query=#qry_get_topcats# Result=qry_get_topcats Unique=category_ID Parent=Parent_ID /cfif TY Ray -- = Ray Champagne - Senior Application Developer CrystalVision Web Site Design and Internet Services 603.433.9559 www.crystalvision.org = ~| 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:219630 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: ways to cache a query other than 'cachedwithin'
Ray, You could store the query in the application scope. Robert -Original Message- From: Ray Champagne [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 10:27 AM To: CF-Talk Subject: ways to cache a query other than 'cachedwithin' I've got a simple query that will grab all my navigational menu items. pretty simple. Now, I've added Mike D's cf_maketree script to modify the query by adding another field, called maketreesortlevel, that stores the tree level so that I can output the menu items in the correct order and depth. this query gets rather big, since I've got over 1300 categories and subcategories, so I'd like to be able to cache it. i know how to use cachedwithin as a cfquery attribute, but this won't work here, since I am modifying that query. is there another way to cache a query? here's the code, if it helps: cfif NOT isdefined(qry_get_topcats) or isdefined(attributes.refresh) cfquery name=qry_get_topcats datasource=#Request.DS# username=#Request.user# password=#Request.pass# cachedwithin=#Request.Cache# SELECT Category_ID, Name, Parent_ID, ParentIDs FROM Categories WHERE Categories.Display = 1 ORDER BY Priority, Name /cfquery !--- this script modifies the query to add 'maketreesortlevel' --- cfmodule template=make_tree.cfm Query=#qry_get_topcats# Result=qry_get_topcats Unique=category_ID Parent=Parent_ID /cfif TY Ray -- = Ray Champagne - Senior Application Developer CrystalVision Web Site Design and Internet Services 603.433.9559 www.crystalvision.org = ~| 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:219631 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
Re: ways to cache a query other than 'cachedwithin'
Hey, thanks Mike, I'd love to see the unedited version. CFC's are new to me, but I've been looking for an excuse to jump in with both feet! You can send it to this address. Ray Michael Dinowitz wrote: Make a persisted CFC and put the query in it at a variables scope variable. As long as the CFC is persisted, the query is persisted. MUCH cleaner than cachedwithin/cachedafter. I have an article on it waiting to go which I can send you off list (it's pre-edit) I've got a simple query that will grab all my navigational menu items. pretty simple. Now, I've added Mike D's cf_maketree script to modify the query by adding another field, called maketreesortlevel, that stores the tree level so that I can output the menu items in the correct order and depth. this query gets rather big, since I've got over 1300 categories and subcategories, so I'd like to be able to cache it. i know how to use cachedwithin as a cfquery attribute, but this won't work here, since I am modifying that query. is there another way to cache a query? here's the code, if it helps: cfif NOT isdefined(qry_get_topcats) or isdefined(attributes.refresh) cfquery name=qry_get_topcats datasource=#Request.DS# username=#Request.user# password=#Request.pass# cachedwithin=#Request.Cache# SELECT Category_ID, Name, Parent_ID, ParentIDs FROM Categories WHERE Categories.Display = 1 ORDER BY Priority, Name /cfquery !--- this script modifies the query to add 'maketreesortlevel' --- cfmodule template=make_tree.cfm Query=#qry_get_topcats# Result=qry_get_topcats Unique=category_ID Parent=Parent_ID /cfif TY Ray -- = Ray Champagne - Senior Application Developer CrystalVision Web Site Design and Internet Services 603.433.9559 www.crystalvision.org = ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219632 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
RE: ways to cache a query other than 'cachedwithin'
-Original Message- From: Ray Champagne [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 11:27 AM To: CF-Talk Subject: ways to cache a query other than 'cachedwithin' I've got a simple query that will grab all my navigational menu items. pretty simple. Now, I've added Mike D's cf_maketree script to modify the query by adding another field, called maketreesortlevel, that stores the tree level so that I can output the menu items in the correct order and depth. this query gets rather big, since I've got over 1300 categories and subcategories, so I'd like to be able to cache it. i know how to use cachedwithin as a cfquery attribute, but this won't work here, since I am modifying that query. is there another way to cache a query? here's the code, if it helps: I think there might be some confusion here... you're cfif statement isn't doing what I think you think its doing. ;^) When you cache a query using cachedwithin it caches the query results keyed to the SQL statement (and information about it like user and password) - it doesn't cache the VARIABLE you're using however. Essentially if you run that EXACT SQL again you'll get the cached results. In your case this line: cfif NOT isdefined(qry_get_topcats) or isdefined(attributes.refresh) Doesn't do what you think it does. The variable qry_get_topcats is a local (variables scoped) variable and so is created at the beginning of a request and destroyed at the end - this line will never prevent the code from being run (unless it's call twice in the same request... if it were in an include for example). The variable qry_get_topcats is not cached. The SQL Statement you made (SELECT Catefory_ID...) is linked to the cached data. As it is I think your CFModule call will be run every-single request. Now... as to the actual problem. I'm going to throw out an idea, but I don't know if it'll work. It should be easy to try tho'. The query itself is cached by CF (and linked to the SQL Statement). But it can still be modified (as you see when you run your CFModule call). So, instead of checking for the query, check for your changes. The first time the query is run it's cached. But the maketreesortlevel column doesn't exist, your CFModule adds that. If the query is pulled from cache it will not change. However once the query is rerun it will be fresh again (no maketreesortlevel column). So something like this might work (I've abbreviated some of the code): cfquery name=qry_get_topcats ... cachedwithin=#Request.Cache# SELECT Category_ID, Name, Parent_ID, ParentIDs FROM Categories WHERE Categories.Display = 1 ORDER BY Priority, Name /cfquery cfif NOT IsDefined(qry_get_topcats.maketreesortlevel) cfmodule template=make_tree.cfm Query=#qry_get_topcats# Result=qry_get_topcats Unique=category_ID Parent=Parent_ID /cfif This caches the query. Running the same SQL Statement again will NOT result in a database hit until the cache timeout occurs. However the next block checks for your modifications - if it doesn't find them it adds them. Now - finally, to reset the query when you want... I believe (but I may be wrong) that calling the query with a cachedwithin of zero should effectively reset it. I think this might also result in two calls to the query however (one to reset it, another to recache it). Somebody else probably has a cleaner solution... but it would be something like this: cfif IsDefined(attributes.refresh) cfquery name=qry_get_topcats ... cachedwithin=0 SELECT Category_ID, Name, Parent_ID, ParentIDs FROM Categories WHERE Categories.Display = 1 ORDER BY Priority, Name /cfquery cfelse cfquery name=qry_get_topcats ... cachedwithin=#Request.Cache# SELECT Category_ID, Name, Parent_ID, ParentIDs FROM Categories WHERE Categories.Display = 1 ORDER BY Priority, Name /cfquery /cfif cfif NOT IsDefined(qry_get_topcats.maketreesortlevel) cfmodule template=make_tree.cfm Query=#qry_get_topcats# Result=qry_get_topcats Unique=category_ID Parent=Parent_ID /cfif I hope this helps, Jim Davis ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219633 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
RE: ways to cache a query other than 'cachedwithin'
-Original Message- From: Jim Davis [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 12:05 PM To: CF-Talk Subject: RE: ways to cache a query other than 'cachedwithin' I think there might be some confusion here... you're cfif statement isn't doing what I think you think its doing. ;^) When you cache a query using cachedwithin it caches the query results keyed to the SQL statement (and information about it like user and password) - it doesn't cache the VARIABLE you're using however. Essentially if you run that EXACT SQL again you'll get the cached results. Now I'm not so sure about that... but I think I'm still right. The queryname is definitely used as part of the key to the cached data... but since it's not in a permanent scope I don't see how it could be available to be checked (using IsDefined()) until the query is run. I think this needs some testing... Jim Davis ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219635 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ways to cache a query other than 'cachedwithin'
Thanks Jim. This is some good insight. I am trying out some tests right now, I'll let you know what the results are in a little bit. Jim Davis wrote: -Original Message- From: Jim Davis [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 12:05 PM To: CF-Talk Subject: RE: ways to cache a query other than 'cachedwithin' I think there might be some confusion here... you're cfif statement isn't doing what I think you think its doing. ;^) When you cache a query using cachedwithin it caches the query results keyed to the SQL statement (and information about it like user and password) - it doesn't cache the VARIABLE you're using however. Essentially if you run that EXACT SQL again you'll get the cached results. Now I'm not so sure about that... but I think I'm still right. The queryname is definitely used as part of the key to the cached data... but since it's not in a permanent scope I don't see how it could be available to be checked (using IsDefined()) until the query is run. I think this needs some testing... 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:219638 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
RE: ways to cache a query other than 'cachedwithin'
-Original Message- From: Jim Davis [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 12:12 PM To: CF-Talk Subject: RE: ways to cache a query other than 'cachedwithin' Now I'm not so sure about that... but I think I'm still right. The queryname is definitely used as part of the key to the cached data... but since it's not in a permanent scope I don't see how it could be available to be checked (using IsDefined()) until the query is run. I think this needs some testing... Do some testing... I think I'm correct. Run the following code (but change the query to actually work on your system): cfif IsDefined(MyQuery) Is Defined! cfelse Is NOT Defined! /cfif cfquerydatasource=... name=MyQuery cachedwithin=1 result=MyQueryInfo SELECT ... /cfquery cfdump var=#MyQueryInfo# The first time you run it you'll see that MyQuery is not defined. The Query metadata will show that in this instance it's not been cached. The second time you run it you'll see that MyQuery is still not defined... but the metadata will show that the query was retrieved from cache. Hope this helps, Jim Davis ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219641 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
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... Running the code below, I always get the result MakeTree rides again when I refresh the page, so it apparently seems like the module call is not modifying the cached query, it is just modifying the cached query, but dropping that modified query at the end of the request - right? I also see that make_tree.cfm is being called every time when I turn on debugging. Weird? Normal behavior? cfquery name=qry_get_topcats datasource=#Request.DS# username=#Request.user# password=#Request.pass# cachedwithin=#Request.Cache# SELECT Category_ID, Name, Parent_ID, ParentIDs FROM Categories WHERE Categories.Display = 1 ORDER BY Priority, Name /cfquery cfif NOT IsDefined(qry_get_topcats.maketreesortlevel) MakeTree rides again cfmodule template=make_tree.cfm Query=#qry_get_topcats# Result=qry_get_topcats Unique=category_ID Parent=Parent_ID /cfif Jim Davis wrote: -Original Message- From: Jim Davis [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 12:12 PM To: CF-Talk Subject: RE: ways to cache a query other than 'cachedwithin' Now I'm not so sure about that... but I think I'm still right. The queryname is definitely used as part of the key to the cached data... but since it's not in a permanent scope I don't see how it could be available to be checked (using IsDefined()) until the query is run. I think this needs some testing... Do some testing... I think I'm correct. Run the following code (but change the query to actually work on your system): cfif IsDefined(MyQuery) Is Defined! cfelse Is NOT Defined! /cfif cfquery datasource=... name=MyQuery cachedwithin=1 result=MyQueryInfo SELECT ... /cfquery cfdump var=#MyQueryInfo# The first time you run it you'll see that MyQuery is not defined. The Query metadata will show that in this instance it's not been cached. The second time you run it you'll see that MyQuery is still not defined... but the metadata will show that the query was retrieved from cache. Hope this helps, 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:219649 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: ways to cache a query other than 'cachedwithin'
-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
