I forgot to add that a simple way to prove this point is to create 2 templates (test1.cfm and test2.cfm) doing a simple query, such as:
<CFQUERY DATASOURCE="CFSnippets" cachedwithin="#createtimespan(0,0,2,0)#" name="test"> SELECT FirstName, LastName FROM Employees </CFQUERY> With that code in each, save them and browse them in 2 different browser instances (just to make any refresh/browser caching matters easier to isolate). If you run test1.cfm (with debugging on and "show query information" turned on), you'll see that the first time, it has a time of some milliseconds. If you refresh it, it shows "cached query" (in the debugging output). Go ahead and run test2.cfm in another window. You'll see that the first time you run it, it is indeed getting the cached result (as long as you run it within 2 minutes of the time you first ran test1.cfm). That shows that two templates do indeed share the same result if all the query attributes and SQL are the same (proving that it's shared among all users is something I'll leave for the reader, though it should seem obvious that there's nothing about the query above that ties it to a user or an application.) Now, change the query in test1.cfm (remove the lastname), thus changing the SQL. Refresh it. You'll see that it changes to a non-cached result time, but if you hop over to test2.cfm (Still running the same old previously cached query) and you refresh it, you see that it is indeed still a cached result. Again, you need to do this change of the SQL and running of test1 and refreshing of test2 within 2 minutes of the time the result was first cached to really prove the point.) But this shows that a cached result set, once created, is NOT "purged" or "replaced". A new cached result set is created, and any queries with the same attributes that run in the timespan from which the cached result for that combination was first created will indeed benefit from the cached result. One last thing: I was mistaken in suggesting that the cfquery.executiontime variable would tell you that it was a cached result. Sadly, it just shows "0", which in some queries may indeed prove it's cached (if they couldn't possible run in 0 seconds) but it's not as good nor as accurate as the debugging output which does literally say "Time=Cached Query"). /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of charles arehart Sent: Friday, February 22, 2002 1:21 PM To: [EMAIL PROTECTED] Subject: RE: [CFTALKTor] CF cachedwithin question Good point, Bob, about the CreateTimeSpan when it's just a number. I'd always wondered about that myself--and since it was days, it's kind of hard to observe! :-). And, yes, it's true that: > A cached copy of a query will only be used if the current query is exactly > the same as the cached copy in all respects, i.e. sql, db, username, > password. But I have to disagree with your point next point that > If anything has changed (e.g. different search parameter so sql is > different) then the query will be run and it will replace the cached copy. Indeed, the same mistake is made in the quote Glenn offers from the book (what book was that?), when it says: > "Cached queries are tied to all users accessing the same query. If any > one user modifies the query or its attributes, then the query is purged > from the cache." Now, the first sentence is true, and that's one of the questions Glen had asked. And I assume Bob that you were asserting that as well. But when each of you indicate that a changed set of SQL will "replace" (Bob's words) or "purge" (the book's words) the previously cached query, that's a mistaken assertion. The new SQL will simply create a new instance of a cached result. The cool thing about that is that if this CACHEDWITHIN (or the equally if not more interesting CACHEDAFTER) attribute is used on a single query in a single template that's perhaps got some form variable used to drive the SQL being generated, then each instance of someone executing that template will indeed generate a new, cached result for that SQL. This is great in that you could have the benefit of caching to lots of different query results being generated on the same page (or indeed other pages that are using the exact same query attributes as Bob noted). And, for sure Glenn, this is for all users on the server (not just the application). Since it's tied to the datasource, there's little need for it to be app-specific. Indeed, it has no connection at all with application variables or even the existence of a CFAPPLICATION tag. The converse of this power to create lots of different cached results from a single CFQUERY is that it could create too many. Indeed, you should consider this before turning it on willy-nilly. The ramifications could be negative for your app, your users, and indeed all those on the server who want to use cached queries. As currently designed, the cache results are stored in memory (again, one place for all users of all apps) in such a way that the admin can limit how many are stored (not how much space it taken, but simply how many cached result sets are stored). So if your app gobbles up lots of cached result set slots because you cache a query that's often resulting in unique new SQL results--even if it's just a search engine doing an equality or pattern match on a single form input field, where users enter lots of values, then you're filling up that cache. And in doing so, you'll force any older cached results (from your users or those of other apps using caching) out of the cache. Hope that all makes sense. Caching is a very cool thing, and when used effectively it can be a huge resource saver and performance booster. But if not used carefully, like any tool, it can hurt you. (The admin debugging output showing query results will show if a query came from a "cached query", instead of showing the number of milliseconds it took. Same for the cfquery.executiontime variable that's available after every CFQUERY. Sadly, there's no system-level monitoring statistic I know of to tell how often "cache pops" occur on the system-wide cached result stack.) /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bob Keleher Sent: Friday, February 22, 2002 12:29 PM To: [EMAIL PROTECTED] Subject: Re: [CFTALKTor] CF cachedwithin question Timespans are stored as number of days where the fractional part is the fraction of a day, e.g. 10.25 is 10 days 6 hours. So the original 20 in the cachedwithin setting meant 20 days. A cached copy of a query will only be used if the current query is exactly the same as the cached copy in all respects, i.e. sql, db, username, password. If anything has changed (e.g. different search parameter so sql is different) then the query will be run and it will replace the cached copy. In other words, cached queries are only useful if the sql seldom changes. ----- Original Message ----- From: "Glenn Shukster" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 11:21 AM Subject: [CFTALKTor] CF cachedwithin question > Hi > In an application that I just took over there was a line of code in a > cfquery that said cachedwithin="20". I changed that to = > createtimespan(0,0,20,0). What would the 20 on its own be. Is that > minites, hours, days? > > I read a book on this area and it said: > "Cached queries are tied to all users accessing the same query. If any > one user modifies the query or its attributes, then the query is purged > from the cache." > > This doesn't make sense to me for I took away this line of code and that > page was taking over 12 sec to load on average. > Once I put that line back in it was down to under 7. > If every user shares the query and the query is not used unless someone > elses every user comes in with a different query then why would there be > such a dramatic difference? > -- > Cheers > Glenn Shukster (Logic Fundamentals Inc.) Thornhill, Ont. Canada > Phone:(905)771-6458 Fax:(905)771-6819 www.logicfundamentals.com > President of TDUG www.tdug.com > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: Glenn Shukster <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Bob Keleher" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "charles arehart" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "charles arehart" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
