Re: What scope are cached queries in?
On Wednesday 24 January 2007 19:01, DURETTE, STEVEN J (ASI-AIT) wrote: This code isn't mine. I'm just trying to trouble shoot the excessive amounts of memory that are being used and not released. The site has a low hit count, so I don't see why it should be using soo much. Fusion Reactor ! -- Tom Chiverton Helping to dramatically grow one-to-one e-business This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267579 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
What scope are cached queries in?
Is there a scope that holds cached queries and if so how can it be access to see what is in there? This is related to my previous post about enumerating what is in an instance's memory block. Thanks, Steve ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267467 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: What scope are cached queries in?
Is there a scope that holds cached queries and if so how can it be access to see what is in there? No, there isn't. If you want to manage memory directly, store your queries in Session, Application or Server scope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267509 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: What scope are cached queries in?
This code isn't mine. I'm just trying to trouble shoot the excessive amounts of memory that are being used and not released. The site has a low hit count, so I don't see why it should be using soo much. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 24, 2007 12:48 PM To: CF-Talk Subject: RE: What scope are cached queries in? Is there a scope that holds cached queries and if so how can it be access to see what is in there? No, there isn't. If you want to manage memory directly, store your queries in Session, Application or Server scope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267524 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cached queries
On Tuesday 26 September 2006 19:30, Snake wrote: You don't want to store in sesison scope, otherwise a copy of the query will be stored for each and every visitor, which could result in jrun using up all the available memory. It *could*, but you'd need a lot of visitors on a very under spec'ed box. Session-caching is the way to go here, unless the results never* change, in which case use Application. -- Tom Chiverton Helping to dramatically e-enable high-end customers This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254361 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Cached queries
I did think of storing them in a session/application scope but to be honest I am currently thinking I can safely ignore text fields as any single quotes would be excaped anyway, I can then do... cfif isNumeric(somefield)do the sqlcfelsecfthrow type=an error/cfif And that would prevent any injection attempts. I could then cache the queries the regular way, no? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254364 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cached queries
On Wednesday 27 September 2006 14:32, James Smith wrote: And that would prevent any injection attempts. I could then cache the queries the regular way, no? It will execute slower than a query that uses cfqueryparam though. -- Tom Chiverton Helping to vitalistically bully end-to-end bandwidth This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254381 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cached queries
On 9/26/06, Che Vilnonis [EMAIL PROTECTED] wrote: Wait for Scorpio (CF8). This was just answered on the list a few weeks ago. Try one of these links: Not exactly 100% And no, that is not a promise or a public commitment (yet). ;-) --- Ben ~~~ Casey ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254387 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Cached queries
I have a need to cache some queries. For safety and security these queries use cfqueryparam. You are not allowed to use cachedWithin or cachedAfter with queries that contain a cfqueryparam. Any idea how I go about caching these? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254258 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cached queries
Wait for Scorpio (CF8). This was just answered on the list a few weeks ago. Try one of these links: http://www.google.com/custom?domains=www.houseoffusion.com%3Bwww.fusionautho rity.comq=cachedWithinsa=-%3Esitesearch=www.houseoffusion.comclient=pub- 9987223904467795forid=1channel=3811075283ie=ISO-8859-1oe=ISO-8859-1cof= GALT%3A%23008000%3BGL%3A1%3BDIV%3A%2366%3BVLC%3A663399%3BAH%3Acenter%3BB GC%3AEE%3BLBGC%3A66%3BALC%3A00%3BLC%3A00%3BT%3AFF%3BGFNT %3AFF%3BGIMP%3AFF%3BLH%3A50%3BLW%3A500%3BL%3Ahttp%3A%2F%2Fwww.houseo ffusion.com%2Fimages%2Fhof_google.gif%3BS%3Ahttp%3A%2F%2Fwww.houseoffusion.c om%3BFORID%3A1%3Bhl=en -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 1:08 PM To: CF-Talk Subject: Cached queries I have a need to cache some queries. For safety and security these queries use cfqueryparam. You are not allowed to use cachedWithin or cachedAfter with queries that contain a cfqueryparam. Any idea how I go about caching these? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254259 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Cached queries
You can make a function that caches the query object in the application scope. Have the function return type be query, and when the query is called, check the cache time and re-run the query if the cache has expired. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254260 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Cached queries
Or, you could store your queries within persistent memory variables (Session, Application, Server). This might be a better link to help: http://tinyurl.com/kou23 -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 1:08 PM To: CF-Talk Subject: Cached queries I have a need to cache some queries. For safety and security these queries use cfqueryparam. You are not allowed to use cachedWithin or cachedAfter with queries that contain a cfqueryparam. Any idea how I go about caching these? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254261 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Cached queries
You don't want to store in sesison scope, otherwise a copy of the query will be stored for each and every visitor, which could result in jrun using up all the available memory. Russ -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: 26 September 2006 18:17 To: CF-Talk Subject: RE: Cached queries Or, you could store your queries within persistent memory variables (Session, Application, Server). This might be a better link to help: http://tinyurl.com/kou23 -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 1:08 PM To: CF-Talk Subject: Cached queries I have a need to cache some queries. For safety and security these queries use cfqueryparam. You are not allowed to use cachedWithin or cachedAfter with queries that contain a cfqueryparam. Any idea how I go about caching these? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254274 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Manually flush all cached queries?
I know you can zero out the cachedwithin times, but that doesn't seem feasible as the caches are in all sorts of CFCs and have various timeouts assigned. It's worth noting that the caching is keyed on the text of the query. And once you execute it once with a timeout low enough that it hits the database, that updated data will be available to all of the places that query is run with a longer timeout. So if you can, immediately after an operation that should invalidate the cache, run that query with a zero second window, that new data will be visible in all of those other CFCs. (On a side note, perhaps it would be wise to factor these queries out into their own methods/CFCs, with an argument to specify the cachedwithin value that should be used. Then your cache-invalidation procedure would just be to invoke the same method everything else does, with a lower value. And everyone is guaranteed to share the same query code.) Sixten ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252383 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Manually flush all cached queries?
Thank you, Raymond. (I just posted my self-followup coming to the same conclusion before your response came in.) Clearing all of the queries is exactly what I wanted to do, so problem solved. cfobjectcache action=refresh will clear ALL of them. To clear one query, rerun the query with the EXACT same settings (sql, attributes, etc) but with a timeout of zero. When I say exact same - it must be the exact same even when it comes to white space. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252386 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Manually flush all cached queries?
Self-followup: I researched a bit and came up with cfobjectcache action=clear/. It seems to do the trick, although I've just read elsewhere that it may not clear all queries. However, some is better than none for my purposes and it worked in my test just now. Anyone used it before? I had never even heard about it. Thanks, Jeff ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252385 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Manually flush all cached queries?
I fear I already know the answer to this, but is it possible? We use cachedwithin queries all over the place and want to be able to flush CF's memory when needed to reflect changes from the database immediately. Is there any way to do this other than restarting the CF service? I know you can zero out the cachedwithin times, but that doesn't seem feasible as the caches are in all sorts of CFCs and have various timeouts assigned. Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252376 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Manually flush all cached queries?
Just went through this yesterday. Basically you can add a space or change the query so CF thinks it's a new query and it won't use the cached version. However, that only works for a single query. Otherwise, you have to restart the server. Jeff Guillaume wrote: I fear I already know the answer to this, but is it possible? We use cachedwithin queries all over the place and want to be able to flush CF's memory when needed to reflect changes from the database immediately. Is there any way to do this other than restarting the CF service? I know you can zero out the cachedwithin times, but that doesn't seem feasible as the caches are in all sorts of CFCs and have various timeouts assigned. Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252377 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Manually flush all cached queries?
cfobjectcache action=refresh will clear ALL of them. To clear one query, rerun the query with the EXACT same settings (sql, attributes, etc) but with a timeout of zero. When I say exact same - it must be the exact same even when it comes to white space. On 9/7/06, Jeff Guillaume [EMAIL PROTECTED] wrote: I fear I already know the answer to this, but is it possible? We use cachedwithin queries all over the place and want to be able to flush CF's memory when needed to reflect changes from the database immediately. Is there any way to do this other than restarting the CF service? I know you can zero out the cachedwithin times, but that doesn't seem feasible as the caches are in all sorts of CFCs and have various timeouts assigned. Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252384 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Cached queries memory usage
Has anyone found a way to get access to the underlying java object(s) that CFMX uses for query caching? It would be nice to be able to see precisely (a) how many queries have been cached and (b) how much memory each one is using at any point in time. Additionally, it would be nice to know precisely how the access times for a query scales as the number of cached queries increase (a hash?), and how those access times compare to self-caching in arrays or structs. Regards, Terry ~| 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:226507 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: Cached queries memory usage
Honestly, you really ought not to care. I'd say it's safe to assume
that MM made it operate as quickly and efficiently as possible. If
the functionality the built-in cache uses is sufficient for your
needs, it's very likely it'll be faster than anything you can build in
CFML (simply because the built-in stuff is written directly in Java).
If the functionality isn't sufficient, then you have to write your
own.
CF provides no means to check the sizes of in-memory objects (and I
don't thinkg Java does either - except perhaps via JVM monitoring
tools), so it's pretty unlikely that you'll be able to do that either
way.
Since queries are cached based on exact SQL matching, I'd consider it
a safe wager that the cache does something like this:
function runCachedQuery(sql) {
var key = hash(sql);
if (! queryCache.has(key)) {
queryCache.put(key, runQuery(sql));
}
return queryCache.get(key);
}
cheers,
barneyb
On 12/7/05, Terry Ford [EMAIL PROTECTED] wrote:
Has anyone found a way to get access to the underlying java object(s) that
CFMX uses for query caching?
It would be nice to be able to see precisely (a) how many queries have been
cached and (b) how much memory each one is using at any point in time.
Additionally, it would be nice to know precisely how the access times for a
query scales as the number of cached queries increase (a hash?), and how
those access times compare to self-caching in arrays or structs.
Regards,
Terry
~|
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:226511
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: Cached queries memory usage
Honestly, you really ought not to care.
The reason I care is that when you're using a large number of cached queries of
differing (sometimes variable) sizes in a large, highly loaded, dynamic
environment, it's not at all easy to know how many queries you ought to cache,
nor what sort of durations you should place on each one. It would be quite
insightful to know what the average size of a cached query over a period of
time in the cache was, to better tune the number of queries option based on
the amount of memory you're willing to dedicate to that task.
As far as the question of performance, it is very important to understand the
strengths and weaknesses of CF and Java's data types and caching mechanisms
precisely so you can better choose between using one of them or rolling your
own. Anyone truly interested in high performance and optimization ought to
care.
Regards,
Terry
Honestly, you really ought not to care. I'd say it's safe to assume
that MM made it operate as quickly and efficiently as possible. If
the functionality the built-in cache uses is sufficient for your
needs, it's very likely it'll be faster than anything you can build
in
CFML (simply because the built-in stuff is written directly in Java).
If the functionality isn't sufficient, then you have to write your
own.
CF provides no means to check the sizes of in-memory objects (and I
don't thinkg Java does either - except perhaps via JVM monitoring
tools), so it's pretty unlikely that you'll be able to do that either
way.
Since queries are cached based on exact SQL matching, I'd consider it
a safe wager that the cache does something like this:
function runCachedQuery(sql) {
var key = hash(sql);
if (! queryCache.has(key)) {
queryCache.put(key, runQuery(sql));
}
return queryCache.get(key);
}
cheers,
barneyb
On 12/7/05, Terry Ford [EMAIL PROTECTED] wrote:
Has anyone found a way to get access to the underlying java
object(s) that CFMX uses for query caching?
It would be nice to be able to see precisely (a) how many queries
have been cached and (b) how much memory each one is using at any
point in time. Additionally, it would be nice to know precisely how
the access times for a query scales as the number of cached queries
increase (a hash?), and how those access times compare to self-caching
in arrays or structs.
Regards,
Terry
~|
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:226519
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: Cached queries memory usage
If you really care that much, you're going to be doing some pretty in-depth load testing anyway, so you can vary the different cache amounts (query cache, other manual caches) and get app-specific results. And even then, it's still irrelevant what the absolute efficiency of the query cache is. It's a question of good enough that can only be answered by testing your specific app. No preexisting data will be meaningful, because your app will be different than whatever app was used to generate the data. I'm a huge fan of the build well, optimize later approach to programming. Build your app as well as you can, using the simplest means reasonable, and be confident that the app is more than good enough until it's objectively demonstrated not to be. So use the query cache until it's been proven insufficient, and then find a more efficient way to do it. But only worry about it if it's been DEMONSTRATED insufficient. cheers, barneyb On 12/7/05, Terry Ford [EMAIL PROTECTED] wrote: Honestly, you really ought not to care. The reason I care is that when you're using a large number of cached queries of differing (sometimes variable) sizes in a large, highly loaded, dynamic environment, it's not at all easy to know how many queries you ought to cache, nor what sort of durations you should place on each one. It would be quite insightful to know what the average size of a cached query over a period of time in the cache was, to better tune the number of queries option based on the amount of memory you're willing to dedicate to that task. As far as the question of performance, it is very important to understand the strengths and weaknesses of CF and Java's data types and caching mechanisms precisely so you can better choose between using one of them or rolling your own. Anyone truly interested in high performance and optimization ought to care. Regards, Terry -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| 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:226520 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: Cached queries memory usage
I'm a huge fan of the build well, optimize later approach to programming. Build your app as well as you can, using the simplest means reasonable, and be confident that the app is more than good enough until it's objectively demonstrated not to be. Yep, that's the approach I take too. I'm constantly looking to improve application response once something new is up and running. One of the best ways of determining how something will act in a real environment is to let it run in that real environment and see how it does act, and then optimize from there. I suppose that's why query caching has always bothered me as far as optimization is concerned, because it really is a great bit of functionality but is largely unobservable in realtime testing. So many other aspects of the process are measurable, such as using cfstat, observing mysql query times, timing iterative processes, etc. Query caching is one of CF's few enigmas, particularly when it comes to its relatively unpredictable and unobservable use of memory. If you are using your own caching mechanisms or storing lots of data in application/session scopes in addition to query caching, you might as well throw a dart at a board to determine how much memory your CF cached queries are really using. Terry If you really care that much, you're going to be doing some pretty in-depth load testing anyway, so you can vary the different cache amounts (query cache, other manual caches) and get app-specific results. And even then, it's still irrelevant what the absolute efficiency of the query cache is. It's a question of good enough that can only be answered by testing your specific app. No preexisting data will be meaningful, because your app will be different than whatever app was used to generate the data. I'm a huge fan of the build well, optimize later approach to programming. Build your app as well as you can, using the simplest means reasonable, and be confident that the app is more than good enough until it's objectively demonstrated not to be. So use the query cache until it's been proven insufficient, and then find a more efficient way to do it. But only worry about it if it's been DEMONSTRATED insufficient. cheers, barneyb On 12/7/05, Terry Ford [EMAIL PROTECTED] wrote: -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| 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:226523 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: Cached queries memory usage
Because of the way memory usage is related to JVM garbage collection and other processes, I agree with Barney that load testing of your whole app with your data is the best way to see if you have problems, rather than trying to put together a picture based on measurements of individual bits of the app. Packages like OpenSTA make this a repeatable and reproducible task. On 12/8/05, Terry Ford [EMAIL PROTECTED] wrote: I'm a huge fan of the build well, optimize later approach to programming. Build your app as well as you can, using the simplest means reasonable, and be confident that the app is more than good enough until it's objectively demonstrated not to be. Yep, that's the approach I take too. I'm constantly looking to improve application response once something new is up and running. One of the best ways of determining how something will act in a real environment is to let it run in that real environment and see how it does act, and then optimize from there. [snip] -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| 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:226527 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
Flushing Cached Queries
Okay, this is probably something obvious I'm either overlooking or not thinking of. For some reason, I can figure out the really complicated things far easier than the ones that should be simple. Anyway Is there a way to force CF 6.1 to update a cached query programatically. That is, is there a way to force the query to re-run even if the timeout specified in cachedwithin is not up yet. We've got a client who runs a database update routine at a certain time, and we want to try and run the query just before (to cache it across the update) and just after (to pick up the updated data). Ideas? --Ben ~| 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:222056 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: Flushing Cached Queries
set cachedwithin to 0? Okay, this is probably something obvious I'm either overlooking or not thinking of. For some reason, I can figure out the really complicated things far easier than the ones that should be simple. Anyway Is there a way to force CF 6.1 to update a cached query programatically. That is, is there a way to force the query to re-run even if the timeout specified in cachedwithin is not up yet. We've got a client who runs a database update routine at a certain time, and we want to try and run the query just before (to cache it across the update) and just after (to pick up the updated data). Ideas? --Ben ~| 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:222059 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: Flushing Cached Queries
If it's a specific query you can use cachedwithin=#creattimespan(0,0,0,0)# You might also consider cachedafter - sounds like it fits your problem. I have a blog on this very topic :) http://mkruger.cfwebtools.com/index.cfm/2005/10/13/safe.caching -Mark -Original Message- From: Ben Doom [mailto:[EMAIL PROTECTED] Sent: Monday, October 24, 2005 10:43 AM To: CF-Talk Subject: Flushing Cached Queries Okay, this is probably something obvious I'm either overlooking or not thinking of. For some reason, I can figure out the really complicated things far easier than the ones that should be simple. Anyway Is there a way to force CF 6.1 to update a cached query programatically. That is, is there a way to force the query to re-run even if the timeout specified in cachedwithin is not up yet. We've got a client who runs a database update routine at a certain time, and we want to try and run the query just before (to cache it across the update) and just after (to pick up the updated data). Ideas? --Ben ~| 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:222061 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: Flushing Cached Queries
Is there a way to force CF 6.1 to update a cached query programatically. There are a couple of potential options off the top of my head. If the times that the query is run are set, you could use a cachedwithin attribute to force it to expire after a certain period. If you want to force a flush, you could invoke the CF factory object and force it to flush all queries. To invoke the factory... cfset factory = CreateObject(java, coldfusion.server.ServiceFactory) You can CFDUMP the factory object to find the exact method to call, I don't remember what it is off hand. -Justin Scott ~| 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:222063 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: Flushing Cached Queries
after the database update use a cfobjectcache? http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b11.htm On 10/24/05, John Beynon [EMAIL PROTECTED] wrote: set cachedwithin to 0? Okay, this is probably something obvious I'm either overlooking or not thinking of. For some reason, I can figure out the really complicated things far easier than the ones that should be simple. Anyway Is there a way to force CF 6.1 to update a cached query programatically. That is, is there a way to force the query to re-run even if the timeout specified in cachedwithin is not up yet. We've got a client who runs a database update routine at a certain time, and we want to try and run the query just before (to cache it across the update) and just after (to pick up the updated data). Ideas? --Ben ~| 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:222065 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
cached Queries
Hi there, Is there away of telling if a query was generated from a cached query without setting a variable or anything?? Cheers Andy www.andyjarrett.co.uk ~| Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta http://www.houseoffusion.com/banners/view.cfm?bannerid=40 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182463 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: cached Queries
When you use the cached* attributes of cfquery, i think it displays the fact that it was cached in the space in debug where the query time is usually shown. Aaron - Original Message - From: Andy Jarrett [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 25, 2004 8:56 AM Subject: cached Queries Hi there, Is there away of telling if a query was generated from a cached query without setting a variable or anything?? Cheers Andy www.andyjarrett.co.uk ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=35 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182464 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
ColdFusion TechNote Notification: Behavior of cached queries whe n maximum number is set to 0
Check out the new TechNote at: http://www.macromedia.com/support/coldfusion/ts/documents/cached_queries_max _zero.htm http://www.macromedia.com/support/coldfusion/ts/documents/cached_queries_ma x_zero.htm Intro: Cached queries within Macromedia ColdFusion MX are used to retrieve data result sets from server memory rather than through a database transaction. Because cached queries are stored in server memory, the ColdFusion Administrator allows a user imposed limit to the number of queries that are cached. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: ColdFusion TechNote Notification: Behavior of cached queries whe n maximum number is set to 0
Can these emails be set up to use short URLs, that will fit in a email message?Surely setting up a single RewriteRule in the apache config could turn them into links like this: http://www.macromedia.com/cftn/cached_queries_max_zero Not to say I don't appreaciate them, just it's a pain having to manually assemble the URL every time. Cheers, barneyb -Original Message- From: Debbie Dickerson [mailto:[EMAIL PROTECTED] Sent: Monday, June 28, 2004 11:58 AM To: CF-Talk Subject: ColdFusion TechNote Notification: Behavior of cached queries whe n maximum number is set to 0 Check out the new TechNote at: http://www.macromedia.com/support/coldfusion/ts/documents/cach ed_queries_max _zero.htm http://www.macromedia.com/support/coldfusion/ts/documents/cac hed_queries_ma x_zero.htm [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Cached queries
Just stick the query object in a shared scope.If it's user specific, then the session scope would be the way to go, otherwise application. cfquery ... Name=get SELECT ... /cfquery cfset application.myQuery = get / Just keep in mind that the query will not expire until the application scope expired (two days without a request by default, I believe).So you may want to store an additional value (myQueryCreateDate) that stores when it was created.Each request check that and see if it's more than X minutes (seconds, hours, days) ago, and if it is, rerun the query. Cheers, barneyb -Original Message- From: Les Irvin [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 19, 2004 2:29 PM To: CF-Talk Subject: Cached queries I would like to carry query results across a number of pages as users move through the site without having to perform the query on each page. I understand persistant queries with the cachewithin attribute but that's not possible with this query as it includes an embedded CFQUERYPARAM tag. What's the best, least CPU-intensive solution to this problem? Thanks in advance for the help, Les [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached queries
From: Les Irvin I would like to carry query results across a number of pages as users move through the site without having to perform the query on each page. I understand persistant queries with the cachewithin attribute but that's not possible with this query as it includes an embedded CFQUERYPARAM tag. What's the best, least CPU-intensive solution to this problem? Use the CFQUERYPARAM and then store the query in the Application scope Best of both worlds [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Cached queries
I would like to carry query results across a number of pages as users move through the site without having to perform the query on each page. I understand persistant queries with the cachewithin attribute but that's not possible with this query as it includes an embedded CFQUERYPARAM tag. What's the best, least CPU-intensive solution to this problem? Thanks in advance for the help, Les [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cached Queries
On Tuesday 06 Apr 2004 19:09 pm, Dave Watts wrote: How many users are you serving from your desktop? A couple :-} There are many reasons why queries may run slowly or quickly, of course. I suppose all you can really do is benchmark the various methods in your own app... -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
There are many reasons why queries may run slowly or quickly, of course. I suppose all you can really do is benchmark the various methods in your own app... This, of course, is the secret to success with any web application. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Cached Queries
I have an app with a front end (the main site that users see) and a backend (an admin app for updating articles, calendar events, etc).On the front end, I want to use cached queries to make the performance better.However, if I use cached queries and an admin user goes into the backend and adds a new article, then goes and views the front end, he won't see the new update automatically, right?How could I (in my admin code) flush the cache for that particular query or set of queries? >From what I've gathered from previous posts on the list about caching queries in CF, the cache depends on the exact sql syntax and the application name...I think.Is that correct?Any tips on how to optimize my queries for fast caching while also being able to update the live site immediately with new info inserted through CF on the backend would be extremely helpful!Thanks! John Burns [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
You can use cfobjectcache action = "" to clear all cached queries Qasim -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 9:53 AM To: CF-Talk Subject: Cached Queries I have an app with a front end (the main site that users see) and a backend (an admin app for updating articles, calendar events, etc).On the front end, I want to use cached queries to make the performance better.However, if I use cached queries and an admin user goes into the backend and adds a new article, then goes and views the front end, he won't see the new update automatically, right?How could I (in my admin code) flush the cache for that particular query or set of queries? >From what I've gathered from previous posts on the list about caching queries in CF, the cache depends on the exact sql syntax and the application name...I think.Is that correct?Any tips on how to optimize my queries for fast caching while also being able to update the live site immediately with new info inserted through CF on the backend would be extremely helpful!Thanks! John Burns _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
Is there any way to clear just individual queries?Would the best method for doing that be to execute the same query and give it a new cachedwithin value of #CreateTimeSpan(0,0,0,0)# to kill the cache from the backend and then when the frontend query is called again, it will reset the cache?I'm just curious how CF differentiates cached queries. If the front end has one application name and the backend has another, does it handle the cached queries separate even though they're the exact same query? John -Original Message- From: Qasim Rasheed [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 9:58 AM To: CF-Talk Subject: RE: Cached Queries You can use cfobjectcache action = "" to clear all cached queries Qasim -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 9:53 AM To: CF-Talk Subject: Cached Queries I have an app with a front end (the main site that users see) and a backend (an admin app for updating articles, calendar events, etc).On the front end, I want to use cached queries to make the performance better.However, if I use cached queries and an admin user goes into the backend and adds a new article, then goes and views the front end, he won't see the new update automatically, right?How could I (in my admin code) flush the cache for that particular query or set of queries? >From what I've gathered from previous posts on the list about caching queries in CF, the cache depends on the exact sql syntax and the application name...I think.Is that correct?Any tips on how to optimize my queries for fast caching while also being able to update the live site immediately with new info inserted through CF on the backend would be extremely helpful!Thanks! John Burns _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
You can use cfobjectcache action = "" to clear all cached queries I'm not sure that's what he's after. If you use the above, you blow away the entire cache, instead of just the query or queries that you want to refresh. You could store the query results in the Application scope (or some shared scope) and then refresh the key in the scope when necessary with the new results. Something like: cfif I want to update my query cfset structDelete(Application, myQuery, No) / /cfif cfif NOT structKeyExists(Application, myQuery) cfquery name=Application.myQuery datasource... /cfif If you're using the Session scope for a personalized query, then just change Application to Session in the above code. Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
Is there any way to clear just individual queries?Would the best method for doing that be to execute the same query and give it a new cachedwithin value of #CreateTimeSpan(0,0,0,0)# to kill the cache from the backend and then when the frontend query is called again, it will reset the cache?I'm just curious how CF differentiates cached queries. If the front end has one application name and the backend has another, does it handle the cached queries separate even though they're the exact same query? While setting a createTimeSpan value of 0 will indeed work, the results won't take effect until the *next* page request, I think. In the above scenario, you'd be relying on somebody hitting that page again before showing the proper results (even if you just hit Refresh in your browser, you still have to load the page twice to achieve the desired result). Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cached Queries
John I did exactly this on a site with news articles.You want to used cached after with on the Articles query the date-time stored as an application variable, e..g., Articles,CachedAfterDateTime, When an admin updates an article reset the Articles,CachedAfterDateTime variable to now() -- forces the next query to re-cache. Simple, but works well! HTH Dick On Apr 6, 2004, at 6:52 AM, Burns, John D wrote: I have an app with a front end (the main site that users see) and a backend (an admin app for updating articles, calendar events, etc).  On the front end, I want to use cached queries to make the performance better.  However, if I use cached queries and an admin user goes into the backend and adds a new article, then goes and views the front end, he won't see the new update automatically, right?  How could I (in my admin code) flush the cache for that particular query or set of queries? From what I've gathered from previous posts on the list about caching queries in CF, the cache depends on the exact sql syntax and the application name...I think.  Is that correct?  Any tips on how to optimize my queries for fast caching while also being able to update the live site immediately with new info inserted through CF on the backend would be extremely helpful!  Thanks! John Burns [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
Well, my thought was as follows: People visit the front end and set the query into cache. More people visit the front end and see cached query. Admin user goes into backend and inserts a new record. At the bottom of the code where I insert the new record, I do the select query and set createTimeSpan value to 0. Admin user goes to the front end and loads the page. At the last step, will that not re-call the query and then cache it with the correct timespan? Will all of this work if my front end and back end are in 2 different application names? (Application.cfm in each with a cfapplication name=frontend for the front end and cfapplication name=backend for the back end)Is there any way to clear caches outside in a different application than you're currently in? John -Original Message- From: Dave Carabetta [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 10:19 AM To: CF-Talk Subject: RE: Cached Queries Is there any way to clear just individual queries?Would the best method for doing that be to execute the same query and give it a new cachedwithin value of #CreateTimeSpan(0,0,0,0)# to kill the cache from the backend and then when the frontend query is called again, it will reset the cache?I'm just curious how CF differentiates cached queries. If the front end has one application name and the backend has another, does it handle the cached queries separate even though they're the exact same query? While setting a createTimeSpan value of 0 will indeed work, the results won't take effect until the *next* page request, I think. In the above scenario, you'd be relying on somebody hitting that page again before showing the proper results (even if you just hit Refresh in your browser, you still have to load the page twice to achieve the desired result). Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cached Queries
On Tuesday 06 Apr 2004 14:52 pm, Burns, John D wrote: the backend and adds a new article, then goes and views the front end, he won't see the new update automatically, right?How could I (in my If you use cfqueryparam, you don't need to worry about caching your querys yourself. Oh, and it's more secure. Oh, and quicker. -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
While setting a createTimeSpan value of 0 will indeed work, the results won't take effect until the *next* page request, I think. In the above scenario, you'd be relying on somebody hitting that page again before showing the proper results (even if you just hit Refresh in your browser, you still have to load the page twice to achieve the desired result). No, I'm pretty sure that once you run the query with CACHEDWITHIN set to a timespan of zero, that'll remove the previous cached query from the cache. Someone will need to rerun the query against the database to recache it, though. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
If you use cfqueryparam, you don't need to worry about caching your querys yourself. Oh, and it's more secure. Oh, and quicker. While I just love the CFQUERYPARAM tag to death, it's not a substitute for in-memory recordset caching. It doesn't cache recordsets in memory, and therefore isn't quicker when that recordset can be reused. Unfortunately, you can't use CFQUERYPARAM in conjunction with CACHEDWITHIN/CACHEDAFTER, but you can cache recordsets in Session/Application/Server scopes instead. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
cfqueryparam makes it so your query is cached automatically?These are static queries written directly in CF with no CF variables being passed in. For instance: cfquery name=getArticles datasource=#dsn# select * from Articles LIMIT 5 /cfquery Or at the most, for a specific article with an ID passed through the URL cfquery name=getArticle datasource=#dsn# select * from Articles where articleID = #articleID# /cfquery I can see using cfqueryparam in the second instance, but I don't see how it helps in the first and I don't see how using it caches my queries or improves performance.I looked at the CFDOCS and don't see any info about it auto-caching when using cfqueryparam.Any insight would be appreciated. John -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 10:33 AM To: CF-Talk Subject: Re: Cached Queries On Tuesday 06 Apr 2004 14:52 pm, Burns, John D wrote: the backend and adds a new article, then goes and views the front end, he won't see the new update automatically, right?How could I (in my If you use cfqueryparam, you don't need to worry about caching your querys yourself. Oh, and it's more secure. Oh, and quicker. -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
Is there any way to clear just individual queries?Would the best method for doing that be to execute the same query and give it a new cachedwithin value of #CreateTimeSpan(0,0,0,0)# to kill the cache from the backend and then when the frontend query is called again, it will reset the cache? Yes, that works perfectly fine. In fact, if you want to avoid having the frontend user wait for the database, you can flush the query as you described above, then rerun it with the appropriate cache time again in your backend page. I'm just curious how CF differentiates cached queries. If the front end has one application name and the backend has another, does it handle the cached queries separate even though they're the exact same query? CF compares the exact SQL statement sent to the database, and compares all of the attribute values for CFQUERY except for CACHEDWITHIN/CACHEDAFTER. If anything is different, CF will run the query against the database rather than use the previously cached recordset. In some versions of CF, the exact SQL statement even includes whitespace, such as tabs, within the CFQUERY block. CF doesn't use the application name, just the attributes described above. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
No, I'm pretty sure that once you run the query with CACHEDWITHIN set to a timespan of zero, that'll remove the previous cached query from the cache. Someone will need to rerun the query against the database to recache it, though. That was my point (however unclear it may have been!), if I'm reading your post correctly. When you change the cachedwithin value to 0, the query does not refresh itself on that specific call (because you've just changed the value and the query is still cached). It doesn't get run and re-cached until the *next* page request (which is usually when that query is called again, unless you run a cfhttp request to programatically call that page again or something similar). Or did I misunderstand? Regards, Dave. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
cfqueryparam makes it so your query is cached automatically? No, it doesn't. I can see using cfqueryparam in the second instance, but I don't see how it helps in the first and I don't see how using it caches my queries or improves performance.I looked at the CFDOCS and don't see any info about it auto-caching when using cfqueryparam.Any insight would be appreciated. When you use CFQUERYPARAM, the database will generally cache query execution plans, which can speed up future database queries using the same plan. It doesn't have anything to do with caching recordsets in memory on your CF server, though, and in fact you can't use CFQUERYPARAM with CACHEDWITHIN/CACHEDAFTER. For this reason, you might consider caching recordsets within persistent memory variable scopes (Session/Application/Server) instead. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
No, I'm pretty sure that once you run the query with CACHEDWITHIN set to a timespan of zero, that'll remove the previous cached query from the cache. Someone will need to rerun the query against the database to recache it, though. That was my point (however unclear it may have been!), if I'm reading your post correctly. When you change the cachedwithin value to 0, the query does not refresh itself on that specific call (because you've just changed the value and the query is still cached). It doesn't get run and re-cached until the *next* page request (which is usually when that query is called again, unless you run a cfhttp request to programatically call that page again or something similar). Or did I misunderstand? I'm sorry, I must have misunderstood you. Changing the CACHEDWITHIN value to zero causes the previous recordset to be overwritten with a new one fetched from the database, which is itself immediately discarded since it has a cache time of zero. The net result is that the query is no longer cached. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cached Queries
On Tuesday 06 Apr 2004 16:00 pm, Dave Watts wrote: When you use CFQUERYPARAM, the database will generally cache query execution plans, which can speed up future database queries using the same plan. Hmm... :tests I'll typical get consistant ~3ms query times using cfqueryparam, compaired to much longer without. Of course, caching into a persistant scope will get you a 0ms query time, but then you have to manage the cache (I really must write a generic memoiser CFC) yourself. And if you're worried about gaining 3ms/query I think you have too many queries on the page :-) -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
I'll typical get consistant ~3ms query times using cfqueryparam, compaired to much longer without. Of course, caching into a persistant scope will get you a 0ms query time, but then you have to manage the cache (I really must write a generic memoiser CFC) yourself. And if you're worried about gaining 3ms/query I think you have too many queries on the page :-) Unfortunately, even with consistent use of CFQUERYPARAM, many of us will have longer query times than 3 milliseconds. An aggressive caching strategy is often the best way to significantly increase runtime performance. In those cases, you do have to manage the cache, but that's a small price to pay for significantly better performance. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cached Queries
On Tuesday 06 Apr 2004 17:09 pm, Dave Watts wrote: Unfortunately, even with consistent use of CFQUERYPARAM, many of us will have longer query times than 3 milliseconds. Really ?!? This is on a very loaded desktop dev box, to a remote Oracle, that isn't on the best hardware either... -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44(0)1749 834997 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834901 web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG. *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
Unfortunately, even with consistent use of CFQUERYPARAM, many of us will have longer query times than 3 milliseconds. Really ?!? This is on a very loaded desktop dev box, to a remote Oracle, that isn't on the best hardware either... How many users are you serving from your desktop? There are many reasons why queries may run slowly or quickly, of course. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Clearing all Cached Queries (Attn: Ray C.)
Ray, You told me once of a way to clear out all cached queries at once, but I'll be darned if I can find the thread. Would you please repeat? Thanks, Jamie [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Clearing all Cached Queries (Attn: Ray C.)
You told me once of a way to clear out all cached queries at once, but I'll be darned if I can find the thread. I'm not Ray, but: cfobjectcache action=""> Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Clearing all Cached Queries (Attn: Ray C.)
cfobjectcache if I am correct Qasim -Original Message- From: Jamie Jackson [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 1:43 PM To: CF-Talk Subject: Clearing all Cached Queries (Attn: Ray C.) Ray, You told me once of a way to clear out all cached queries at once, but I'll be darned if I can find the thread. Would you please repeat? Thanks, Jamie _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Cached Queries/Cf Admin
Is there any rule of thumb or method for figuring out how to optimize the number of queries to cache setting in Administrator? Thanks, Scott [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries/Cf Admin
From: Scott Mulholland Is there any rule of thumb or method for figuring out how to optimize the number of queries to cache setting in Administrator? There is no hard and fast setting for this It really depends on the applications you have running on your server, how many queries you have cached, how many variations in the SQL each has, etc. Remember that; SELECT * FROM TABLE WHERE FIELD=1 Is different from SELECT * FROM TABLE WHERE FIELD=2 Each datasource is also another set of queries Remember to ensure that you have enough memory in your server to handle all of the queries, or it'll drop them sooner [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Cached Queries
Are cached-queries cfapplication-name specific?I've got a web site running a cf application (named myapp) and a related administrative application on another web site (also myapp for the moment).I'd like to change the name of the admin app so that admins aren't automatically logged in to the application on the web site proper (make sense?).If the admin app with a different cfapplication name forces the reloading of cached queries, should they carry over to the other cfapplication? Or are cached queries identified by query name and exist across the entire physical CF server? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
cached queries, via cachedwithin or cachedafter, are identified by their signature which is composed of query name, SQL, etc..I forget the exact qualities.These are cached regardless of the presence of a cfapplication, else you would be required to use one when caching. Doug -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Friday, January 30, 2004 1:31 PM To: CF-Talk Subject: Cached Queries Are cached-queries cfapplication-name specific?I've got a web site running a cf application (named myapp) and a related administrative application on another web site (also myapp for the moment).I'd like to change the name of the admin app so that admins aren't automatically logged in to the application on the web site proper (make sense?).If the admin app with a different cfapplication name forces the reloading of cached queries, should they carry over to the other cfapplication? Or are cached queries identified by query name and exist across the entire physical CF server? _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Cached Queries
Are cached-queries cfapplication-name specific? No, they're not. They're identified by the SQL statement and the attributes of the CFQUERY tag, excluding the CACHEDWITHIN/CACHEDAFTER attribute itself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Cached Queries
Thanks, Doug.That makes sense. - Original Message - From: [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, January 30, 2004 11:35 AM Subject: RE: Cached Queries cached queries, via cachedwithin or cachedafter, are identified by their signature which is composed of query name, SQL, etc..I forget the exact qualities.These are cached regardless of the presence of a cfapplication, else you would be required to use one when caching. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Clearing all Cached Queries at Once?
It's a pretty simple matter to clear all the queries accessed in a request, say by setting a clearcache url var, and writing the code to set the cache span to a nil duration when url.clearcache exists. Q1. However, what if I'd like to clear *all* cached queries in the entire application in one fell swoop? (Without a server restart ;-) Q2. Also, say I've got a dynamic query... cfquery ...select * from t where c = #v#/cfquery ... and I clear the query when v = 1, does this all-at-once clear the other cached versions (which were cached when v equaled 2 and v equaled 3)? Thanks, Jamie [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Clearing all Cached Queries at Once?
Q1. However, what if I'd like to clear *all* cached queries in the entire application in one fell swoop? (Without a server restart ;-) cfobjectcache action=""> This clears the entire server's set of cached queries. Q2. Also, say I've got a dynamic query... cfquery ...select * from t where c = #v#/cfquery ... and I clear the query when v = 1, does this all-at-once clear the other cached versions (which were cached when v equaled 2 and v equaled 3)? No, cached queries are indexed by their sql and the other attributes to the tag. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Clearing all Cached Queries at Once?
Brilliant, thanks. Jamie On Tue, 6 Jan 2004 12:47:04 -0600, in cf-talk you wrote: Q1. However, what if I'd like to clear *all* cached queries in the entire application in one fell swoop? (Without a server restart ;-) cfobjectcache action=""> This clears the entire server's set of cached queries. Q2. Also, say I've got a dynamic query... cfquery ...select * from t where c = #v#/cfquery ... and I clear the query when v = 1, does this all-at-once clear the other cached versions (which were cached when v equaled 2 and v equaled 3)? No, cached queries are indexed by their sql and the other attributes to the tag. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Custom tag to see list of cached queries!
Hello, I have written a CFX custom tag for CFMX, called cfx_queryCache, which allows you to get a list of all your cached queries and flush individual queries based on its key. I'd be interested in any ideas for improvements, both functionality and improving the Java code. It's open-source and available from http://tamuri.cfdeveloper.co.uk/ Thanks, Tamuri [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
How do I flush cached queries?
I have tried using the undocumented cfset CFUSION_DBCONNECTIONS_FLUSH()but it is not flushing query results and forcing a new query (I want to flush the cached queryies whenever an administrator updates info in the database).Is there any other way to flush cached query results?I am using CF5 on Win2K with MS SQL Server 2000. Thanks Pete [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How do I flush cached queries?
I thought that DBCOMMECTIONS_FLUSH only flushed the ODBC connectors, not the cached queries Anyways, the way I do it is to put a CreateTimeSpan(0,0,0,0) in the CACHEDWITHIN, that tells CF to get the latest data If you're using CACHEDWITHIN, then I'd suggest putting the time span into a variable, and then when a certain URL condition is met, then set the time span to 0 - thus flushing all queries -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 9:03 AM To: CF-Talk Subject: How do I flush cached queries? I have tried using the undocumented cfset CFUSION_DBCONNECTIONS_FLUSH()but it is not flushing query results and forcing a new query (I want to flush the cached queryies whenever an administrator updates info in the database).Is there any other way to flush cached query results?I am using CF5 on Win2K with MS SQL Server 2000. Thanks Pete _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How do I flush cached queries?
cfobjectcache action=""> Good luck, -JSLucido -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 8:03 AM To: CF-Talk Subject: How do I flush cached queries? I have tried using the undocumented cfset CFUSION_DBCONNECTIONS_FLUSH()but it is not flushing query results and forcing a new query (I want to flush the cached queryies whenever an administrator updates info in the database).Is there any other way to flush cached query results?I am using CF5 on Win2K with MS SQL Server 2000. Thanks Pete _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How do I flush cached queries?
Oops, I am pretty sure this works in CF5 but I can't remember if I used it back then or not. This definitely works in MX. Also, the cachewithin attribute is pretty much a must unless you have a query you want to keep indefinitely (be nice to your RAM memory, it is your friend). Another point, this will clear all queries in the cache so it may be overkill. The previous suggestion about setting the cachewithin attribute to '0' is a surefire way to kill the cache on just a single query. S. Isaac (who is lurking on this list daily) has some pretty neat ways to cache queries within his framework, onTap. Good luck, -JSLucido -Original Message- From: Jeff Lucido [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 8:20 AM To: CF-Talk Subject: RE: How do I flush cached queries? cfobjectcache action=""> Good luck, -JSLucido -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 8:03 AM To: CF-Talk Subject: How do I flush cached queries? I have tried using the undocumented cfset CFUSION_DBCONNECTIONS_FLUSH()but it is not flushing query results and forcing a new query (I want to flush the cached queryies whenever an administrator updates info in the database).Is there any other way to flush cached query results?I am using CF5 on Win2K with MS SQL Server 2000. Thanks Pete _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: How do I flush cached queries?
I have the following query, however if I enter more than one value in the search form such as
Surname = Vaughan
Funding = budget
CFQUERY datasource=liv8 name=funding
SELECT * FROM funding
WHERE 1=1
cfif Len(Trim(form.orgname))
AND UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=%#form.orgname#%)
cfelseif Len(Trim(form.funding))
AND UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=%#form.funding#%)
cfelseif Len(Trim(form.surname))
AND UPPER(surname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=%#form.surname#%)
cfelseif IsNumeric(form.commyearfrom) AND IsNumeric(form.commmonthfrom)
AND IsNumeric(form.commdayfrom) AND IsNumeric(form.commyearto) AND
IsNumeric(form.commmonthto) AND IsNumeric(form.commdayto)
AND IssueDate BETWEEN
TO_DATE('#form.commyearfrom#-#form.commmonthfrom#-#form.commdayfrom#','-MM-DD')
AND
TO_DATE('#form.commyearto#-#form.commmonthto#-#form.commdayto#','-MM-DD')
/cfif
/CFQUERY
--
This is the output from debugging
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: How do I flush cached queries?
The problem is with caching dynamic queries.If you cache a query that has dynamic statements, it will cache the result set from each dynamic statement that is run. I'll try Jeff's solution. Thanks for the help! Pete - Original Message - From: Philip Arnold To: CF-Talk Sent: Friday, October 10, 2003 9:18 AM Subject: RE: How do I flush cached queries? I thought that DBCOMMECTIONS_FLUSH only flushed the ODBC connectors, not the cached queries Anyways, the way I do it is to put a CreateTimeSpan(0,0,0,0) in the CACHEDWITHIN, that tells CF to get the latest data If you're using CACHEDWITHIN, then I'd suggest putting the time span into a variable, and then when a certain URL condition is met, then set the time span to 0 - thus flushing all queries -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 9:03 AM To: CF-Talk Subject: How do I flush cached queries? I have tried using the undocumented cfset CFUSION_DBCONNECTIONS_FLUSH()but it is not flushing query results and forcing a new query (I want to flush the cached queryies whenever an administrator updates info in the database).Is there any other way to flush cached query results?I am using CF5 on Win2K with MS SQL Server 2000. Thanks Pete _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How do I flush cached queries?
Thanks Jeff, I'm not certain (off the top of my head) if query caching was really included in the onTap framework. The method I usually use is to create a structure to hold dates for caching in the application scope. Any time a given query is run, it uses cfparam to ensure a date value for that query with the current time as a default for the cachedafter attribute. Any time data in the relevant tables is updated, I simply set the value of the application variable to now() and this forces the application to return to the database for the next query. If you're concerned about preserving RAM, this isn't going to expire the queries quickly, since it will wait for the application to expire before all queries are refreshed. My opinion about memory usage for data caching as a general rule is this: it's less expensive than more/faster processors. I have yet to have any legitimate problems caused by the amount of available RAM on a server -- although oddly enough I did once run into a problem with one client who didn't have enough physical storage (hd space) on a server. ;P Iirc that was caused largely by the frequency and longevity of their database backups -- creating them too often and keeping them too long. Because most of the queries in my CMS application are from stored procedures, cachedwithin isn't available for those, so I've created similar caching routines for storing structures and other types of data. There's a content.cfm custom tag in the framework that stores strings of html output in the application scope and uses cachedwithin and cachedafter as attributes by using datediff() to compare now() with the timestamp for the given bit of content. Lately lurking is probably fairly appropriate unfortunately. I've been pretty busy trying to wrap up a client project and starting a new project that have been taking up much of my time. I lost the transmission in my car, and to add insult to injury, my apartment complex poisoned me. They laquered or painted something in the apartment next door and it's been ventilating into my unit, directly over my desk. I've been prone to ear infections in the past, so when I started getting dizzy I didn't think much of it. I breathed it in for a little over a day before I passed out and we realized what was happening. I'm staying at a friend's place right now and working from here, but it's like pulling teeth trying to get any kind of recompense from the complex for the fact that they've injured me both physically and financially. All they want to do is wave their hands in the air and say it's fine now. s. isaac dealey 972-490-6624 team macromedia volunteerhttp://www.macromedia.com/go/team chief architect, tapestry cmshttp://products.turnkey.to onTap is open sourcehttp://www.turnkey.to/ontap Oops, I am pretty sure this works in CF5 but I can't remember if I used it back then or not. This definitely works in MX. Also, the cachewithin attribute is pretty much a must unless you have a query you want to keep indefinitely (be nice to your RAM memory, it is your friend). Another point, this will clear all queries in the cache so it may be overkill. The previous suggestion about setting the cachewithin attribute to '0' is a surefire way to kill the cache on just a single query. S. Isaac (who is lurking on this list daily) has some pretty neat ways to cache queries within his framework, onTap. Good luck, -JSLucido -Original Message- From: Jeff Lucido [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 8:20 AM To: CF-Talk Subject: RE: How do I flush cached queries? cfobjectcache action=""> Good luck, -JSLucido -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 8:03 AM To: CF-Talk Subject: How do I flush cached queries? I have tried using the undocumented cfset CFUSION_DBCONNECTIONS_FLUSH()but it is not flushing query results and forcing a new query (I want to flush the cached queryies whenever an administrator updates info in the database).Is there any other way to flush cached query results?I am using CF5 on Win2K with MS SQL Server 2000. Thanks Pete _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Flushing All Cached Queries
When using the cfobjectcache action=clear, Does it flush just the cached queries in the application, or on all applications across the server? Thanks, Mark ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Flushing All Cached Queries
When using the cfobjectcache action=clear, Does it flush just the cached queries in the application, or on all applications across the server? It flushes all cached queries on the server. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flushing All Cached Queries
Supposedly...I've never found that to be the case though. It only seems to work at a template level for me, refreshing any cached queries that are executed after the cfobjectcache statement during the request. Any other cached queries for that application remain cached, unaffected by cfobjectcache. Any cached queries that are executed before the cfobjectcache statement will refresh the next time the query is executed. This applies to CF5, I don't know about MX. If there is a trick I am missing I'd love to know about it. -- jon mailto:[EMAIL PROTECTED] Thursday, February 27, 2003, 5:05:28 PM, you wrote: When using the cfobjectcache action=clear, Does it flush just the cached queries in the application, or on all applications across the server? DW It flushes all cached queries on the server. DW Dave Watts, CTO, Fig Leaf Software DW http://www.figleaf.com/ DW voice: (202) 797-5496 DW fax: (202) 797-5444 DW ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Flushing All Cached Queries
Supposedly...I've never found that to be the case though. It only seems to work at a template level for me, refreshing any cached queries that are executed after the cfobjectcache statement during the request. Any other cached queries for that application remain cached, unaffected by cfobjectcache. Any cached queries that are executed before the cfobjectcache statement will refresh the next time the query is executed. This applies to CF5, I don't know about MX. If there is a trick I am missing I'd love to know about it. That's odd. I remember you mentioning this before. I just built a very simple test case, consisting of two separate pages, one containing a CFQUERY with CACHEDWITHIN and the other containing CFOBJECTCACHE. I then turned on debugging, and ran the query page twice (the second time to cache the query), then ran the second page, then reran the query page, and the query was no longer cached. I did this under CF 5 and CFMX. It worked under both. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Flushing All Cached Queries
-Original Message- From: jon hall [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 5:21 PM To: CF-Talk Subject: Re: Flushing All Cached Queries Supposedly...I've never found that to be the case though. It only seems to work at a template level for me, refreshing any cached queries that are executed after the cfobjectcache statement during the request. Any other cached queries for that application remain cached, unaffected by cfobjectcache. Any cached queries that are executed before the cfobjectcache statement will refresh the next time the query is executed. This applies to CF5, I don't know about MX. If there is a trick I am missing I'd love to know about it. I just tested this on CF 5.0 and CFMX, and I got the same behavior. CFOBJECTCACHE clears all queries that are cached with cachedwithin, no matter what application they belong to. I created a directory structure like so: codetesting objectcache app1 app2 App1 and App2 had their own application.cfm and index.cfm files. App1 was named CodeTestingApp1, App2 was named CodeTestingApp2. I used the same queries in each index.cfm page first, then I tested with two different queries. Each time I ran the test, both queries had different names. Performing a cfobjectcache action=clear in the index.cfm in App2 after all other cfml, the query was never cached, as expected. After running App1 to cache the query, running App2 causes a different query to be run and after, the object cache is cleared. Running App1 right after this shows that cfobjectcache did indeed clear the cached query in App1. I then tested again with queries cached in the Application scope, not using cachedwithin. CFOBJECTCACHE does not affect queries cached in the Application scope, it seems; it only affects queries cached with cachedwithin and perhaps (not tested) cachedafter. The documentation on this tag is somewhat confusing. The action=CLEAR attribute says it Clears queries from the cache in the Application scope, while the actual Description of the tag says Flushes the query cache. Since this is obviously not all true (cfobjectcache does NOT clear queries in the Application scope, based on my testing in CF 5.0 and CFMX), the docs on this tag don't appear to be fully correct. The Livedocs on this tag are the same as the ones I have installed in CF Studio 4.5.2. --Andy ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flushing All Cached Queries
Thursday, February 27, 2003, 6:48:53 PM, you wrote: Supposedly...I've never found that to be the case though. It only seems to work at a template level for me, refreshing any cached queries that are executed after the cfobjectcache statement during the request. Any other cached queries for that application remain cached, unaffected by cfobjectcache. Any cached queries that are executed before the cfobjectcache statement will refresh the next time the query is executed. This applies to CF5, I don't know about MX. If there is a trick I am missing I'd love to know about it. DW That's odd. I remember you mentioning this before. I just built a very DW simple test case, consisting of two separate pages, one containing a CFQUERY DW with CACHEDWITHIN and the other containing CFOBJECTCACHE. I then turned on DW debugging, and ran the query page twice (the second time to cache the DW query), then ran the second page, then reran the query page, and the query DW was no longer cached. I did this under CF 5 and CFMX. It worked under both. Running a quick test case seemed to confirm that is does indeed work as it should using cachedwithin queries. Running the test case though I found out that apparently CF ignores the name attribute of a cached query when deciding whether to return a cached recordset it and only uses the sql. Learn something new every day... This may have been the cause of my confusion, as the only place I have tested cfobjectcache was on a server with a bunch of nearly identical applications. So i would go from site to site seeing the queries cached, even though the page had never been executed. For now, I guess I'll have to take back my post. :) -- jon mailto:[EMAIL PROTECTED] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Cached Queries
I have a bunch of code that I don't want to execute if a query is cached. I tried this cfif NOT isdefined(cachedQueryName) ... whole lotta code, then the query occurs cfquery name=cachedQueryName /cfquery cfelse ... skip all that code /cfif and it still doesn't seem to skip over the code. Basically all the code does some cfhttp calls and creates a query, which I then do a query of a query and cache that one. I only need all the code to run once a day (because it takes some time). I don't want it to run every call to the page. I guess the question is, is there some variable in the server scope that lists the names of the cached queries? Byron ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Cached Queries
cfquery.executionTime might be useful. In my experience it is 0 for cached queries. However it might conceivably return 0 for non-cached queries too. Matthew Walker http://www.matthewwalker.net.nz/ -Original Message- From: Byron M [mailto:mbyron;comcast.net] Sent: Tuesday, 29 October 2002 10:37 a.m. To: CF-Talk Subject: Cached Queries I have a bunch of code that I don't want to execute if a query is cached. I tried this cfif NOT isdefined(cachedQueryName) ... whole lotta code, then the query occurs cfquery name=cachedQueryName /cfquery cfelse ... skip all that code /cfif and it still doesn't seem to skip over the code. Basically all the code does some cfhttp calls and creates a query, which I then do a query of a query and cache that one. I only need all the code to run once a day (because it takes some time). I don't want it to run every call to the page. I guess the question is, is there some variable in the server scope that lists the names of the cached queries? Byron ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: Cached Queries
The problem seems to be that none of the query information is available until the line in the code where the cfquery statement is. Therefore, I can't figure out a good way to skip over all the code. I would use an application variable to flag weather the query is current cached or not, and the time since it was first cached, but all this code is in a .cfc and I want to keep it portable as possible. Byron Mann - Original Message - From: Matthew Walker [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 28, 2002 4:47 PM Subject: RE: Cached Queries cfquery.executionTime might be useful. In my experience it is 0 for cached queries. However it might conceivably return 0 for non-cached queries too. Matthew Walker http://www.matthewwalker.net.nz/ -Original Message- From: Byron M [mailto:mbyron;comcast.net] Sent: Tuesday, 29 October 2002 10:37 a.m. To: CF-Talk Subject: Cached Queries I have a bunch of code that I don't want to execute if a query is cached. I tried this cfif NOT isdefined(cachedQueryName) ... whole lotta code, then the query occurs cfquery name=cachedQueryName /cfquery cfelse ... skip all that code /cfif and it still doesn't seem to skip over the code. Basically all the code does some cfhttp calls and creates a query, which I then do a query of a query and cache that one. I only need all the code to run once a day (because it takes some time). I don't want it to run every call to the page. I guess the question is, is there some variable in the server scope that lists the names of the cached queries? Byron ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Cached Queries
Nothing wrong with a cfc having access to persistent data...I'd say an
app variable is the best way to do this. Just make the cfc test to see
if 24 hours have passed between refreshes. Application variables
aren't the only way to make data persist...you could use a db, or the
filesystem.
Some skeleton code I did for something similar in CF5.
cfset doupdate = 0
cfset variables.updateInterval = 15
!--- cfset application.nextupdate = dateadd('n',1,now()) In case
you need to manually flush cache ---
cflock timeout=5 type=EXCLUSIVE scope=APPLICATION
cfif NOT isDefined(application.nextupdate)
cfset application.nextupdate = now()
/cfif
/cflock
cflock timeout=5 throwontimeout=No type=READONLY scope=APPLICATION
cfif dateDiff('n',now(),application.nextupdate) LTE 0
cfset doupdate = 1
/cfif
/cflock
If doupdate equals one, run your query, otherwise don't. I can imagine
putting the above check in the constructor code, and setting a
obj.doupdate property...
--
jon
mailto:jonhall;ozline.net
Monday, October 28, 2002, 4:56:20 PM, you wrote:
BM The problem seems to be that none of the query information is available
BM until the line in the code where the cfquery statement is. Therefore, I
BM can't figure out a good way to skip over all the code.
BM I would use an application variable to flag weather the query is current
BM cached or not, and the time since it was first cached, but all this code is
BM in a .cfc and I want to keep it portable as possible.
BM Byron Mann
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Making use of cached queries
Hi. I have a cached query, as below, which runs on a number of pages: cfquery cachedwithin=#createtimespan(1,0,0,0)# name=findcats datasource=#application.DSN# SELECT sub_name,cat_name FROM categories, sub_cats WHERE categories.ID = sub_cats.cat_id ORDER by cat_name /cfquery The data in the query will only be changed monthly, at most. If I had a var such as cfset LeadCat = 100 (which would correspond to categories.ID), could I grab categories.cat_name from the cached query without altering it, somehow? I could probably have worded it better, but I hope it's enough for someone to understand! Thanks Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Making use of cached queries
Hi Will, If I understand what you're asking you should be able to accomplish that by using QueryOfQuery functionality which lets u use regular SQL to query an existing recordset in memory. http://livedocs.macromedia.com/cfmxdocs/Developing_ColdFusion_MX_Application s_with_CFML/using_recordsets.jsp Stace -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 6:12 PM To: CF-Talk Subject: Making use of cached queries Hi. I have a cached query, as below, which runs on a number of pages: cfquery cachedwithin=#createtimespan(1,0,0,0)# name=findcats datasource=#application.DSN# SELECT sub_name,cat_name FROM categories, sub_cats WHERE categories.ID = sub_cats.cat_id ORDER by cat_name /cfquery The data in the query will only be changed monthly, at most. If I had a var such as cfset LeadCat = 100 (which would correspond to categories.ID), could I grab categories.cat_name from the cached query without altering it, somehow? I could probably have worded it better, but I hope it's enough for someone to understand! Thanks Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Making use of cached queries
W Luke mailto:[EMAIL PROTECTED] wrote: If I understand what you're asking you should be able to accomplish that by using QueryOfQuery functionality which lets u use regular SQL to query an existing recordset in memory. Completely forgot about QoQ - been salivating over that for years, and only now (having moved to MX) do I have a chance to use it! Does it have a problem in calling TOP 2 records? For instance: Cfquery name=mytest dbtype=query SELECT TOP 2 ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY That causes an error with me. Any ideas? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Making use of cached queries
Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Ken -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 4:05 PM To: CF-Talk Subject: Re: Making use of cached queries W Luke mailto:[EMAIL PROTECTED] wrote: If I understand what you're asking you should be able to accomplish that by using QueryOfQuery functionality which lets u use regular SQL to query an existing recordset in memory. Completely forgot about QoQ - been salivating over that for years, and only now (having moved to MX) do I have a chance to use it! Does it have a problem in calling TOP 2 records? For instance: Cfquery name=mytest dbtype=query SELECT TOP 2 ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY That causes an error with me. Any ideas? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Making use of cached queries
Ken Brocx [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Does it have a problem in calling TOP 2 records? For instance: Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Thanks Ken. And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: Making use of cached queries
replace the double quotes around Yes with single quotes 'Yes' and it should run OK. Marius Milosav www.scorpiosoft.com It's not about technology, it's about people. Virtual Company (VICO) Application Demo www.scorpiosoft.com/vicodemo/login.cfm - Original Message - From: W Luke [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, October 04, 2002 7:20 PM Subject: Re: Making use of cached queries Ken Brocx [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Does it have a problem in calling TOP 2 records? For instance: Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Thanks Ken. And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: Making use of cached queries
Marius Milosav [EMAIL PROTECTED] wrote in message news:000d01c26bf5$17838510$[EMAIL PROTECTED]... replace the double quotes around Yes with single quotes 'Yes' and it should run OK. There shouldn't be any quotes, single or double - no need for them. I tried single quotes anyway, and it caused a new (and apparently more common) error: Unsupported type comparison. I've read lots from people having problems with these QoQs migrating from 5-to-MX - has anyone got any advice? Seems a great shame I won't be able to make use of QoQ's Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Making use of cached queries
W Luke wrote: Marius Milosav [EMAIL PROTECTED] wrote in message news:000d01c26bf5$17838510$[EMAIL PROTECTED]... replace the double quotes around Yes with single quotes 'Yes' and it should run OK. There shouldn't be any quotes, single or double - no need for them. I tried single quotes anyway, and it caused a new (and apparently more common) error: Unsupported type comparison. I've read lots from people having problems with these QoQs migrating from 5-to-MX - has anyone got any advice? Lots of people get the same advice: cfqueryparam :) Actually, I have never tried it with a boolean field before, and boolean is not a supported datatype for cfqueryparam (although you might try bit). Also, I would imagine it depends quite a bit on what your database returns *exactly*. Does it return YES/NO, TRUE/FALSE, 't'/'f' or 1/0? All of these are options to try, but based on the error I would go for either: Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 AND approved = 1 /CFQUERY or: Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 AND approved = cfqueryparam cfsqltype=cf_sql_bit' value=TRUE /CFQUERY Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Making use of cached queries
And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? I always use queryparam lately just solves a lot of problems. It depends on your database. Which are you using? And what type of field is it? Is it an actual yes/no field or a bit field with 1/0? I haven't had any problems with bit fields or boolean fields in a QoQ but I'm using SQLServer2k. I'd try either using cfqueryparam value=1 cfsqltype=CF_SQL_BIT Or cfqueryparam value=Yes cfsqltype=CF_SQL_VARCHAR Although IMHO all yes/no fields should just be bit fields Good luck have a great weekend! Ken -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 4:21 PM To: CF-Talk Subject: Re: Making use of cached queries Ken Brocx [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Does it have a problem in calling TOP 2 records? For instance: Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Thanks Ken. And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: failed retrieval of cached queries
Hi Tim I don't know how much of this response will be relevant to your situation, but here's how we managed to resolve this problem - (apologies if any of this seems patronising, but I don't know what level to aim at here, so I'll explain everything!) We had this problem, and it was driving us absolutely nuts for months. Yeah, sure, some of our old sites (written before any of the current developers had joined the company) had some dubious locking strategies, but the errors kept being thrown on our NEW sites, which used REQUEST variables rather than APPLICATION variables, and had EVERY read/write to a shared scope inside a CFLOCK. After many LNG head-scratching hours, we finally worked out that the following situation could occur - 1) Some old sites were using statements like this - cfquery name=qry_productfeatures datasource=#DSN# cachedwithin=#APPLICATION.querycachetime# 2) Although WRITES to the application variables were cflocked, statements such as the above were not. 3) If that CFQUERY statement was run at exactly the same time as APPLICATION.QueryCacheTime was being set, then the query would still be executed and the resultset cached, but the time for which to hold the resultset in the cache could be corrupt. 4) New sites were also running statements like this cfquery name=qry_productfeatures datasource=#REQUEST.DSN# cachedwithin=#REQUEST.querycachetime# against the same datasource as the old sites, and just occasionally, the SQL might match exactly. 5) The CF engine checks to see if it already has a query called qry_productfeatures on that datasource with the same SQL. If it does, it checks the current time against the cached query's cache time, to see if the query has expired. 6) If the cached query is corrupt, then the cfquery... statement does NOT return an catchable exception - the CF server keeps trying to retrieve it, and eventually (after about 60 seconds, in our case) it gives up, and the CF sub-system throws an error WITHOUT returning execution to your template. 7) For all the time that the engine is trying to retrieve that query, the thread used to process that request is unresponsive. It does NOT timeout at the Timeout request at X seconds setting in the CF administrator. 8) If this is on a frequently hit page, it can very easily clog up the whole thread pool, and leave your CF server unresponsive. 9) The only way to free up the threads again is to bounce the CF service. Conclusions --- 1) DON'T use application variables if you don't have to! Use the Request scope instead. 2) If you HAVE to use application variables (e.g. to store complex persistent structures that are based on huge DB queries that take ages to run), then Duplicate() the application structure into the REQUEST scope in your application.cfm, like this - cflock name=(some name) timeout=2 throwontimeout=YES cfset REQUEST = Duplicate( APPLICATION ) /cflock 3) Make sure ANY references to shared scopes are inside a lock of the same name (or scope) as the writes to those variables. Enforce this on a CODE level- don't rely on the Automatic locking setting in CFAdmin, because this only checks cfset ... statements, and DOES NOT catch implied reads like the cfquery ...cachedwithin=#APPLICATION.QueryCacheTime# statement. Hope that helps Alistair Davidson Senior Developer Rocom New Media www.rocomx.net There is no spoon -Original Message- From: Tim Stadinski [mailto:[EMAIL PROTECTED]] Sent: 25 October 2001 22:12 To: CF-Talk Subject: failed retrieval of cached queries Has anyone seen the following error: CFQuery Retrieval of cached query failedpThe error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (401:3) to (401:153) in the template file I have posted this on the forums, but have not got a response and it seems like there are others with similiar issues. thanks in advance. Timothy Stadinski Afternic.com [EMAIL PROTECTED] ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: failed retrieval of cached queries
Of all the points you have made, I agree about the locking issues, but that is not the case here. One point that I am curious about (6)If the cached query is corrupt How can you determine if it is corrupt and what makes a cached query corrupt? Thanks Tim -Original Message- From: Alistair Davidson [mailto:[EMAIL PROTECTED]] Sent: Monday, October 29, 2001 3:50 AM To: CF-Talk Subject: RE: failed retrieval of cached queries Hi Tim I don't know how much of this response will be relevant to your situation, but here's how we managed to resolve this problem - (apologies if any of this seems patronising, but I don't know what level to aim at here, so I'll explain everything!) We had this problem, and it was driving us absolutely nuts for months. Yeah, sure, some of our old sites (written before any of the current developers had joined the company) had some dubious locking strategies, but the errors kept being thrown on our NEW sites, which used REQUEST variables rather than APPLICATION variables, and had EVERY read/write to a shared scope inside a CFLOCK. After many LNG head-scratching hours, we finally worked out that the following situation could occur - 1) Some old sites were using statements like this - cfquery name=qry_productfeatures datasource=#DSN# cachedwithin=#APPLICATION.querycachetime# 2) Although WRITES to the application variables were cflocked, statements such as the above were not. 3) If that CFQUERY statement was run at exactly the same time as APPLICATION.QueryCacheTime was being set, then the query would still be executed and the resultset cached, but the time for which to hold the resultset in the cache could be corrupt. 4) New sites were also running statements like this cfquery name=qry_productfeatures datasource=#REQUEST.DSN# cachedwithin=#REQUEST.querycachetime# against the same datasource as the old sites, and just occasionally, the SQL might match exactly. 5) The CF engine checks to see if it already has a query called qry_productfeatures on that datasource with the same SQL. If it does, it checks the current time against the cached query's cache time, to see if the query has expired. 6) If the cached query is corrupt, then the cfquery... statement does NOT return an catchable exception - the CF server keeps trying to retrieve it, and eventually (after about 60 seconds, in our case) it gives up, and the CF sub-system throws an error WITHOUT returning execution to your template. 7) For all the time that the engine is trying to retrieve that query, the thread used to process that request is unresponsive. It does NOT timeout at the Timeout request at X seconds setting in the CF administrator. 8) If this is on a frequently hit page, it can very easily clog up the whole thread pool, and leave your CF server unresponsive. 9) The only way to free up the threads again is to bounce the CF service. Conclusions --- 1) DON'T use application variables if you don't have to! Use the Request scope instead. 2) If you HAVE to use application variables (e.g. to store complex persistent structures that are based on huge DB queries that take ages to run), then Duplicate() the application structure into the REQUEST scope in your application.cfm, like this - cflock name=(some name) timeout=2 throwontimeout=YES cfset REQUEST = Duplicate( APPLICATION ) /cflock 3) Make sure ANY references to shared scopes are inside a lock of the same name (or scope) as the writes to those variables. Enforce this on a CODE level- don't rely on the Automatic locking setting in CFAdmin, because this only checks cfset ... statements, and DOES NOT catch implied reads like the cfquery ...cachedwithin=#APPLICATION.QueryCacheTime# statement. Hope that helps Alistair Davidson Senior Developer Rocom New Media www.rocomx.net There is no spoon -Original Message- From: Tim Stadinski [mailto:[EMAIL PROTECTED]] Sent: 25 October 2001 22:12 To: CF-Talk Subject: failed retrieval of cached queries Has anyone seen the following error: CFQuery Retrieval of cached query failedpThe error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (401:3) to (401:153) in the template file I have posted this on the forums, but have not got a response and it seems like there are others with similiar issues. thanks in advance. Timothy Stadinski Afternic.com [EMAIL PROTECTED] ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: failed retrieval of cached queries
Hmmmtricky! 1) How do you determine if it is corrupt? Um if it throws a Retrieval of cached query failed error, then it's corrupt! I know it's a cop-out answer, but we haven't been able to find any other way - you don't seem to be able to CFCATCH the error. 2) What makes a cached query corrupt? Well, the situation in 3) in my original response is one way - there's probably many others. Here's a quote from Greg Gauthier (Allaire Sr. Tech. Support Engineer) in the forums - This error typically happens in one of three cases: 1) If you were trying to insert a query into a full cache where all of the queries were being used you'd get the error. 2) Another place where this could happen is if the cache max size is set to zero and you try to cache a query 3) Finally this occurs when global access to the cache is granted initially, but because the page timeout has been reached the retrieval fails. Does the page where this error occurs take a long time to run? Alistair Davidson Senior Developer Rocom New Media www.rocomx.net There is no spoon -Original Message- From: Tim Stadinski [mailto:[EMAIL PROTECTED]] Sent: 29 October 2001 15:38 To: CF-Talk Subject: RE: failed retrieval of cached queries Of all the points you have made, I agree about the locking issues, but that is not the case here. One point that I am curious about (6)If the cached query is corrupt How can you determine if it is corrupt and what makes a cached query corrupt? Thanks Tim -Original Message- From: Alistair Davidson [mailto:[EMAIL PROTECTED]] Sent: Monday, October 29, 2001 3:50 AM To: CF-Talk Subject: RE: failed retrieval of cached queries Hi Tim I don't know how much of this response will be relevant to your situation, but here's how we managed to resolve this problem - (apologies if any of this seems patronising, but I don't know what level to aim at here, so I'll explain everything!) We had this problem, and it was driving us absolutely nuts for months. Yeah, sure, some of our old sites (written before any of the current developers had joined the company) had some dubious locking strategies, but the errors kept being thrown on our NEW sites, which used REQUEST variables rather than APPLICATION variables, and had EVERY read/write to a shared scope inside a CFLOCK. After many LNG head-scratching hours, we finally worked out that the following situation could occur - 1) Some old sites were using statements like this - cfquery name=qry_productfeatures datasource=#DSN# cachedwithin=#APPLICATION.querycachetime# 2) Although WRITES to the application variables were cflocked, statements such as the above were not. 3) If that CFQUERY statement was run at exactly the same time as APPLICATION.QueryCacheTime was being set, then the query would still be executed and the resultset cached, but the time for which to hold the resultset in the cache could be corrupt. 4) New sites were also running statements like this cfquery name=qry_productfeatures datasource=#REQUEST.DSN# cachedwithin=#REQUEST.querycachetime# against the same datasource as the old sites, and just occasionally, the SQL might match exactly. 5) The CF engine checks to see if it already has a query called qry_productfeatures on that datasource with the same SQL. If it does, it checks the current time against the cached query's cache time, to see if the query has expired. 6) If the cached query is corrupt, then the cfquery... statement does NOT return an catchable exception - the CF server keeps trying to retrieve it, and eventually (after about 60 seconds, in our case) it gives up, and the CF sub-system throws an error WITHOUT returning execution to your template. 7) For all the time that the engine is trying to retrieve that query, the thread used to process that request is unresponsive. It does NOT timeout at the Timeout request at X seconds setting in the CF administrator. 8) If this is on a frequently hit page, it can very easily clog up the whole thread pool, and leave your CF server unresponsive. 9) The only way to free up the threads again is to bounce the CF service. Conclusions --- 1) DON'T use application variables if you don't have to! Use the Request scope instead. 2) If you HAVE to use application variables (e.g. to store complex persistent structures that are based on huge DB queries that take ages to run), then Duplicate() the application structure into the REQUEST scope in your application.cfm, like this - cflock name=(some name) timeout=2 throwontimeout=YES cfset REQUEST = Duplicate( APPLICATION ) /cflock 3) Make sure ANY references to shared scopes are inside a lock of the same name (or scope) as the writes to those variables. Enforce this on a CODE level- don't rely on the Automatic locking setting in CFAdmin, because this only checks cfset ... statements, and DOES NOT catch implied reads like the cfquery ...cachedwithin=#APPLICATION.QueryCacheTime# statement. Hope that helps
