SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Query Caching

  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Query Caching

    Ok, I've been implementing query caching in some of my code, and I've started to wonder about when it's best used. Consider the following example:

    Code CFM:
    <cfquery name="getstaff" datasource="dsn" cachedwithin="#createtimespan(0,8,0,0)#">
    SELECT staffmember
    	, title
    	, phone_number
    	, email_address
    FROM contacts
    WHERE 1=1
    AND state = 'user_selected_value'
    AND office = 'another_user_selected_value'
    </cfquery>

    And let's say the user_selected_values are from <select> lists.

    On this particular page, each visitor will likely choose a different state/office combo, so that makes hundreds of different possibilities.

    This got me wondering, when that query is cached, is it caching the results of that exact query, or is it caching the entire table and then pulling the results from it?

    It seems like if just the resulting rows are cached, then it doesn't make much sense to cache that query, because in that 8 hr time frame, not many users will want those exact same results.

    If that's true, it seems like this might be a better solution:

    Code CFM:
    <cfquery name="getallstaff" datasource="dsn" cachedwithin="#createtimespan(0,8,0,0)#">
    SELECT staffmember
    	, title
    	, phone_number
    	, email_address
    FROM contacts
    </cfquery>

    And then...

    Code CFM:
    <cfquery name="getstaff" dbtype="query">
    SELECT staffmember
    	, title
    	, phone_number
    	, email_address
    FROM getallstaff
    WHERE 1=1
    AND state = 'user_selected_value'
    AND office = 'another_user_selected_value'
    </cfquery>
    </cfquery>

    Is my thinking correct, or am I over thinking this all?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Is my thinking correct
    spot on

    you query cache the entire table, and then query-of-query the cache for each user

    brilliant, innit

    and meanwhile, you hit the database only once a day

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    p.s. remember, you don't do anything administrative about the cache

    each cfm page that wants staff data has to include ("execute") both queries

    you don't have to have any logic that says CFIF cache-rows-exist...

    just include the first query, cold fusion will execute it basically by not doing anything, and then the query-of-query takes over

    i hope i made sense there...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy. See? I'm only halfway through your book and already its helping!

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I just had another though. With all these big (big for us, probably not big for most people) tables getting cached, how big of a hit is that going to be on the web server memory.

    Our biggest mdb file is currently around 60mb, but I somehow doubt that it'll actually use 60mb of ram. I'm wondering if I start caching a bunch of query tables (like maybe 500mb worth of mdb files), how much ram that'll use.

    I should probably only be caching queries on the pages that'll get the most traffic.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I should probably only be caching queries on the pages that'll get the most traffic.
    yes, and only data that is going to be accessed often

    i'll bet your contacts table has more than those four columns in it, so you SELECT and cache only those four (vertical partitioning of table data)

    in addition, your contacts table might (i'm making a stretch here to make a point) have retired people on it, and you'd filter them out with a WHERE clause (horizontal partitioning of table data)

    you have to be careful when deciding what to cache
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'll bet your contacts table has more than those four columns in it,

    in addition, your contacts table might (i'm making a stretch here to make a point) have retired people on it
    Correct on both points. When a person leaves, their name is replaced with "vacant" and is filtered out in that manner. The reason we do this is because sometimes someone will get reassigned (or promoted) and his/her replacement takes a while to be appointed. On the office directory, we'll have a spot for something like "Office Director" and that will show as vacant, rather than the previous person's name, and which can't be missing from the list.

    But I see what you're getting at. By filtering out all the data that can't possibly be a valid result, you reduce the amount that's cached.

    In the case of the contacts table, there's a lot more that can be excluded. There are some people who will never be a contact (like contractors, interns, and office assistants) and are marked as such in the db. Those people can be filtered out of the result set as well.

    Good info!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    tip for ease of maintenance: declare a view that defines the applicable columns and applicable rows

    then select from the view in the cached query -- and it would be okay to use the dreaded, evil "select star" in this case

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •