Re: What scope are cached queries in?

[Tom Chiverton]

On Wednesday 24 January 2007 19:01, DURETTE, STEVEN J (ASI-AIT) wrote:
> This code isn't mine.  I'm just trying to trouble shoot the excessive
> amounts of memory that are being used and not released.  The site has a
> low hit count, so I don't see why it should be using soo much.

Fusion Reactor !

-- 
Tom Chiverton
Helping to dramatically grow one-to-one e-business



This email is sent for and on behalf of Halliwells LLP.

Halliwells LLP is a limited liability partnership registered in England and 
Wales under registered number OC307980 whose registered office address is at St 
James's Court Brown Street Manchester M2 2JF.  A list of members is available 
for inspection at the registered office. Any reference to a partner in relation 
to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law 
Society.

CONFIDENTIALITY

This email is intended only for the use of the addressee named above and may be 
confidential or legally privileged.  If you are not the addressee you must not 
read it and must not use any information contained in nor copy it nor inform 
any person other than Halliwells LLP or the addressee of its existence or 
contents.  If you have received this email in error please delete it and notify 
Halliwells LLP IT Department on 0870 365 8008.

For more information about Halliwells LLP visit www.halliwells.com.


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267579
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: What scope are cached queries in?

[DURETTE, STEVEN J \(ASI-AIT\)]

This code isn't mine.  I'm just trying to trouble shoot the excessive
amounts of memory that are being used and not released.  The site has a
low hit count, so I don't see why it should be using soo much.


-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 24, 2007 12:48 PM
To: CF-Talk
Subject: RE: What scope are cached queries in?


> Is there a scope that holds cached queries and if so how can 
> it be access to see what is in there?

No, there isn't. If you want to manage memory directly, store your
queries
in Session, Application or Server scope.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!




~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267524
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: What scope are cached queries in?

[Dave Watts]

> Is there a scope that holds cached queries and if so how can 
> it be access to see what is in there?

No, there isn't. If you want to manage memory directly, store your queries
in Session, Application or Server scope.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267509
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

What scope are cached queries in?

[DURETTE, STEVEN J \(ASI-AIT\)]

Is there a scope that holds cached queries and if so how can it be
access to see what is in there?

This is related to my previous post about enumerating what is in an
instance's memory block.
 
Thanks,
Steve
 


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267467
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Cached queries

[Casey Dougall]

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

[Tom Chiverton]

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

[James Smith]

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...

do the sql

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

[Tom Chiverton]

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

[Snake]

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

[Che Vilnonis]

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

[Carl L]

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

[Che Vilnonis]

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.com&q=cachedWithin&sa=-%3E&sitesearch=www.houseoffusion.com&client=pub-
9987223904467795&forid=1&channel=3811075283&ie=ISO-8859-1&oe=ISO-8859-1&cof=
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%3B&hl=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

Cached queries

[James Smith]

I have a need to cache some queries.  For safety and security these queries
use cfqueryparam.

You are not allowed to use cachedWithin or cachedAfter with queries that
contain a cfqueryparam.

Any idea how I go about caching these?

--
Jay


~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254258
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Manually flush all cached queries?

[Raymond Camden]

 will clear ALL of them.

To clear one query, rerun the query with the EXACT same settings (sql,
attributes, etc) but with a timeout of zero. When I say exact same -
it must be the exact same even when it comes to white space.

On 9/7/06, Jeff Guillaume <[EMAIL PROTECTED]> wrote:
> I fear I already know the answer to this, but is it possible?
>
> We use cachedwithin queries all over the place and want to be able to "flush" 
> CF's memory when needed to reflect changes from the database immediately.  Is 
> there any way to do this other than restarting the CF service?  I know you 
> can zero out the cachedwithin times, but that doesn't seem feasible as the 
> caches are in all sorts of CFCs and have various timeouts assigned.
>
> Thanks.
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252384
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Manually flush all cached queries?

[Jake Churchill]

Just went through this yesterday.  Basically you can add a space or 
change the query so CF thinks it's a new query and it won't use the 
cached version.  However, that only works for a single query.  
Otherwise, you have to restart the server.

Jeff Guillaume wrote:
> I fear I already know the answer to this, but is it possible?
>
> We use cachedwithin queries all over the place and want to be able to "flush" 
> CF's memory when needed to reflect changes from the database immediately.  Is 
> there any way to do this other than restarting the CF service?  I know you 
> can zero out the cachedwithin times, but that doesn't seem feasible as the 
> caches are in all sorts of CFCs and have various timeouts assigned.
>
> Thanks.
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252377
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Manually flush all cached queries?

[Jeff Guillaume]

I fear I already know the answer to this, but is it possible?

We use cachedwithin queries all over the place and want to be able to "flush" 
CF's memory when needed to reflect changes from the database immediately.  Is 
there any way to do this other than restarting the CF service?  I know you can 
zero out the cachedwithin times, but that doesn't seem feasible as the caches 
are in all sorts of CFCs and have various timeouts assigned.

Thanks.

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252376
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Manually flush all cached queries?

[Jeff Guillaume]

Self-followup:

I researched a bit and came up with .  It seems 
to do the trick, although I've just read elsewhere that it may not clear all 
queries.  However, some is better than none for my purposes and it worked in my 
test just now.

Anyone used it before? I had never even heard about it.

Thanks,
Jeff

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252385
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Manually flush all cached queries?

[Jeff Guillaume]

Thank you, Raymond.  (I just posted my self-followup coming to the same 
conclusion before your response came in.)

Clearing all of the queries is exactly what I wanted to do, so problem solved.

>  will clear ALL of them.
> 
> To clear one query, rerun the query with the EXACT same settings (sql,
> 
> attributes, etc) but with a timeout of zero. When I say exact same -
> it must be the exact same even when it comes to white space.

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252386
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Manually flush all cached queries?

[Sixten Otto]

> I know you can zero out the cachedwithin times, but that 
> doesn't seem feasible as the caches are in all sorts of CFCs and have 
> various timeouts assigned.

It's worth noting that the caching is keyed on the text of the query. And once 
you execute it once with a timeout low enough that it hits the database, that 
updated data will be available to all of the places that query is run with a 
longer timeout. So if you can, immediately after an operation that should 
invalidate the cache, run that query with a zero second window, that new data 
will be visible in all of those other CFCs.

(On a side note, perhaps it would be wise to factor these queries out into 
their own methods/CFCs, with an argument to specify the cachedwithin value that 
should be used. Then your cache-invalidation procedure would just be to invoke 
the same method everything else does, with a lower value. And everyone is 
guaranteed to share the same query code.)

Sixten

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252383
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Cached queries & memory usage

[James Holmes]

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 & memory usage

[Terry Ford]

<< 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

[Barney Boisvert]

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

[Terry Ford]

<< 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

[Barney Boisvert]

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

Cached queries & memory usage

[Terry Ford]

Has anyone found a way to get access to the underlying java object(s) that CFMX 
uses for query caching?

It would be nice to be able to see precisely (a) how many queries have been 
cached and (b) how much memory each one is using at any point in time.  
Additionally, it would be nice to know precisely how the access times for a 
query scales as the number of cached queries increase (a hash?), and how those 
access times compare to self-caching in arrays or structs.

Regards,
Terry

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226507
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Flushing Cached Queries

[Charlie Griefer]

after the database update use a ?

http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b11.htm

On 10/24/05, John Beynon <[EMAIL PROTECTED]> wrote:
> set cachedwithin to 0?
>
> >Okay, this is probably something obvious I'm either overlooking or not
> >thinking of.  For some reason, I can figure out the really complicated
> >things far easier than the ones that should be simple.  Anyway
> >
> >Is there a way to force CF 6.1 to update a cached query programatically.
> >  That is, is there a way to force the query to re-run even if the
> >timeout specified in cachedwithin is not up yet.
> >
> >We've got a client who runs a database update routine at a certain time,
> >and we want to try and run the query just before (to cache it across the
> >update) and just after (to pick up the updated data).  Ideas?
> >
> >--Ben
>
> 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222065
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Flushing Cached Queries

[Justin D. Scott]

> Is there a way to force CF 6.1 to update a cached
> query programatically.

There are a couple of potential options off the top of my head.  If the
times that the query is run are set, you could use a cachedwithin attribute
to force it to expire after a certain period.  If you want to force a flush,
you could invoke the CF factory object and force it to flush all queries.
To invoke the factory...



You can CFDUMP the factory object to find the exact method to call, I don't
remember what it is off hand.


-Justin Scott


~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222063
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: Flushing Cached Queries

[Mark A Kruger]

If it's a specific query you can use
cachedwithin="#creattimespan(0,0,0,0)#"

You might also consider "cachedafter" - sounds like it fits your problem. I
have a blog on this very topic :)

http://mkruger.cfwebtools.com/index.cfm/2005/10/13/safe.caching

-Mark


-Original Message-
From: Ben Doom [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 10:43 AM
To: CF-Talk
Subject: Flushing Cached Queries


Okay, this is probably something obvious I'm either overlooking or not
thinking of.  For some reason, I can figure out the really complicated
things far easier than the ones that should be simple.  Anyway

Is there a way to force CF 6.1 to update a cached query programatically.
  That is, is there a way to force the query to re-run even if the
timeout specified in cachedwithin is not up yet.

We've got a client who runs a database update routine at a certain time,
and we want to try and run the query just before (to cache it across the
update) and just after (to pick up the updated data).  Ideas?

--Ben




~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222061
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Flushing Cached Queries

[John Beynon]

set cachedwithin to 0?

>Okay, this is probably something obvious I'm either overlooking or not 
>thinking of.  For some reason, I can figure out the really complicated 
>things far easier than the ones that should be simple.  Anyway
>
>Is there a way to force CF 6.1 to update a cached query programatically. 
>  That is, is there a way to force the query to re-run even if the 
>timeout specified in cachedwithin is not up yet.
>
>We've got a client who runs a database update routine at a certain time, 
>and we want to try and run the query just before (to cache it across the 
>update) and just after (to pick up the updated data).  Ideas?
>
>--Ben

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222059
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Flushing Cached Queries

[Ben Doom]

Okay, this is probably something obvious I'm either overlooking or not 
thinking of.  For some reason, I can figure out the really complicated 
things far easier than the ones that should be simple.  Anyway

Is there a way to force CF 6.1 to update a cached query programatically. 
  That is, is there a way to force the query to re-run even if the 
timeout specified in cachedwithin is not up yet.

We've got a client who runs a database update routine at a certain time, 
and we want to try and run the query just before (to cache it across the 
update) and just after (to pick up the updated data).  Ideas?

--Ben


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222056
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: cached Queries

[Aaron DC]

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

cached Queries

[Andy Jarrett]

Hi there,

Is there away of telling if a query was generated from a cached query
without setting a variable or anything??

Cheers

Andy
www.andyjarrett.co.uk

~|
Sams Teach Yourself Regular Expressions in 10 Minutes  by Ben Forta 
http://www.houseoffusion.com/banners/view.cfm?bannerid=40

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182463
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

RE: ColdFusion TechNote Notification: "Behavior of cached queries whe n maximum number is set to 0"

[Barney Boisvert]

Can these emails be set up to use short URLs, that will fit in a email
message?  Surely setting up a single RewriteRule in the apache config could
turn them into links like this:

http://www.macromedia.com/cftn/cached_queries_max_zero

Not to say I don't appreaciate them, just it's a pain having to manually
assemble the URL every time.

Cheers,
barneyb

> -Original Message-
> From: Debbie Dickerson [mailto:[EMAIL PROTECTED] 
> Sent: Monday, June 28, 2004 11:58 AM
> To: CF-Talk
> Subject: ColdFusion TechNote Notification: "Behavior of 
> cached queries whe n maximum number is set to 0"
> 
> Check out the new TechNote at:
> http://www.macromedia.com/support/coldfusion/ts/documents/cach
> ed_queries_max
> _zero.htm
> 
> hed_queries_ma
> x_zero.htm> 
> 
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

ColdFusion TechNote Notification: "Behavior of cached queries whe n maximum number is set to 0"

[Debbie Dickerson]

Check out the new TechNote at:
http://www.macromedia.com/support/coldfusion/ts/documents/cached_queries_max
_zero.htm

x_zero.htm> 

Intro: 
Cached queries within Macromedia ColdFusion MX are used to retrieve data
result sets from server memory rather than through a database transaction.
Because cached queries are stored in server memory, the ColdFusion
Administrator allows a user imposed limit to the number of queries that are
cached.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Cached queries

[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  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

[Philip Arnold]

> 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  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

[Barney Boisvert]

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.


  SELECT ...



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 
>  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

[Dave Watts]

> > 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

[Thomas Chiverton]

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

[Dave Watts]

> > 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

[Thomas Chiverton]

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

[Dave Watts]

> 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

[Thomas Chiverton]

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

[Dave Watts]

> > 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

[Dave Watts]

>  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 .  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

[Dave Carabetta]

>
>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

[Dave Watts]

> 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

[Burns, John D]

 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:


 select *
 from Articles
 LIMIT 5


Or at the most, for a specific article with an ID passed through the URL


 select *
 from Articles
 where articleID = #articleID#


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 .  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

[Dave Watts]

> 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

[Dave Watts]

> 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

[Thomas Chiverton]

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

[Burns, John D]

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 
name="frontend"> for the front end and 
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

[Dick Applebaum]

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

[Dave Carabetta]

>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

[Dave Carabetta]

>You can use 
>

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:


  



  


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

[Burns, John D]

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 

 
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

[Qasim Rasheed]

You can use 

 
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]

Cached Queries

[Burns, John D]

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: Clearing all Cached Queries (Attn: Ray C.)

[Qasim Rasheed]

 if I am correct

 
Qasim

-Original Message-
From: Jamie Jackson [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 19, 2004 1:43 PM
To: CF-Talk
Subject: Clearing all Cached Queries (Attn: Ray C.)

Ray,

You told me once of a way to clear out all cached queries at once, but
I'll be darned if I can find the thread.

Would you please repeat?

Thanks,
Jamie 
  _
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: Clearing all Cached Queries (Attn: Ray C.)

[Dave Watts]

> You told me once of a way to clear out all cached queries at 
> once, but I'll be darned if I can find the thread.

I'm not Ray, but:



Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Clearing all Cached Queries (Attn: Ray C.)

[Jamie Jackson]

Ray,

You told me once of a way to clear out all cached queries at once, but
I'll be darned if I can find the thread.

Would you please repeat?

Thanks,
Jamie
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: Cached Queries/Cf Admin

[Philip Arnold]

> From: Scott Mulholland
> 
> Is there any rule of thumb or method for figuring out how to 
> optimize the number of queries to cache setting in Administrator?

There is no "hard and fast" setting for this

It really depends on the applications you have running on your server,
how many queries you have cached, how many variations in the SQL each
has, etc.

Remember that;
SELECT * FROM TABLE WHERE FIELD=1
Is different from
SELECT * FROM TABLE WHERE FIELD=2

Each datasource is also another set of queries

Remember to ensure that you have enough memory in your server to handle
all of the queries, or it'll drop them sooner
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Cached Queries/Cf Admin

[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?

 
Thanks,
Scott
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: Cached Queries

[Jim McAtee]

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

[Dave Watts]

> 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

[Douglas.Knudsen]

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]

Cached Queries

[Jim McAtee]

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: Clearing all Cached Queries at Once?

[Jamie Jackson]

Brilliant, thanks.

Jamie

On Tue, 6 Jan 2004 12:47:04 -0600, in cf-talk you wrote:

>> Q1. However, what if I'd like to clear *all* cached queries 
>> in the entire application in one fell swoop? (Without a 
>> server restart ;-)
>
>
>
>This clears the entire server's set of cached queries.
>
>> Q2. Also, say I've got a dynamic query...
>> 
>> select * from t where c = #v#
>> 
>> ... and I clear the query when v = 1, does this all-at-once 
>> clear the other cached versions (which were cached when v 
>> equaled 2 and v equaled 3)?
>> 
>
>No, cached queries are indexed by their sql and the other attributes to the
>tag.
>
>
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: Clearing all Cached Queries at Once?

[Raymond Camden]

> Q1. However, what if I'd like to clear *all* cached queries 
> in the entire application in one fell swoop? (Without a 
> server restart ;-)



This clears the entire server's set of cached queries.

> Q2. Also, say I've got a dynamic query...
> 
> select * from t where c = #v#
> 
> ... and I clear the query when v = 1, does this all-at-once 
> clear the other cached versions (which were cached when v 
> equaled 2 and v equaled 3)?
> 

No, cached queries are indexed by their sql and the other attributes to the
tag.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Clearing all Cached Queries at Once?

[Jamie Jackson]

It's a pretty simple matter to clear all the queries accessed in a
request, say by setting a "clearcache" url var, and writing the code
to set the cache span to a nil duration when url.clearcache exists.

Q1. However, what if I'd like to clear *all* cached queries in the
entire application in one fell swoop? (Without a server restart ;-)

Q2. Also, say I've got a dynamic query...

select * from t where c = #v#

... and I clear the query when v = 1, does this all-at-once clear the
other cached versions (which were cached when v equaled 2 and v
equaled 3)?

Thanks,
Jamie
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Custom tag to see list of cached queries!

[A Tamuri]

Hello,

I have written a CFX custom tag for CFMX, called cfx_queryCache, which allows you to get a list of all your cached queries and flush individual queries based on its key.

I'd be interested in any ideas for improvements, both functionality and improving the Java code. It's open-source and available from http://tamuri.cfdeveloper.co.uk/

Thanks,

Tamuri
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: How do I flush cached queries?

[S . Isaac Dealey]

Thanks Jeff,

I'm not certain (off the top of my head) if query caching was really
included in the onTap framework. The method I usually use is to create
a structure to hold dates for caching in the application scope. Any
time a given query is run, it uses cfparam to ensure a date value for
that query with the current time as a default for the cachedafter
attribute. Any time data in the relevant tables is updated, I simply
set the value of the application variable to now() and this forces the
application to return to the database for the next query.

If you're concerned about preserving RAM, this isn't going to expire
the queries quickly, since it will wait for the application to expire
before all queries are refreshed. My opinion about memory usage for
data caching as a general rule is this: it's less expensive than
more/faster processors. I have yet to have any legitimate problems
caused by the amount of available RAM on a server -- although oddly
enough I did once run into a problem with one client who didn't have
enough physical storage (hd space) on a server. ;P Iirc that was
caused largely by the frequency and longevity of their database
backups -- creating them too often and keeping them too long.

Because most of the queries in my CMS application are from stored
procedures, cachedwithin isn't available for those, so I've created
similar caching routines for storing structures and other types of
data. There's a content.cfm custom tag in the framework that stores
strings of html output in the application scope and uses cachedwithin
and cachedafter as attributes by using datediff() to compare now()
with the timestamp for the given bit of content.

Lately lurking is probably fairly appropriate unfortunately. I've been
pretty busy trying to wrap up a client project and starting a new
project that have been taking up much of my time. I lost the
transmission in my car, and to add insult to injury, my apartment
complex poisoned me. They laquered or painted something in the
apartment next door and it's been ventilating into my unit, directly
over my desk. I've been prone to ear infections in the past, so when I
started getting dizzy I didn't think much of it. I breathed it in for
a little over a day before I passed out and we realized what was
happening. I'm staying at a friend's place right now and working from
here, but it's like pulling teeth trying to get any kind of recompense
from the complex for the fact that they've injured me both physically
and financially. All they want to do is wave their hands in the air
and say "it's fine now".

s. isaac dealey    972-490-6624

team macromedia volunteer  http://www.macromedia.com/go/team

chief architect, tapestry cms  http://products.turnkey.to

onTap is open source   http://www.turnkey.to/ontap

> Oops, I am pretty sure this works in CF5 but I can't
> remember if I used
> it back then or not. This definitely works in MX. Also,
> the
> "cachewithin" attribute is pretty much a must unless you
> have a query
> you want to keep indefinitely (be nice to your RAM memory,
> it is your
> friend). Another point, this will clear all queries in the
> cache so it
> may be overkill. The previous suggestion about setting the
> cachewithin
> attribute to '0' is a surefire way to kill the cache on
> just a single
> query. S. Isaac (who is lurking on this list daily) has
> some pretty neat
> ways to cache queries within his framework, onTap.

> Good luck,
> -JSLucido

> -Original Message-
> From: Jeff Lucido [mailto:[EMAIL PROTECTED]
> Sent: Friday, October 10, 2003 8:20 AM
> To: CF-Talk
> Subject: RE: How do I flush cached queries?

> 

> Good luck,
> -JSLucido

> -Original Message-
> From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED]
> Sent: Friday, October 10, 2003 8:03 AM
> To: CF-Talk
> Subject: How do I flush cached queries?

> I have tried using the undocumented 
> CFUSION_DBCONNECTIONS_FLUSH()>  but it is not flushing
> query results and
> forcing a new query (I want to flush the cached queryies
> whenever an
> administrator updates info in the database).  Is there any
> other way to
> flush cached query results?  I am using CF5 on Win2K with
> MS SQL Server
> 2000.

> Thanks

> Pete

>   _

>   _

> 
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: How do I flush cached queries?

[Pete Ruckelshaus]

The problem is with caching dynamic queries.  If you cache a query that has dynamic statements, it will cache the result set from each dynamic statement that is run.

I'll try Jeff's solution.

Thanks for the help!

Pete
  - Original Message - 
  From: Philip Arnold 
  To: CF-Talk 
  Sent: Friday, October 10, 2003 9:18 AM
  Subject: RE: How do I flush cached queries?

  I thought that DBCOMMECTIONS_FLUSH only flushed the ODBC connectors, not
  the cached queries

  Anyways, the way I do it is to put a CreateTimeSpan(0,0,0,0) in the
  CACHEDWITHIN, that tells CF to get the latest data

  If you're using CACHEDWITHIN, then I'd suggest putting the time span
  into a variable, and then when a certain URL condition is met, then set
  the time span to 0 - thus flushing all queries

  -Original Message-
  From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] 
  Sent: Friday, October 10, 2003 9:03 AM
  To: CF-Talk
  Subject: How do I flush cached queries?

  I have tried using the undocumented 
  CFUSION_DBCONNECTIONS_FLUSH()>  but it is not flushing query results and
  forcing a new query (I want to flush the cached queryies whenever an
  administrator updates info in the database).  Is there any other way to
  flush cached query results?  I am using CF5 on Win2K with MS SQL Server
  2000.

  Thanks

  Pete

    _  


 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: How do I flush cached queries?

[Ian Vaughan]

I have the following query, however if I enter more than one value in the search form such as

Surname = Vaughan
Funding = budget


SELECT * FROM funding
WHERE 1=1


    AND UPPER(orgname) LIKE UPPER(
value="%#form.orgname#%">)


    AND UPPER(funding) LIKE UPPER(
value="%#form.funding#%">)


    AND UPPER(surname) LIKE UPPER(
value="%#form.surname#%">)


AND IsNumeric(form.commdayfrom) AND IsNumeric(form.commyearto) AND
IsNumeric(form.commmonthto) AND IsNumeric(form.commdayto)>

   
 AND IssueDate BETWEEN
    TO_DATE('#form.commyearfrom#-#form.commmonthfrom#-#form.commdayfrom#','-MM-DD')
    AND
    TO_DATE('#form.commyearto#-#form.commmonthto#-#form.commdayto#','-MM-DD')

 



--

This is the output from debugging

 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: How do I flush cached queries?

[Jeff Lucido]

Oops, I am pretty sure this works in CF5 but I can't remember if I used
it back then or not. This definitely works in MX. Also, the
"cachewithin" attribute is pretty much a must unless you have a query
you want to keep indefinitely (be nice to your RAM memory, it is your
friend). Another point, this will clear all queries in the cache so it
may be overkill. The previous suggestion about setting the cachewithin
attribute to '0' is a surefire way to kill the cache on just a single
query. S. Isaac (who is lurking on this list daily) has some pretty neat
ways to cache queries within his framework, onTap.

 
Good luck,
-JSLucido

-Original Message-
From: Jeff Lucido [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 10, 2003 8:20 AM
To: CF-Talk
Subject: RE: How do I flush cached queries?



Good luck,
-JSLucido

-Original Message-
From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 10, 2003 8:03 AM
To: CF-Talk
Subject: How do I flush cached queries?

I have tried using the undocumented 
CFUSION_DBCONNECTIONS_FLUSH()>  but it is not flushing query results and
forcing a new query (I want to flush the cached queryies whenever an
administrator updates info in the database).  Is there any other way to
flush cached query results?  I am using CF5 on Win2K with MS SQL Server
2000.

Thanks

Pete

  _  

  _  


 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: How do I flush cached queries?

[Jeff Lucido]

 
Good luck,
-JSLucido

 
-Original Message-
From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 10, 2003 8:03 AM
To: CF-Talk
Subject: How do I flush cached queries?

I have tried using the undocumented 
CFUSION_DBCONNECTIONS_FLUSH()>  but it is not flushing query results and
forcing a new query (I want to flush the cached queryies whenever an
administrator updates info in the database).  Is there any other way to
flush cached query results?  I am using CF5 on Win2K with MS SQL Server
2000.

Thanks

Pete

  _  


 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: How do I flush cached queries?

[Philip Arnold]

I thought that DBCOMMECTIONS_FLUSH only flushed the ODBC connectors, not
the cached queries

 
Anyways, the way I do it is to put a CreateTimeSpan(0,0,0,0) in the
CACHEDWITHIN, that tells CF to get the latest data

 
If you're using CACHEDWITHIN, then I'd suggest putting the time span
into a variable, and then when a certain URL condition is met, then set
the time span to 0 - thus flushing all queries

-Original Message-
From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 10, 2003 9:03 AM
To: CF-Talk
Subject: How do I flush cached queries?

I have tried using the undocumented 
CFUSION_DBCONNECTIONS_FLUSH()>  but it is not flushing query results and
forcing a new query (I want to flush the cached queryies whenever an
administrator updates info in the database).  Is there any other way to
flush cached query results?  I am using CF5 on Win2K with MS SQL Server
2000.

Thanks

Pete

  _  


 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

How do I flush cached queries?

[Pete Ruckelshaus]

I have tried using the undocumented   but it is not flushing query results and forcing a new query (I want to flush the cached queryies whenever an administrator updates info in the database).  Is there any other way to flush cached query results?  I am using CF5 on Win2K with MS SQL Server 2000.

Thanks

Pete

 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: Flushing All Cached Queries

[jon hall]

Thursday, February 27, 2003, 6:48:53 PM, you wrote:
>> Supposedly...I've never found that to be the case though. 
>> It only seems to work at a template level for me, refreshing 
>> any cached queries that are executed after the cfobjectcache 
>> statement during the request. Any other cached queries for 
>> that application remain cached, unaffected by cfobjectcache. 
>> Any cached queries that are executed before the cfobjectcache
>> statement will refresh the next time the query is executed.
>> This applies to CF5, I don't know about MX.
>> 
>> If there is a trick I am missing I'd love to know about it.

DW> That's odd. I remember you mentioning this before. I just built a very
DW> simple test case, consisting of two separate pages, one containing a CFQUERY
DW> with CACHEDWITHIN and the other containing CFOBJECTCACHE. I then turned on
DW> debugging, and ran the query page twice (the second time to cache the
DW> query), then ran the second page, then reran the query page, and the query
DW> was no longer cached. I did this under CF 5 and CFMX. It worked under both.

Running a quick test case seemed to confirm that is does indeed work
as it should using cachedwithin queries. Running the test case though
I found out that apparently CF ignores the name attribute of a cached
query when deciding whether to return a cached recordset it and only
uses the sql. Learn something new every day...

This may have been the cause of my confusion, as the only place I have
tested cfobjectcache was on a server with a bunch of nearly identical
applications. So i would go from site to site seeing the queries
cached, even though the page had never been executed. For now, I guess
I'll have to take back my post. :)

-- 
 jon
 mailto:[EMAIL PROTECTED]

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Flushing All Cached Queries

[Andrew Tyrone]

> -Original Message-
> From: jon hall [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 27, 2003 5:21 PM
> To: CF-Talk
> Subject: Re: Flushing All Cached Queries
>
>
> Supposedly...I've never found that to be the case though. It only
> seems to work at a template level for me, refreshing any cached
> queries that are executed after the cfobjectcache statement during the
> request. Any other cached queries for that application remain cached,
> unaffected by cfobjectcache. Any cached queries that are executed
> before the cfobjectcache statement will refresh the next time the
> query is executed.
> This applies to CF5, I don't know about MX.
>
> If there is a trick I am missing I'd love to know about it.

I just tested this on CF 5.0 and CFMX, and I got the same behavior.
CFOBJECTCACHE clears all queries that are cached with cachedwithin, no
matter what application they belong to.

I created a directory structure like so:

codetesting
  objectcache
app1
app2

App1 and App2 had their own application.cfm and index.cfm files.  App1 was
named "CodeTestingApp1", App2 was named "CodeTestingApp2".  I used the same
queries in each index.cfm page first, then I tested with two different
queries.  Each time I ran the test, both queries had different names.
Performing a cfobjectcache action="clear" in the index.cfm in App2 after all
other cfml, the query was never cached, as expected.  After running App1 to
cache the query, running App2 causes a different query to be run and after,
the object cache is cleared.  Running App1 right after this shows that
cfobjectcache did indeed clear the cached query in App1.  I then tested
again with queries cached in the Application scope, not using cachedwithin.
CFOBJECTCACHE does not affect queries cached in the Application scope, it
seems; it only affects queries cached with cachedwithin and perhaps (not
tested) cachedafter.

The documentation on this tag is somewhat confusing.  The action="CLEAR"
attribute says it "Clears queries from the cache in the Application scope",
while the actual Description of the tag says "Flushes the query cache."

Since this is obviously not all true (cfobjectcache does NOT clear queries
in the Application scope, based on my testing in CF 5.0 and CFMX), the docs
on this tag don't appear to be fully correct.  The Livedocs on this tag are
the same as the ones I have installed in CF Studio 4.5.2.

--Andy


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Flushing All Cached Queries

[Dave Watts]

> Supposedly...I've never found that to be the case though. 
> It only seems to work at a template level for me, refreshing 
> any cached queries that are executed after the cfobjectcache 
> statement during the request. Any other cached queries for 
> that application remain cached, unaffected by cfobjectcache. 
> Any cached queries that are executed before the cfobjectcache
> statement will refresh the next time the query is executed.
> This applies to CF5, I don't know about MX.
> 
> If there is a trick I am missing I'd love to know about it.

That's odd. I remember you mentioning this before. I just built a very
simple test case, consisting of two separate pages, one containing a CFQUERY
with CACHEDWITHIN and the other containing CFOBJECTCACHE. I then turned on
debugging, and ran the query page twice (the second time to cache the
query), then ran the second page, then reran the query page, and the query
was no longer cached. I did this under CF 5 and CFMX. It worked under both.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Flushing All Cached Queries

[jon hall]

Supposedly...I've never found that to be the case though. It only
seems to work at a template level for me, refreshing any cached
queries that are executed after the cfobjectcache statement during the
request. Any other cached queries for that application remain cached,
unaffected by cfobjectcache. Any cached queries that are executed
before the cfobjectcache statement will refresh the next time the
query is executed.
This applies to CF5, I don't know about MX.

If there is a trick I am missing I'd love to know about it.

-- 
 jon
 mailto:[EMAIL PROTECTED]

Thursday, February 27, 2003, 5:05:28 PM, you wrote:
>> When using the ,
>> Does it flush just the cached queries in the application, 
>> or on all applications across the server?

DW> It flushes all cached queries on the server.

DW> Dave Watts, CTO, Fig Leaf Software
DW> http://www.figleaf.com/
DW> voice: (202) 797-5496
DW> fax: (202) 797-5444

DW> 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Flushing All Cached Queries

[Dave Watts]

> When using the ,
> Does it flush just the cached queries in the application, 
> or on all applications across the server?

It flushes all cached queries on the server.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Flushing All Cached Queries

[Mark Leder]

When using the ,
Does it flush just the cached queries in the application, or on all
applications across the server?

Thanks, Mark 

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Cached Queries

[jon hall]

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.

















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=lists&body=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

[Byron M]

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
> >
> > 
> > ... whole lotta code, then the query occurs
> > 
> > 
> > 
> > ... skip all that code
> > 
> >
> > 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=lists&body=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

[Matthew Walker]

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
> 
> 
> ... whole lotta code, then the query occurs
> 
> 
> 
> ... skip all that code
> 
> 
> 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=lists&body=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.

Cached Queries

[Byron M]

I have a bunch of code that I don't want to execute if a query is cached.

I tried this


... whole lotta code, then the query occurs



... skip all that code


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=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

RE: Making use of cached queries

[Ken Brocx]

>And last but not least, how should I treat Yes/No fields in
>a QoQ?  For example something like "WHERE ProductID = 2 AND approved =
>Yes" causes a "The select column reference [Yes] is not a column in any
>of the tables of the FROM table list." error.  Is this a task for
>? 

I always use queryparam lately just solves a lot of problems.
It depends on your database. Which are you using? And what type of field
is it? Is it an actual yes/no field or a bit field with 1/0? I haven't
had any problems with bit fields or boolean fields in a QoQ but I'm
using SQLServer2k.

I'd try either using 

Or 

Although IMHO all yes/no fields should just be bit fields
Good luck have a great weekend!
Ken


 

-Original Message-
From: W Luke [mailto:[EMAIL PROTECTED]] 
Sent: Friday, October 04, 2002 4:21 PM
To: CF-Talk
Subject: Re: Making use of cached queries

"Ken Brocx" <[EMAIL PROTECTED]> wrote in message
news:<[EMAIL PROTECTED]>...

>> Does it have a problem in calling "TOP 2" records?  For instance:

> Try
> 
> 
> SELECT ProductName
> FROM AllPaid
> WHERE ProductID = 5
> 

Thanks Ken.  And last but not least, how should I treat Yes/No fields in
a QoQ?  For example something like "WHERE ProductID = 2 AND approved =
Yes" causes a "The select column reference [Yes] is not a column in any
of the tables of the FROM table list." error.  Is this a task for
?

Will



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Re: Making use of cached queries

[Jochem van Dieten]

W Luke wrote:
> "Marius Milosav" <[EMAIL PROTECTED]> wrote in message
> news:<000d01c26bf5$17838510$[EMAIL PROTECTED]>... 
> 
>>replace the double quotes around "Yes"  with single quotes 'Yes' and
>>it should run OK. 
> 
> 
> There shouldn't be any quotes, single or double - no need for them.  I
> tried single quotes anyway, and it caused a new (and apparently more
> common) error: "Unsupported type comparison."  I've read lots from
> people having problems with these QoQs migrating from 5-to-MX - has
> anyone got any advice?

Lots of people get the same advice: cfqueryparam :)

Actually, I have never tried it with a boolean field before, and boolean 
is not a supported datatype for cfqueryparam (although you might try 
bit). Also, I would imagine it depends quite a bit on what your database 
returns *exactly*. Does it return YES/NO, TRUE/FALSE, 't'/'f' or 1/0? 
All of these are options to try, but based on the error I would go for 
either:


   SELECT ProductName
   FROM   AllPaid
   WHERE  ProductID = 5
  AND approved = 1


or:


   SELECT ProductName
   FROM   AllPaid
   WHERE  ProductID = 5
  AND approved = 


Jochem

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Re: Making use of cached queries

[W Luke]

"Marius Milosav" <[EMAIL PROTECTED]> wrote in message
news:<000d01c26bf5$17838510$[EMAIL PROTECTED]>... 
> replace the double quotes around "Yes"  with single quotes 'Yes' and
> it should run OK. 

There shouldn't be any quotes, single or double - no need for them.  I
tried single quotes anyway, and it caused a new (and apparently more
common) error: "Unsupported type comparison."  I've read lots from
people having problems with these QoQs migrating from 5-to-MX - has
anyone got any advice?  Seems a great shame I won't be able to make use
of QoQ's

Will


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Re: Making use of cached queries

[Marius Milosav]

replace the double quotes around "Yes"  with single quotes 'Yes' and it
should run OK.

Marius Milosav
www.scorpiosoft.com
It's not about technology, it's about people.
Virtual Company (VICO) Application Demo
www.scorpiosoft.com/vicodemo/login.cfm

- Original Message -
From: "W Luke" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, October 04, 2002 7:20 PM
Subject: Re: Making use of cached queries


> "Ken Brocx" <[EMAIL PROTECTED]> wrote in message
> news:<[EMAIL PROTECTED]>...
>
> >> Does it have a problem in calling "TOP 2" records?  For instance:
>
> > Try
> >
> > 
> > SELECT ProductName
> > FROM AllPaid
> > WHERE ProductID = 5
> > 
>
> Thanks Ken.  And last but not least, how should I treat Yes/No fields in
> a QoQ?  For example something like "WHERE ProductID = 2 AND approved =
> Yes" causes a "The select column reference [Yes] is not a column in any
> of the tables of the FROM table list." error.  Is this a task for
> ?
>
> Will
>
>
> 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Re: Making use of cached queries

[W Luke]

"Ken Brocx" <[EMAIL PROTECTED]> wrote in message
news:<[EMAIL PROTECTED]>...

>> Does it have a problem in calling "TOP 2" records?  For instance:

> Try
> 
> 
> SELECT ProductName
> FROM AllPaid
> WHERE ProductID = 5
> 

Thanks Ken.  And last but not least, how should I treat Yes/No fields in
a QoQ?  For example something like "WHERE ProductID = 2 AND approved =
Yes" causes a "The select column reference [Yes] is not a column in any
of the tables of the FROM table list." error.  Is this a task for
?

Will


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

RE: Making use of cached queries

[Ken Brocx]

Try 


SELECT ProductName
FROM AllPaid
WHERE ProductID = 5


Ken
 
 

-Original Message-
From: W Luke [mailto:[EMAIL PROTECTED]] 
Sent: Friday, October 04, 2002 4:05 PM
To: CF-Talk
Subject: Re: Making use of cached queries

W Luke <mailto:[EMAIL PROTECTED]> wrote:

> If I understand what you're asking you should be able to accomplish
> that by using QueryOfQuery functionality which lets u use regular SQL
> to query an existing recordset in memory. 

Completely forgot about QoQ - been salivating over that for years, and
only now (having moved to MX) do I have a chance to use it!  

Does it have a problem in calling "TOP 2" records?  For instance:


SELECT TOP 2 ProductName
FROM AllPaid
WHERE ProductID = 5


That causes an error with me.  Any ideas?

Will



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Re: Making use of cached queries

[W Luke]

W Luke  wrote:

> If I understand what you're asking you should be able to accomplish
> that by using QueryOfQuery functionality which lets u use regular SQL
> to query an existing recordset in memory. 

Completely forgot about QoQ - been salivating over that for years, and
only now (having moved to MX) do I have a chance to use it!  

Does it have a problem in calling "TOP 2" records?  For instance:


SELECT TOP 2 ProductName
FROM AllPaid
WHERE ProductID = 5


That causes an error with me.  Any ideas?

Will


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

RE: Making use of cached queries

[Stacy Young]

Hi Will,

If I understand what you're asking you should be able to accomplish that by
using QueryOfQuery functionality which lets u use regular SQL to query an
existing recordset in memory.

http://livedocs.macromedia.com/cfmxdocs/Developing_ColdFusion_MX_Application
s_with_CFML/using_recordsets.jsp

Stace

-Original Message-
From: W Luke [mailto:[EMAIL PROTECTED]] 
Sent: Friday, October 04, 2002 6:12 PM
To: CF-Talk
Subject: Making use of cached queries

Hi.

I have a cached query, as below, which runs on a number of pages:


SELECT sub_name,cat_name FROM categories, sub_cats 
WHERE categories.ID = sub_cats.cat_id
ORDER by cat_name


The data in the query will only be changed monthly, at most.  If I had a
var such as  (which would correspond to
categories.ID), could I grab categories.cat_name from the cached query
without altering it, somehow?

I could probably have worded it better, but I hope it's enough for
someone to understand!

Thanks

Will



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=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

Making use of cached queries

[W Luke]

Hi.

I have a cached query, as below, which runs on a number of pages:


SELECT sub_name,cat_name FROM categories, sub_cats 
WHERE categories.ID = sub_cats.cat_id
ORDER by cat_name


The data in the query will only be changed monthly, at most.  If I had a
var such as  (which would correspond to
categories.ID), could I grab categories.cat_name from the cached query
without altering it, somehow?

I could probably have worded it better, but I hope it's enough for
someone to understand!

Thanks

Will


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

RE: failed retrieval of cached queries

[Tim Stadinski]

it is a very large application that I just got involved with and hadn't seen
that error before.  Don't worry about the cop out answer, I have been trying
to put my finger on 1 thing, but can't seem to nail it just yet.  I have
seen that positing by  Greg Gauthier and have verified that it could
possibly be that the app is exceeding the number of cached queries as set in
the administrator.  I also heard at the DevCon2001 that CFQUERY does not
scale very well in large cf sites.  I think this might also be the case
here.  

If I get to the root of the evil here, I will let you know.

Thanks for the food for thought.

Cheers,

Tim

-Original Message-
From: Alistair Davidson [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 29, 2001 11:25 AM
To: CF-Talk
Subject: RE: failed retrieval of cached queries


Hmmmtricky!

1) How do you determine if it is corrupt?
Um if it throws a "Retrieval of cached query failed" error, then it's
corrupt! I know it's a cop-out answer, but we haven't been able to find any
other way - you don't seem to be able to CFCATCH the error.

2) What makes a cached query corrupt?
Well, the situation in 3) in my original response is one way - there's
probably many others. Here's a quote from Greg Gauthier (Allaire Sr. Tech.
Support Engineer) in the forums -

"This error typically happens in one of three cases:

1) If you were trying to insert a query into a full cache 
where all of the queries were being used you'd get the 
error. 

2) Another place where this could happen is if the cache 
max size is set to zero and you try to cache a query 

3) Finally this occurs when global access to the cache is 
granted initially, but because the page timeout has 
been reached the retrieval fails. "

Does the page where this error occurs take a long time to run?

Alistair Davidson
Senior Developer
Rocom New Media
www.rocomx.net
"There is no spoon"

-Original Message-
From: Tim Stadinski [mailto:[EMAIL PROTECTED]]
Sent: 29 October 2001 15:38
To: CF-Talk
Subject: RE: failed retrieval of cached queries


Of all the points you have made, I agree about the locking issues, but that
is not the case here.  One point that I am curious about (6)If the cached
query is corrupt  How can you determine if it is corrupt and what makes
a cached query corrupt?

Thanks

Tim


-Original Message-
From: Alistair Davidson [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 29, 2001 3:50 AM
To: CF-Talk
Subject: RE: failed retrieval of cached queries


Hi Tim

I don't know how much of this response will be relevant to your situation,
but here's how we managed to resolve this problem - (apologies if any of
this seems patronising, but I don't know what level to aim at here, so I'll
explain everything!)

We had this problem, and it was driving us absolutely nuts for months. Yeah,
sure, some of our old sites (written before any of the current developers
had joined the company) had some dubious locking strategies, but the errors
kept being thrown on our NEW sites, which used REQUEST variables rather than
APPLICATION variables, and had EVERY read/write to a shared scope inside a
CFLOCK.

After many LNG head-scratching hours, we finally worked out that the
following situation could occur -

1) Some old sites were using statements like this -


2) Although WRITES to the application variables were cflocked, statements
such as the above were not.

3) If that CFQUERY statement was run at exactly the same time as
APPLICATION.QueryCacheTime was being set, then the query would still be
executed and the resultset cached, but the time for which to hold the
resultset in the cache could be corrupt.

4) "New" sites were also running statements like this


against the same datasource as the old sites, and just occasionally, the SQL
might match exactly.

5) The CF engine checks to see if it already has a query called
qry_productfeatures on that datasource with the same SQL. If it does, it
checks the current time against the cached query's cache time, to see if the
query has expired.

6) If the cached query is corrupt, then the  statement does NOT
return an catchable exception - the CF server keeps trying to retrieve it,
and eventually (after about 60 seconds, in our case) it gives up, and the CF
sub-system throws an error WITHOUT returning execution to your template. 

7) For all the time that the engine is trying to retrieve that query, the
thread used to process that request is unresponsive. It does NOT timeout at
the "Timeout request at X seconds" setting in the CF administrator.

8) If this is on a frequently hit page, it can very easily clog up the whole
thread pool, and leave your CF server unresponsive. 

9) The only way to free up the threads again is to bounce the CF service.

Conclusions
---

1) DON'T use application variables if you don't have to! Use the Req

RE: failed retrieval of cached queries

[Alistair Davidson]

Hmmmtricky!

1) How do you determine if it is corrupt?
Um if it throws a "Retrieval of cached query failed" error, then it's
corrupt! I know it's a cop-out answer, but we haven't been able to find any
other way - you don't seem to be able to CFCATCH the error.

2) What makes a cached query corrupt?
Well, the situation in 3) in my original response is one way - there's
probably many others. Here's a quote from Greg Gauthier (Allaire Sr. Tech.
Support Engineer) in the forums -

"This error typically happens in one of three cases:

1) If you were trying to insert a query into a full cache 
where all of the queries were being used you'd get the 
error. 

2) Another place where this could happen is if the cache 
max size is set to zero and you try to cache a query 

3) Finally this occurs when global access to the cache is 
granted initially, but because the page timeout has 
been reached the retrieval fails. "

Does the page where this error occurs take a long time to run?

Alistair Davidson
Senior Developer
Rocom New Media
www.rocomx.net
"There is no spoon"

-Original Message-
From: Tim Stadinski [mailto:[EMAIL PROTECTED]]
Sent: 29 October 2001 15:38
To: CF-Talk
Subject: RE: failed retrieval of cached queries


Of all the points you have made, I agree about the locking issues, but that
is not the case here.  One point that I am curious about (6)If the cached
query is corrupt  How can you determine if it is corrupt and what makes
a cached query corrupt?

Thanks

Tim


-Original Message-
From: Alistair Davidson [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 29, 2001 3:50 AM
To: CF-Talk
Subject: RE: failed retrieval of cached queries


Hi Tim

I don't know how much of this response will be relevant to your situation,
but here's how we managed to resolve this problem - (apologies if any of
this seems patronising, but I don't know what level to aim at here, so I'll
explain everything!)

We had this problem, and it was driving us absolutely nuts for months. Yeah,
sure, some of our old sites (written before any of the current developers
had joined the company) had some dubious locking strategies, but the errors
kept being thrown on our NEW sites, which used REQUEST variables rather than
APPLICATION variables, and had EVERY read/write to a shared scope inside a
CFLOCK.

After many LNG head-scratching hours, we finally worked out that the
following situation could occur -

1) Some old sites were using statements like this -


2) Although WRITES to the application variables were cflocked, statements
such as the above were not.

3) If that CFQUERY statement was run at exactly the same time as
APPLICATION.QueryCacheTime was being set, then the query would still be
executed and the resultset cached, but the time for which to hold the
resultset in the cache could be corrupt.

4) "New" sites were also running statements like this


against the same datasource as the old sites, and just occasionally, the SQL
might match exactly.

5) The CF engine checks to see if it already has a query called
qry_productfeatures on that datasource with the same SQL. If it does, it
checks the current time against the cached query's cache time, to see if the
query has expired.

6) If the cached query is corrupt, then the  statement does NOT
return an catchable exception - the CF server keeps trying to retrieve it,
and eventually (after about 60 seconds, in our case) it gives up, and the CF
sub-system throws an error WITHOUT returning execution to your template. 

7) For all the time that the engine is trying to retrieve that query, the
thread used to process that request is unresponsive. It does NOT timeout at
the "Timeout request at X seconds" setting in the CF administrator.

8) If this is on a frequently hit page, it can very easily clog up the whole
thread pool, and leave your CF server unresponsive. 

9) The only way to free up the threads again is to bounce the CF service.

Conclusions
---

1) DON'T use application variables if you don't have to! Use the Request
scope instead.

2) If you HAVE to use application variables (e.g. to store complex
persistent structures that are based on huge DB queries that take ages to
run), then Duplicate() the application structure into the REQUEST scope in
your application.cfm, like this -





3) Make sure ANY references to shared scopes are inside a lock of the same
name (or scope) as the writes to those variables. Enforce this on a CODE
level-  don't rely on the "Automatic locking" setting in CFAdmin, because
this only checks  statements, and DOES NOT catch implied reads
like the  statement.

Hope that helps

Alistair Davidson
Senior Developer
Rocom New Media
www.rocomx.net
"There is no spoon"

-Original Message-
From: Tim Stadinski [mailto:[EMAIL PROTECTED]]
Sent: 25 October 2001 22:12
T

RE: failed retrieval of cached queries

[Tim Stadinski]

Of all the points you have made, I agree about the locking issues, but that
is not the case here.  One point that I am curious about (6)If the cached
query is corrupt  How can you determine if it is corrupt and what makes
a cached query corrupt?

Thanks

Tim


-Original Message-
From: Alistair Davidson [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 29, 2001 3:50 AM
To: CF-Talk
Subject: RE: failed retrieval of cached queries


Hi Tim

I don't know how much of this response will be relevant to your situation,
but here's how we managed to resolve this problem - (apologies if any of
this seems patronising, but I don't know what level to aim at here, so I'll
explain everything!)

We had this problem, and it was driving us absolutely nuts for months. Yeah,
sure, some of our old sites (written before any of the current developers
had joined the company) had some dubious locking strategies, but the errors
kept being thrown on our NEW sites, which used REQUEST variables rather than
APPLICATION variables, and had EVERY read/write to a shared scope inside a
CFLOCK.

After many LNG head-scratching hours, we finally worked out that the
following situation could occur -

1) Some old sites were using statements like this -


2) Although WRITES to the application variables were cflocked, statements
such as the above were not.

3) If that CFQUERY statement was run at exactly the same time as
APPLICATION.QueryCacheTime was being set, then the query would still be
executed and the resultset cached, but the time for which to hold the
resultset in the cache could be corrupt.

4) "New" sites were also running statements like this


against the same datasource as the old sites, and just occasionally, the SQL
might match exactly.

5) The CF engine checks to see if it already has a query called
qry_productfeatures on that datasource with the same SQL. If it does, it
checks the current time against the cached query's cache time, to see if the
query has expired.

6) If the cached query is corrupt, then the  statement does NOT
return an catchable exception - the CF server keeps trying to retrieve it,
and eventually (after about 60 seconds, in our case) it gives up, and the CF
sub-system throws an error WITHOUT returning execution to your template. 

7) For all the time that the engine is trying to retrieve that query, the
thread used to process that request is unresponsive. It does NOT timeout at
the "Timeout request at X seconds" setting in the CF administrator.

8) If this is on a frequently hit page, it can very easily clog up the whole
thread pool, and leave your CF server unresponsive. 

9) The only way to free up the threads again is to bounce the CF service.

Conclusions
---

1) DON'T use application variables if you don't have to! Use the Request
scope instead.

2) If you HAVE to use application variables (e.g. to store complex
persistent structures that are based on huge DB queries that take ages to
run), then Duplicate() the application structure into the REQUEST scope in
your application.cfm, like this -





3) Make sure ANY references to shared scopes are inside a lock of the same
name (or scope) as the writes to those variables. Enforce this on a CODE
level-  don't rely on the "Automatic locking" setting in CFAdmin, because
this only checks  statements, and DOES NOT catch implied reads
like the  statement.

Hope that helps

Alistair Davidson
Senior Developer
Rocom New Media
www.rocomx.net
"There is no spoon"

-Original Message-
From: Tim Stadinski [mailto:[EMAIL PROTECTED]]
Sent: 25 October 2001 22:12
To: CF-Talk
Subject: failed retrieval of cached queries


Has anyone seen the following error:

CFQuery

Retrieval of cached query failedThe error occurred while processing an
element with a general identifier of (CFQUERY), occupying document position
(401:3) to (401:153) in the template file

I have posted this on the forums, but have not got a response and it seems
like there are others with similiar issues.

thanks in advance.

Timothy Stadinski
Afternic.com
[EMAIL PROTECTED]


~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists