Skip to main content
D
Dax_Trajero
Inspiring
July 28, 2011
Answered

database optimisation - stored procedures versus cachedwithin

  • July 28, 2011
  • 2 replies
  • 2034 views

I'm trying to get my head around when to use storeprocedures vs cfquery cachedwithin

Am I right in saying a storedprocedure is always preferrable to a cfquery

And that a cfquery cachedwith in preferrable to a storeprocedure when your dealing with data that you know only changes say once a day ?

take the scenario of an eCommerce site selling videogames, where the left hand menu would contain the categories of video games

fighting (95)

party (46)

puzzle (32)

driving (78)

sports (55)

platform (120)

role playing (33)

these queries could be stored procedures

or

each could be a cfquery cachedwithin, because we know the games only get updated once a day ?

Which should I be using ? Should I be conservative in my use of cachedwithin as there's a RAM consideration on the server ?

    This topic has been closed for replies.
    Correct answer Owainnorth

    The two are *not* the same thing, they are both used to achieve different things.

    Stored procedures are precompiled code in the database, and generally are for "doing stuff" rather than "returning stuff". They sometimes return an ID or similar, but aren't really used for returning recordsets. Say you had a shopping cart system, and when someone paid you had to run around, create a row in one table, update others, insert into logging tables - that would be an ideal job for a stored procedure. It would also run much faster than you doing several cfqueries yourself.

    Cfquery cachedwithin just stores in RAM the result of a query. As per your example of a menu yes that's an ideal opportunity to use it - the data rarely changes and even if it does, it's not important that the changes are reflected immediately.

    I really wouldn't worry about RAM on servers any more, small querysets use barely any memory and the benefits massively outweigh the overhead of having to re-query the database on every page request, which would still happen even if you were using a stored procedure.

    So simply - stored procedures are normally for doing stuff to a database. Cfquery is for getting stuff *from* a database. Cachedwithin massively reduces the number of database calls made, thus significantly improving performance of your site.

    Am I right in saying a storedprocedure is always preferrable to a cfquery

    Only if you're doing multiple inserts, updates and deletes. Bear in mind when you run a query (from a standard cfquery tag) the database engine compiles it up and will use it again *anyway* next time you run the same query, as long as you're using cfqueryparams. There is therefore no benefit. Personally, I have never used a stored procedure to return a queryset, only ever one or two variables if necessary.

    HTH.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    July 28, 2011

    There are times when cachedwithin is great, and times when it is not so great.  There have been more than one occasion when I have had to take away the cache in order to display the most recent data.  I think the things you have to consider are:

    how long does the query take without caching?

    how often does it run?

    how often does the data change?

    does the data change while people are running queries?

    I would not worry about RAM on the server.  CF sets aside a certain amount for caching and when it gets full, it starts pushing queries out the back door to make room for new ones.

    D
    Dax_TrajeroAuthor
    Inspiring
    July 28, 2011

    so when a shopper clicks a particular category, say "fighting" and all the results are displayed

    - we know the data hasn't changed since the last once-a-day-update

    - we know this query is requested frequently

    - we know the data doesn't change whilst running the query

    OPTION1

    we could use a cfstoredproc (cachedwithin) for each of the categories, hard-wired if you like, for increased performance. We could create it programmatically every time the admin creates a new category in the CMS.

    OPTION2

    or if creating a new stored proc for every category is ridiculous...

    we would we use a cfstoredproc and pass the category as a paramter ?

    SELECT *

    FROM merchandise

    WHERE cat="fighting"

    I guess passing a parameter would negate the use of cachedwith

    OPTION3

    We could use a query of a cachedwithin query

    Owainnorth
    Owainnorth
    Inspiring
    July 28, 2011

    If this were the situation:

    - we know the data hasn't changed since the last once-a-day-update

    - we know this query is requested frequently

    - we know the data doesn't change whilst running the query

    I would use a cfquery with a cachedwithin, no doubt about it. Bear in mind you can clear a cachedquery by setting the timeout to a negative value, iirc. Personally, I'd write that into the function. E.g.

    <cffunction name="getByCategory" returntype="query">

      <cfargument name="Category" type="string" />

      <cfargument name="Refresh" type="boolean" default="true" />

      <cfset var qSelect = "" />

      <cfquery name="qSelect" ... cachedwithin="#iif(arguments.Refresh, de(createTimeSpan(0,0,-1)), de(createTimeSpan(6,0,0)))#">

         SELECT [cols]

         FROM

         WHERE category = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.Category#" />

      </cfquery>

      <cfreturn qSelect />

    </cffunction>

    That way, on your page where you do your updates, you just run that function again but with the refresh=true argument, which will clear the cache only after you've made changes.

    Unless I'm being a foo for some reason.

    With regards to your options:

    1 - No chance.

    2 - This is what you would do if you were using procs, yes. Passing parameters to a stored proc will be no slower than having lots of procs.

    3 - As above.

    Owainnorth
    OwainnorthCorrect answer
    Inspiring
    July 28, 2011

    The two are *not* the same thing, they are both used to achieve different things.

    Stored procedures are precompiled code in the database, and generally are for "doing stuff" rather than "returning stuff". They sometimes return an ID or similar, but aren't really used for returning recordsets. Say you had a shopping cart system, and when someone paid you had to run around, create a row in one table, update others, insert into logging tables - that would be an ideal job for a stored procedure. It would also run much faster than you doing several cfqueries yourself.

    Cfquery cachedwithin just stores in RAM the result of a query. As per your example of a menu yes that's an ideal opportunity to use it - the data rarely changes and even if it does, it's not important that the changes are reflected immediately.

    I really wouldn't worry about RAM on servers any more, small querysets use barely any memory and the benefits massively outweigh the overhead of having to re-query the database on every page request, which would still happen even if you were using a stored procedure.

    So simply - stored procedures are normally for doing stuff to a database. Cfquery is for getting stuff *from* a database. Cachedwithin massively reduces the number of database calls made, thus significantly improving performance of your site.

    Am I right in saying a storedprocedure is always preferrable to a cfquery

    Only if you're doing multiple inserts, updates and deletes. Bear in mind when you run a query (from a standard cfquery tag) the database engine compiles it up and will use it again *anyway* next time you run the same query, as long as you're using cfqueryparams. There is therefore no benefit. Personally, I have never used a stored procedure to return a queryset, only ever one or two variables if necessary.

    HTH.

    D
    Dax_TrajeroAuthor
    Inspiring
    July 28, 2011

    Owain, that was a brilliant answer

    Thanks so much.

    Owainnorth
    Owainnorth
    Inspiring
    July 28, 2011

    Anything to delay the list of jobs I'm meant to be doing

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices