5 Replies Latest reply on Feb 7, 2013 8:04 PM by BreakawayPaul

    <cfquery> caching

    BreakawayPaul Level 2

      I have a sidebar item on the front page of my website that pulls a "featured pic" out of my photo gallery and keeps it there for the day.  While it's not really important when that day starts and ends (people are of course, in all different time zones), I'd like to have the pic up for 24-hrs-ish.


      I started to write a complicated script to check the date and rotate the pic every day, but then I thought I might do it the "cheap" way, and just cache the query for 24 hrs.   The problem is, if I leave the page and return, I get a completely different pic.  I'm thinking that since my query is in the request scope, the caching is failing because CF sees the query name as being different.


      Here's my code:

      <cfquery name="FeaturedPic" datasource="#request.dsn#" cachedwithin="#createtimespan(0,24,0,0)#" maxrows="1">

      SELECT    photos.pic_id

              , photos.pic_file

              , albums.album_url

              , albums.album_name

              , parents.album_name AS parent

              , parents.album_url AS parenturl

      FROM photos

      INNER JOIN albums

              ON photos.pic_album = albums.album_id

      LEFT JOIN albums AS parents

              ON parents.album_id = albums.parent_album

      ORDER BY rand()


      Is my hypothesis about my caching correct, or is there something else I'm missing?

        • 1. Re: <cfquery> caching
          Dan Bracuk Level 5

          The code you posted shows the query to be in the variables scope, not the request scope.  Turn on de-bugging to see if you are running a cached copy or not.

          • 2. Re: <cfquery> caching
            BreakawayPaul Level 2

            I can't change any settings (shared hosting), but I can do it locally tonight on my home machine.  I'll see if I can reproduce the problem locally too.  Thanks!

            • 3. Re: <cfquery> caching
              BreakawayPaul Level 2

              Alright, here's what I get from the debugging output (locally):

              FeaturedPic (Datasource=cw_data, Time=0ms, Records=1, Cached Query) in /var/www/includes/cydebar.cfm @ 18:25:17.017


              So it looks like it is indeed cached.  And if I close out of my browser, wait ten minutes, then open that same page back up, I get the same pic (expected).  However, on my shared host, if I close out of my browser, wait ten minutes (or so) then open the page back up, different pic.  Hmmmm...

              • 4. Re: <cfquery> caching
                Dan Bracuk Level 5

                Caching is done at the server level.  Only so much RAM is allocated to the cache and if it gets full, the next entry kicks out the oldest one, or something like that.  This might be what is happening to you.

                1 person found this helpful
                • 5. Re: <cfquery> caching
                  BreakawayPaul Level 2

                  Ah, I hadn't even considered that.  With a shared host, it's entirely possible.


                  I'll just live with it the way it is.  My primary goal is not to have the featured pic change with each page load, and it accomplishes that fine. 


                  Thanks for the input!