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
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: 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
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
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]
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]
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]
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]
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]
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.
Re: Cached queries
Thomas Chiverton wrote: Let me get this right, if I set cachedWithin to 24 hours, and set 100 cached queries in the server, the server limit will 'win' if a 101st query is made, and the oldest query will be uncached ? Correct. Jochem ~~ 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: Cached Queries
Probably obvious, but... I do believe it's based on the entire *exact* contents of the cfquery tag. One simple change effectively makes it a different query...even just adding a space to it. so if you want to use a cached query in more than one place, CFINCLUDE it. That way, you know all instances of that query are going to be identical. -- Aidan Whitehall [EMAIL PROTECTED] Netshopper UK Ltd Advanced Web Solutions Services http://www.netshopperuk.com/ Telephone +44 (01744) 648650 Fax +44 (01744) 648651 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Cached Queries
so if you want to use a cached query in more than one place, CFINCLUDE it. That way, you know all instances of that query are going to be identical. Yep...like all good Fuseboxers do. :) Also makes it trivial to flush that cached query after doing an add/delete/update that would impact that query data. Ken ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Cached Queries
Take a peek in the CFAdmin and you will see that there is a setting for max number of queries. I think the default setting is 100, but you can change this setting if you'd like. -Cameron Cameron Childress elliptIQ Inc. p.770.460.7277.232 f.770.460.0963 -Original Message- From: Duane Boudreau [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 12:41 PM To: CF-Talk Subject: Cached Queries Is there a limit the physical number of queries that can be cached by CF? I know one of the limiting factors is that amount of RAM, but I thought I heard some where that there was a limit of 100 queries. Duane ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Cached Queries
Great thanks! Duane -Original Message- From: Cameron Childress [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 1:08 PM To: CF-Talk Subject: RE: Cached Queries Take a peek in the CFAdmin and you will see that there is a setting for max number of queries. I think the default setting is 100, but you can change this setting if you'd like. -Cameron Cameron Childress elliptIQ Inc. p.770.460.7277.232 f.770.460.0963 -Original Message- From: Duane Boudreau [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 12:41 PM To: CF-Talk Subject: Cached Queries Is there a limit the physical number of queries that can be cached by CF? I know one of the limiting factors is that amount of RAM, but I thought I heard some where that there was a limit of 100 queries. Duane ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Cached Queries
Quick question on cached queries: Are queries cached and retrieved from cache based on the query name or the SQL within the query? Duane -Original Message- From: Cameron Childress [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 1:08 PM To: CF-Talk Subject: RE: Cached Queries Take a peek in the CFAdmin and you will see that there is a setting for max number of queries. I think the default setting is 100, but you can change this setting if you'd like. -Cameron Cameron Childress elliptIQ Inc. p.770.460.7277.232 f.770.460.0963 -Original Message- From: Duane Boudreau [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 12:41 PM To: CF-Talk Subject: Cached Queries Is there a limit the physical number of queries that can be cached by CF? I know one of the limiting factors is that amount of RAM, but I thought I heard some where that there was a limit of 100 queries. Duane ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Cached Queries
I do believe it's based on the entire *exact* contents of the cfquery tag. One simple change effectively makes it a different query...even just adding a space to it. Ken Quick question on cached queries: Are queries cached and retrieved from cache based on the query name or the SQL within the query? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Cached Queries
Thanks Duane -Original Message- From: Ken Wilson [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 11, 2001 1:26 PM To: CF-Talk Subject: RE: Cached Queries I do believe it's based on the entire *exact* contents of the cfquery tag. One simple change effectively makes it a different query...even just adding a space to it. Ken Quick question on cached queries: Are queries cached and retrieved from cache based on the query name or the SQL within the query? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries? huh? - repost
How do I define a cached query and then access it? Problem is that I have a menu built off a query, the pages built from those menu choices off other queries. Click too many menu items too quickly and poof! The CF service reboots. What would be the best way to handle this? In CF 4.x, you can use the CACHEDWITHIN or CACHEDAFTER attributes to cache a query; when that identical query is run later, the recordset will be retrieved from the cache instead of from the database. If many people see the same menu items, query caching is probably a good idea. If the menu items don't change much, you might consider caching the generated HTML output for the menu using CFCACHE, which will mean even less server-side work at runtime. Can I set a query to a variable? Yes, you can store a query in any of the persistent memory scopes: Server, Application, Session. Using the query caching attributes is a little easier, though, in most cases. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries? huh?
How do I define a cached query and then access it? Problem is that I have a menu built off a query, the pages built from those menu choices off other queries. Click too many menu items too quickly and poof! The CF service reboots. What would be the best way to handle this? Can I set a query to a variable? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries vs. structures was(Re: ...BETA...!! (query a query))
Also, you must take into account the fact that CF is not a DBMS, it has a mini-database engine in it, but it does not have an query optimization engine (like a SQL Server or an Oracle would have). So databases will usually process queries quicker than CF will. The real benefits are in eliminating some database activity (and database traffic) as well as being able to perform database operations not possible with straight database calls. --- Ben -Original Message- From: Jennifer [mailto:[EMAIL PROTECTED]] Sent: Friday, February 02, 2001 2:19 PM To: CF-Talk Subject: cached queries vs. structures was(Re: ...BETA...!! (query a query)) I'd like to see some statistics on cached queries vs. querying a structure. It seems like using a cached query would have better performance if memory were unlimited, but at some point the amount of memory used to store the cached queries is going to bog down the server. I've got to imagine that the two methods have differing performance curves and at some point they are going to intersect. Cached queries will be faster up until the intersection but beyond that, structures would be faster. I would just be much happier about the implementation if I could query a query. Then I could avoid having to decide which other method is the worst in this case. At 10:31 AM 2/2/01 -0800, you wrote: I think this example illustrates one use of query of a query is when the cached queries contain de-normalized data. This is the same conclusion I came to when I cached a database in RAM in a Structure and wrote queries against the data in the Structure (which I later abandoned for cached queries, which were faster than the Structure approach). best, paul At 11:45 AM 2/2/01 -0600, you wrote: We have cached queries that hold information that is used on every page hit of the site. Every page returns a random record from the cached record set. Now, each of these records have links to products, and each record has several products associated with it. Ordinarily, the first link is displayed. However, in one section of the site, all of the products are displayed (sometimes these are recipe ingredients so let's assume that's the case). Below the section that lists the products is a section that has links to all recipes that contain that product. Now then (what does that phrase mean anyway?), I have cached queries that contain the recipes and the associated products but the query is run on the recipe id. However, to get the list of all recipes that contain a specified product, I'd have to use a significantly different query but one that contains all of the same fields, joined in a similar way. The only thing that changes is the search criteria: you are either searching by recipe id or you are searching by product id. In both cases, a lot of the same information is returned. Right now, this sucks. However, if I could load in a query of all recipe ids joined on the product ids and another list of the copy for the recipes joined on their recipe id (including the first product id), I could do this instead: 1) display the copy linked to the recipe id where that needs to happen (every page of the site) by outputting a random record from the copy/recipe id set 2) generate the list of ingredients by querying the recipe id/product id list for that recipe id 3) generate the list of recipes by querying the recipe id/product id list for the product id That means that instead of caching 100 recipe id/product id queries plus 500 product id/recipe id queries (or hitting the db every time or loading the records into a structure), I could cache 2 queries and query them as appropriate. It would be much simpler and would save resources lost to all other methods. Ding ding ding! Now announcing my newly updated website http://www.blivit.org/mr_urc/index.cfm Resume: http://www.blivit.org/mr_urc/resume.cfm ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries vs. structures was(Re: ...BETA...!! (query a query))
I'd like to see some statistics on cached queries vs. querying a structure. It would entirely depend on who you are planning on using your data. It's like comparing apples and oranges. Incidentally, a query in CF can be treated like a structure already. You can pull records out of a query using the following format QueryName[ColumnName][RowNumber]. -Cameron Cameron Childress elliptIQ Inc. p.770.460.7277.232 f.770.460.0963 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries vs. structures was(Re: ...BETA...!! (query a query))
To follow this up, queries and structures are both implemented as arrays in CF, so the performance difference should be negligible. Jason Jason Aden Allaire Certified Developer [EMAIL PROTECTED] www.wwstudios.com -Original Message- From: Cameron Childress [mailto:[EMAIL PROTECTED]] Sent: Friday, February 02, 2001 2:38 PM To: CF-Talk Subject: RE: cached queries vs. structures was(Re: ...BETA...!! (query a query)) I'd like to see some statistics on cached queries vs. querying a structure. It would entirely depend on who you are planning on using your data. It's like comparing apples and oranges. Incidentally, a query in CF can be treated like a structure already. You can pull records out of a query using the following format QueryName[ColumnName][RowNumber]. -Cameron Cameron Childress elliptIQ Inc. p.770.460.7277.232 f.770.460.0963 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries vs. structures was(Re: ...BETA...!! (query a query))
At 02:38 PM 2/2/01 -0500, you wrote: I'd like to see some statistics on cached queries vs. querying a structure. It would entirely depend on who you are planning on using your data. It's like comparing apples and oranges. Yes, but if I could get some idea what the difference in performance is for a given number of queries of a certain size with the contents used a certain number of times, I could infer how it would affect my particular system. It wouldn't be exact but it would be a better prediction than I can make now. Several sets of circumstances could give me even better results. Scientific method and graph interpolation and all that Incidentally, a query in CF can be treated like a structure already. You can pull records out of a query using the following format QueryName[ColumnName][RowNumber]. Right, but can we use the struct functions on a query to the effect of running a query on it? Now announcing my newly updated website http://www.blivit.org/mr_urc/index.cfm Resume: http://www.blivit.org/mr_urc/resume.cfm ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries vs. structures was(Re: ...BETA...!! (query a query))
I believe we have to define the problem more accurately, and then test the alternatives, to determine which way to go: Structures vs Cached Queries. I accidently tested Structures vs Cached Queries on a Yellow Pages application. I had planned to use Structures, but developed the app both ways. I then ran into some suspicious results and did a bit of testing. The results are in the archives. So, for example, one page type lists yellow page Headings that start with "A" (Accountants, Attorneys, etc). Structure. I put ALL Headings in a single Structure. I then queried the database to get all Heading IDs for Headings that began with "A", and then CFLOOPed and pulled the Heading Name and other data out of the Structure using the list of IDs. I displayed them 100 at a time on a page, and offered "Next 100" etc. Cached Query. I cached a query for 5 minutes that contained all Headings that began with "A" Again I displayed them 100 at a time on a page, and offered "Next 100? etc. The result? Cached query was faster than Structure. The conclusion? Not a fair test. One alternative is NOT to put ALL Headings in a single Structure. Create StructureA to contain data for those Headings that begin with "A" only. Then StructureB, etc. Then using the Structure approach there would be no trips to the database whatsoever to get all Headings that start with "A". This is just a simple example of what I'll call the de-normalization that potentially can make Structures the better way to go. But to start out, it's hard to beat the flexibility, development simplicity, and speed cached queries offer. I recommend you do what I accidently did: Develop both ways and implement the one you like best. I didn't figure this out for myself. Others here pointed out to me that using Structures to hold de-normalized data was the better way to use them. There's a very informative posting somewhere in the archives. One problem I found with Structures is that it took more than 30 minutes for my app to load up the Structures on startup. There probably is a way for Structures to compete with the flexibility cached queries offer in this respect, but I haven't looked into it. best, paul At 02:51 PM 2/2/01 -0500, you wrote: To follow this up, queries and structures are both implemented as arrays in CF, so the performance difference should be negligible. Jason Jason Aden Allaire Certified Developer [EMAIL PROTECTED] www.wwstudios.com -Original Message- From: Cameron Childress [mailto:[EMAIL PROTECTED]] Sent: Friday, February 02, 2001 2:38 PM To: CF-Talk Subject: RE: cached queries vs. structures was(Re: ...BETA...!! (query a query)) I'd like to see some statistics on cached queries vs. querying a structure. It would entirely depend on who you are planning on using your data. It's like comparing apples and oranges. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: cached queries vs. structures was(Re: ...BETA...!! (query a query))
Yes, but... [snip] What I am saying is that there are probably not very many situations where it would make sense to use both solutions. The foremost question is not "which is faster", but "which is more practical for my app". It's like saying, which is faster Client or Session variables? The answer is really not relevant because in most situations it really only makes sense to use one or the other. Right, but can we use the struct functions on a query to the effect of running a query on it? What? -Cameron Cameron Childress elliptIQ Inc. p.770.460.7277.232 f.770.460.0963 -Original Message- From: Jennifer [mailto:[EMAIL PROTECTED]] Sent: Friday, February 02, 2001 3:36 PM To: CF-Talk Subject: RE: cached queries vs. structures was(Re: ...BETA...!! (query a query)) At 02:38 PM 2/2/01 -0500, you wrote: I'd like to see some statistics on cached queries vs. querying a structure. It would entirely depend on who you are planning on using your data. It's like comparing apples and oranges. Yes, but if I could get some idea what the difference in performance is for a given number of queries of a certain size with the contents used a certain number of times, I could infer how it would affect my particular system. It wouldn't be exact but it would be a better prediction than I can make now. Several sets of circumstances could give me even better results. Scientific method and graph interpolation and all that Incidentally, a query in CF can be treated like a structure already. You can pull records out of a query using the following format QueryName[ColumnName][RowNumber]. Right, but can we use the struct functions on a query to the effect of running a query on it? Now announcing my newly updated website http://www.blivit.org/mr_urc/index.cfm Resume: http://www.blivit.org/mr_urc/resume.cfm ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: [Cached Queries]
I do something like that but use an onChange event in my first select box to run a combo CF/js script in a hidden frame to get only those items necessary based on the first select statement. Since I don't have to return the entire state list for every country in the world each time, it executes fairly quickly. -Original Message- From: Bud [mailto:[EMAIL PROTECTED]] Sent: Sunday, November 12, 2000 8:32 AM To: CF-Talk Subject: Re: [Cached Queries] On 11/12/00, W Luke penned: Excellent. I basically have 2 queries that both return large amounts of records - the first being as you describe above - however the second is an interesting dilema. I have a table called Locations, with "County" and "Town." Using the cool TwoSelectsRelated tag, I'm populating both boxes (users choose a county - and then a town). However with a current town list of over 1600, and due to the fact that TwoSelectsRelated loads both columns of the table in their entirity, this takes *ages* to download. Using TwoSelectsRelated is by far the nicest option available - but unfortunately I might have to change. Hopefully caching the query will speed things up a little anyhow. I tried using it in my shopping cart also for countries and states/territories. It was dreadfully slow, because of the huge amount of javascript code that had to be loaded, and didn't work at times probably due to the same thing. I ended up just going to an extra screen to choose the state based on the country. An extra click, but still much quicker. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
On 11/11/00, pan penned: As a final test to see if reformatted sql affects caching I repeated A B with the difference that afetr a restart I excluded the reformatted sql call from the template but included it within B. Result is that Bud is absolutely right. My mistake was that I have a machine that almost never needs a restart. :) Another thing that I noticed and surprised me. When setting up the queries, I ran them once, then of course even the different on was cached. So I changed the name of the query to run it fresh but they all came back cached again. So, to run them fresh, I had to add product_name to the select statement. Seems that the name of the query doesn't matter. Just the structure/datasource combination. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
They are cached by name, search parameters, and also by the query structure itself. So SELECT product_name FROM products and SELECT product_name FROM products would be read as 2 separate queries because the structure is different, even if they are named the same. Oh I see - didn't realise that. Where it will also help, in addition to static queries, is if you have a query that returns many items and you are doing next'n records. Every time the user clicks to show the next startrow/maxrows, the database will not be hit again and it will speed things up considerably. Excellent. I basically have 2 queries that both return large amounts of records - the first being as you describe above - however the second is an interesting dilema. I have a table called Locations, with "County" and "Town." Using the cool TwoSelectsRelated tag, I'm populating both boxes (users choose a county - and then a town). However with a current town list of over 1600, and due to the fact that TwoSelectsRelated loads both columns of the table in their entirity, this takes *ages* to download. Using TwoSelectsRelated is by far the nicest option available - but unfortunately I might have to change. Hopefully caching the query will speed things up a little anyhow.. Cheers, Will Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
On 11/12/00, W Luke penned: Excellent. I basically have 2 queries that both return large amounts of records - the first being as you describe above - however the second is an interesting dilema. I have a table called Locations, with "County" and "Town." Using the cool TwoSelectsRelated tag, I'm populating both boxes (users choose a county - and then a town). However with a current town list of over 1600, and due to the fact that TwoSelectsRelated loads both columns of the table in their entirity, this takes *ages* to download. Using TwoSelectsRelated is by far the nicest option available - but unfortunately I might have to change. Hopefully caching the query will speed things up a little anyhow. I tried using it in my shopping cart also for countries and states/territories. It was dreadfully slow, because of the huge amount of javascript code that had to be loaded, and didn't work at times probably due to the same thing. I ended up just going to an extra screen to choose the state based on the country. An extra click, but still much quicker. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
I tried using it in my shopping cart also for countries and states/territories. It was dreadfully slow, because of the huge amount of javascript code that had to be loaded, and didn't work at times probably due to the same thing. I ended up just going to an extra screen to choose the state based on the country. An extra click, but still much quicker. Yup this is what I've done for some of the site - as you say, an extra click, but much much quicker. Will -- Coming soon: http://www.LocalBounty.com Local Classified Advertising for the UK Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
On 11/10/00, Alex penned: i think they are cached according to the queryname. so if you name the query with a sessionid you could have them cached per user. and YES they will make the page faster for the end user. i cache all static querys. They are cached by name, search parameters, and also by the query structure itself. So SELECT product_name FROM products and SELECT product_name FROM products would be read as 2 separate queries because the structure is different, even if they are named the same. Where it will also help, in addition to static queries, is if you have a query that returns many items and you are doing next'n records. Every time the user clicks to show the next startrow/maxrows, the database will not be hit again and it will speed things up considerably. Tip. If you are going to be caching alot of queries, you may want to do it like I do my shopping cart. In application.cfm I set: CFPARAM NAME="attributes.cachequery" DEFAULT="#createtimespan(0,0,30,0)#" Then in the query tag: CachedWithin="#attributes.cachequery#" Then when doing administration, you just set the timespan to 0,0,0,0, do your administration, wait 1/2 hour and set it back. This assures everyone is getting up to date records. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
From: "Bud" [EMAIL PROTECTED] Subject: Re: [Cached Queries] On 11/10/00, Alex penned: i think they are cached according to the queryname. so if you name the query with a sessionid you could have them cached per user. and YES they will make the page faster for the end user. i cache all static querys. They are cached by name, search parameters, and also by the query structure itself. So SELECT product_name FROM products and SELECT product_name FROM products would be read as 2 separate queries because the structure is different, even if they are named the same. That's not true. The two selects you wrote will not prevent cachedwithin usage. There has to be a difference in sql that would produce different rows and/or columns. Pan Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
--_-1238158479==_ma
Content-Type: text/plain; charset="us-ascii" ; format="flowed"
On 11/11/00, pan penned:
That's not true.
The two selects you wrote will not prevent cachedwithin usage.
There has to be a difference in sql that would produce different
rows and/or columns.
Yes, it is true. Here's a test I ran to prove it. These 4 queries run
right after one another on the same template. They are identical
except for the layout.
CFQUERY DATASOURCE="YouBuyGiftsSQL" NAME="getproducts"
cachedwithin="#createtimespan(0,0,10,0)#"
SELECT product_id,product_name
FROM products
/CFQUERY
CFQUERY DATASOURCE="YouBuyGiftsSQL" NAME="getproducts"
cachedwithin="#createtimespan(0,0,10,0)#"
SELECT product_id,product_name
FROM products
/CFQUERY
CFQUERY DATASOURCE="YouBuyGiftsSQL" NAME="getproducts"
cachedwithin="#createtimespan(0,0,10,0)#"
SELECT product_id,product_name
FROM products
/CFQUERY
CFQUERY DATASOURCE="YouBuyGiftsSQL" NAME="getproducts"
cachedwithin="#createtimespan(0,0,10,0)#"
SELECT product_id,product_name FROM products
/CFQUERY
Here is the debug output. Notice the first one runs, gets cached for
the next 2, then the last one queries the database again. It's
quicker, but uncached nonetheless.
Queries
getproducts (Records=1815, Time=1247ms)
SQL =
SELECT product_id,product_name
FROM products
getproducts (Records=1815, Time=Cached Query)
SQL =
SELECT product_id,product_name
FROM products
getproducts (Records=1815, Time=Cached Query)
SQL =
SELECT product_id,product_name
FROM products
getproducts (Records=1815, Time=90ms)
SQL =
SELECT product_id,product_name FROM products
--
Bud Schneehagen - Tropical Web Creations
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452
--_-1238158479==_ma
Content-Type: text/html; charset="us-ascii"
!doctype html public "-//W3C//DTD W3 HTML//EN"
htmlheadstyle type="text/css"!--
blockquote, dl, ul, ol, li { margin-top: 0 ; margin-bottom: 0 }
--/styletitleRe: [Cached Queries]/title/headbody
divOn 11/11/00, pan penned:/div
blockquote type="cite" citeThat's not true.br
The two selects you wrote will not prevent cachedwithin usage.br
There has to be a difference in sql that would produce
different/blockquote
blockquote type="cite" citerows and/or columns./blockquote
divbr/div
divYes, it is true. Here's a test I ran to prove it. These 4
queries run right after one another on the same template. They are
identical except for the layout./div
divbr/div
divfont size="-1"lt;CFQUERY
DATASOURCE=quot;YouBuyGiftsSQLquot; NAME=quot;getproductsquot;
cachedwithin=quot;#createtimespan(0,0,10,0)#span
/spanquot;gt;br
SELECT product_id,product_namebr
FROM productsbr
lt;/CFQUERYgt;br
lt;CFQUERY DATASOURCE=quot;YouBuyGiftsSQLquot;
NAME=quot;getproductsquot;
cachedwithin=quot;#createtimespan(0,0,10,0)#span
/spanquot;gt;br
SELECT product_id,product_namebr
FROM productsbr
lt;/CFQUERYgt;br
lt;CFQUERY DATASOURCE=quot;YouBuyGiftsSQLquot;
NAME=quot;getproductsquot;
cachedwithin=quot;#createtimespan(0,0,10,0)#span
/spanquot;gt;br
SELECT product_id,product_namebr
FROM productsbr
lt;/CFQUERYgt;br
lt;CFQUERY DATASOURCE=quot;YouBuyGiftsSQLquot;
NAME=quot;getproductsquot;
cachedwithin=quot;#createtimespan(0,0,10,0)#span
/spanquot;gt;br
SELECT product_id,product_name FROM products/font/div
divfont size="-1"lt;/CFQUERYgt;/font/div
divbr/div
divHere is the debug output. Notice the first one runs, gets cached
for the next 2, then the last one queries the database again. It's
quicker, but uncached nonetheless./div
divbr/div
divbr
font size="-1"Queriesbr
br
getproducts (Records=1815, Time=1247ms)br
SQL =br
SELECT product_id,product_namebr
FROM productsbr
br
getproducts (Records=1815, Time=Cached Query)br
SQL =br
SELECT product_id,product_namebr
FROM productsbr
br
getproducts (Records=1815, Time=Cached Query)br
SQL =br
SELECT product_id,product_namebr
FROM productsbr
br
getproducts (Records=1815, Time=90ms)br
SQL =/font/div
divfont size="-1"SELECT product_id,product_name FROM
products/font/div
div-- br
br
Bud Schneehagen - Tropical Web Creationsbr
br
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/br
ColdFusion Solutions / eCommerce Developmentbr
[EMAIL PROTECTED]br
http://www.twcreations.com/br
954.721.3452/div
/body
/html
--_-1238158479==_ma--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
From: "Bud" [EMAIL PROTECTED] Subject: Re: [Cached Queries] [results of sql formatting changes on caching] Interesting. Before I replied that it wasn't true I also had run the same type of test. As you can see from the debug output (A) below the differently typed sql also cached. To test further and to allow for queries cached within the current lifetime of the running server I start/stopped and then got the debug results in listing B. B shows you were right, but also shows something else. The first cachedwithin call after the intial query did not produce cached results on a freshly restarted machine. That doesn't seem correct unless there is some memory writing procedure that is relatively slow in the cf administrative functions. I would think that if caching is turned on, then the intial query would be in memory at the time the query is performed. Thus, any immediately following cachedwithin call would read from the same memory segment. It seems that there is finite time within which the caching functions copy a query recordset from some working segment to a different caching memory segment. I suppose it makes sense that cached queries are apparently moved from a working memory segment to a separate caching memory segment, but it does make for a need to finely control the sequencing from initial query to any subsequent cached calls. Good discussion. Doe anyone else interpret the differences between A B as anything else than memory segment handling? As a final test to see if reformatted sql affects caching I repeated A B with the difference that afetr a restart I excluded the reformatted sql call from the template but included it within B. Result is that Bud is absolutely right. My mistake was that I have a machine that almost never needs a restart. :) Pan [debug outpout A] [during an active server lifetime] [intial query] getURLs (Records=2553, Time=485ms) SQL = select url_text from URL where 0=0; [first cachedwithin call] getURLs (Records=2553, Time=Cached Query) SQL = select url_text from URL where 0=0; [second cahcedwithin call] getURLs (Records=2553, Time=Cached Query) SQL = select url_text from URL where 0=0; [third cachedwithin call with sql reformat] [first call with reformatted sql] getURLs (Records=2553, Time=Cached Query) SQL = select url_text from URL where 0=0; [debug output B] [machine restarted] [initial query] getURLs (Records=2553, Time=581ms) SQL = select url_text from URL where 0=0; [first cahcedwithin call] getURLs (Records=2553, Time=478ms) SQL = select url_text from URL where 0=0; [second cachedwithin call] getURLs (Records=2553, Time=Cached Query) SQL = select url_text from URL where 0=0; [third cachedwithin call but with sql reformatted] getURLs (Records=2553, Time=457ms) SQL = select url_text from URL where 0=0; Pan Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
Interesting. B shows you were right, but also shows something else. The first cachedwithin call after the intial query did not produce cached results on a freshly restarted machine. Good work boys. Just watching this discussion is making my brain itchy : ) Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Cached Queries
Will: Using the CachedWithin attribute caches the query "globally" as you put it. If you want to cache queries on a per user bases you might want to make the query a session query. You can also accomplish "global" caching by placing the query into the application scope. Andrew Hewitt Web Application Developer webworld studios, inc. www.wwstudios.com -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, November 10, 2000 7:25 PM To: CF-Talk Subject: Cached Queries Hi, I'm quite interested in caching a couple of queries, of 1500 records or more, using the CachedWithin attribute. Can anyone tell me whether queries are cached on a per-user/session basis, or "globally" for all users? I understand it might reduce loads on the server - but what about end-user speeds; will it speed up the users' request? Any other thoughts, comments etc welcome. Will Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: [Cached Queries]
i think they are cached according to the queryname. so if you name the query with a sessionid you could have them cached per user. and YES they will make the page faster for the end user. i cache all static querys. "W Luke" [EMAIL PROTECTED] wrote: Hi, I'm quite interested in caching a couple of queries, of 1500 records or more, using the CachedWithin attribute. Can anyone tell me whether queries are cached on a per-user/session basis, or "globally" for all users? I understand it might reduce loads on the server - but what about end-user speeds; will it speed up the users' request? Any other thoughts, comments etc welcome. Will Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Get free email and a permanent address at http://www.netaddress.com/?N=1 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebarRsts or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Cached Queries
I have recently started to do this cache user-specific queries on a global basis by using dynamic query names, based upon data specific to a user. So far, it seems to be working well. Kevin [EMAIL PROTECTED] 11/10/00 04:52PM Will: Using the CachedWithin attribute caches the query "globally" as you put it. If you want to cache queries on a per user bases you might want to make the query a session query. You can also accomplish "global" caching by placing the query into the application scope. Andrew Hewitt Web Application Developer webworld studios, inc. www.wwstudios.com -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, November 10, 2000 7:25 PM To: CF-Talk Subject: Cached Queries Hi, I'm quite interested in caching a couple of queries, of 1500 records or more, using the CachedWithin attribute. Can anyone tell me whether queries are cached on a per-user/session basis, or "globally" for all users? I understand it might reduce loads on the server - but what about end-user speeds; will it speed up the users' request? Any other thoughts, comments etc welcome. Will Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Cached Queries
At 09:39 PM 10/6/00 -0600, you wrote: Is it advisable to use locking when referencing a cached query? I would assume a cached query fits the bill of being shared data. If so, then wouldn't the lock need to be around the cfquery statment as well as references to any query values? You don't need to lock MyQuery if you name it Request.MyQuery (that is, give it Request scope). Does modifying the contents of a cached query 'dirty' the cache, so that the query would automatically be run again on the next reference, or will the query remain (in its modified state) until the cache period expires? A cached query is a cached query. You reference it by its name in your CF templates, but CF includes, in addition, reference by SQL. So if by "modifying the contents of a cached query" you mean you have a dynamic query that's cached, for example: CFQUERY Name = "MyQuery" SELECT * FROM MyTable WHERE ID = #FORM.ID# then CF will cache a distinct dataset for each value of #FORM.ID# OTOH, if by "modifying the contents of a cached query" you mean you modify the data in the underlying database, my *guess* is that this does not affect the cached query until it's requested after it times out. HTH best, paul -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Cached Queries
Is it advisable to use locking when referencing a cached query? I would assume a cached query fits the bill of being shared data. If so, then wouldn't the lock need to be around the cfquery statment as well as references to any query values? If you're referring to queries cached with the CACHEDWITHIN or CACHEDAFTER attributes, there's no need for locking those queries. The data that CF stores after running a query with a caching attribute is not changeable, so you don't have to worry about concurrent access to the data. Does modifying the contents of a cached query 'dirty' the cache, so that the query would automatically be run again on the next reference, or will the query remain (in its modified state) until the cache period expires? You can't really modify the contents of a cached query (again, assuming that you're talking about using the caching attributes above). CF doesn't store those contents anywhere that you can programmatically access. To further illuminate this, let's say you run the following query: cfquery name="foo" datasource="bar" cachedwithin="#CreateTimeSpan(0,1,0,0)#" SELECT fooval1, fooval2 from footable /cfquery Then, perhaps you use one of the CF 3.x query functions: cfscript QueryAddRow(foo); QuerySetCell(foo, "fooval1", "new value 1"); QuerySetCell(foo, "fooval2", "new value 2"); /cfscript Finally, when you reference the query, it would have your new row in it. However, what you've really modified is the query variable foo, not the underlying cached recordset, which CF stores after executing the query. CF doesn't let you touch this recordset directly, and will only reuse it if someone executes the same CFQUERY tag within the specified time span. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Cached Queries
Does this mean that CF implicitly creates a copy of the cached query (query variable foo, above) for every template that references the query or will CF only do this if a template modifies the data within the query? I don't have enough knowledge of the internals of the CF engine to say for sure, but I'd be really very surprised if the answer wasn't the second choice. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: cached queries
Perhaps your syntax for setting this.attribute wasn't right? This should
work.
cfset this = StructNew()
cfset this.attribute = "color"
cfquery datasource=#dsn# name="#this.attribute#attributes"
cachedafter=#DateAdd('d', '-1', Now())#
SELECT *
FROMEQUIP.ATTRIBUTE_OPTION
WHERE ATTR_NAME = '#this.attribute#'
Order by ATTR_VALUE
/cfquery
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Speaking of what I want in CF (was RE: Cached Queries and Memory)
Is there any way to tell how much memory is being used by a cached query? Recently I have been thinking about how useful this data would be. I'd love to see a suite of functions in CF which would allow us to determine how much memory is being used by different parts of CF at any given time. Functions which would allow you to see total or average memory used by Sessions, Cached Queries, Cached Templates, CF as an application, etc... So that you could effectively break down the total memory usage of CF and see where it's all going. Or maybe this would be better placed in the CF functionality found in the NT Perfmon... -Cameron Cameron Childress McRae Communications p. 770-460-7277 x.232 f. 770-460-0963 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Speaking of what I want in CF (was RE: Cached Queries and Memory)
Something kinda useful: I think that everybody has forgotten about the 'million' recent postings asking to figure out when a session has timed out. An onsessionend script added to the feature list? (plus of course all the cool things that have been posted to this thread already...) Jared. Cameron Childress wrote: Is there any way to tell how much memory is being used by a cached query? Recently I have been thinking about how useful this data would be. I'd love to see a suite of functions in CF which would allow us to determine how much memory is being used by different parts of CF at any given time. Functions which would allow you to see total or average memory used by Sessions, Cached Queries, Cached Templates, CF as an application, etc... So that you could effectively break down the total memory usage of CF and see where it's all going. Or maybe this would be better placed in the CF functionality found in the NT Perfmon... -Cameron Cameron Childress McRae Communications p. 770-460-7277 x.232 f. 770-460-0963 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
