Can cachedwithin use a variable?

[BreakawayPaul]

I'm trying to cache a query on our live web server, but not on our testing server (developers need to see changes realtime, but the public doesn't).

I tried something like this:

<cfif listfirst(servername,".") eq "test"><cfset cachetime = 5><cfelse><cfset cachetime = 0></cfif>

<cfquery name="GetContactInfo" datasource="MyDSN" cachedwithin=#createtimespan(0,0,cache,0)#">

SELECT....

But the results were unexpected (it didn't seem to work).

Is there a way to do this?

[Dan_Bracuk]

Your coding does is the opposite of your stated intent. 

[12Robots]

Oh I see.  His solution would have worked if he had not mixed up "test' and else. Good catch.

jason

[BreakawayPaul]

Hah! This is what I get for typing on the fly and not reading it over.  But the general gist was conveyed.

12Robots, I hadn't thought to try it that way.  Lemme see how it works.

[12Robots · Accepted solution]

Try this:

<cfif listfirst(servername,".") eq "test"><cfset cachetime = createtimespan(0,0,0,0)><cfelse><cfset cachetime = createtimespan(0,0,5,0)></cfif>

<cfquery name="GetContactInfo" datasource="MyDSN" cachedwithin=#cachetime#">

Jason

[BreakawayPaul]

Well it seems to work (no errors) but the database is still getting hit like once per second for some reason.  I think either someone is reloading a page like crazy or I have another page that's hitting it with another query that I've forgotten about.

Maybe this will light a fire under their rears to get them to dump Access and get something real.

[12Robots]

Access?  Please tell them, for me, that they should be embarrassed and ashamed. They clearly do not care about the site, the people who visit it, or the people who work on it.

Maybe they should jsut switch to shared hosting too.

Jason

P.S. Just to be clear, all you people that use shared hosting, I AM implying that if you use shared hosting that you don't care about your site.

[BreakawayPaul]

I've been bugging them to switch for years, but as with many things in government, glaciers move faster.

They have a MSSQL license.  Maybe someday they'll blow the dust off of it.

[BreakawayPaul]

I might have to undo all this, as the pages are now loading super slow.  It's a big query, and there are a lot of pages, and since the query is different for each page, I didn't see any sense in caching each individual iteration.  So I took out the "where" statements and cached the entire table, then did a QoQ to grab the page info.

I guess maybe the entire table (well, more than one since I have a few joined) was too much.  It's the only reason I can think of for the pages loading slowly.

[Dan_Bracuk]

If you bring too much data into ColdFusion, QofQ will get very slow.  Query execution time is only part of the process between the page request and the completion of the html rendering.  If you think it's too slow, find out why so you know what to fix.

[BreakawayPaul]

Thanks Dan.

Tomorrow I'll throw a CFdump on the main query and the QoQ to see what the execution time looks like, then go from there.  I think between the fact that it's Access, and the fact that it's a lot of data (something like 8k rows) the two are conspiring against me.

[BKBK]

BreakawayPaul wrote:

I'm trying to cache a query on our live web server, but not on our testing server (developers need to see changes realtime, but the public doesn't).

I tried something like this:

<cfif listfirst(servername,".") eq "test"><cfset cachetime = 5><cfelse><cfset cachetime = 0></cfif>

<cfquery name="GetContactInfo" datasource="MyDSN" cachedwithin=#createtimespan(0,0,cache,0)#">

SELECT....

But the results were unexpected (it didn't seem to work).

Is there a way to do this?

This code is good, except for typing errors! Simply supply the missing quotes and change cache to cachetime.

<cfif listfirst(servername,".") eq "test"><cfset cachetime = 5><cfelse><cfset cachetime = 0></cfif>

<cfquery name="GetContactInfo" datasource="MyDSN" cachedwithin="#createtimespan(0,0,cachetime,0)#">

SELECT....