database optimisation - stored procedures versus cachedwithin

[Dax_Trajero]

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 ?

[Owainnorth · Accepted solution]

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.

[Dax_Trajero]

Owain, that was a brilliant answer

Thanks so much.

[Owainnorth]

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

[Adam Cameron.]

As Owain suggested: in comparing a CFQUERY with a cachedwithin param to a stored proc, you're comparing oranges to orangutans here a bit.  The sensible comparison here would simply be between CFQUERY and CFSTOREDPROC.  Also bear in mind that since CF9 (?) CFSTOREDPROC takes the cachedwithin parameter too.

I also don't see the chief comparison here as being when to use CFQUERY (getting stuff) or CFSTOREDPROC (doing stuff), although that's a nice way to think about it.

I see it as a separation of roles.  Your CF code (or app) should not be busying itself with the ins and outs of the DB, it should be busying itself with creating mark-up to render a web-page: outputting, looping, branching according to data values, etc.  The data manipulation should be done by the DB.  In that case, I really think CFQUERY is a bit of a fish out of water, from a bygone era when a CF template had all its business logic, data processing and output all muddled up in the same file.  Bleah.  The SQL within the CFQUERY tag should not be plopped there in the middle of your web application code.  It doesn't belong there.  It belongs in the DB.  Even if the DB call is a simple SELECT cols FROM table WHERE filters ORDER BY expression, I'd still put it in a proc, so the CF app then just calls the getMyThing proc, passing in the ID or whatever the proc needs to do its work.

Also bear in mind that a CFQUERY can only return one recordset (and only a recordset).  A CFSTOREDPROC call can return multiple recordsets, as well as simple values into specific return parameters.

--

Adam

[Dax_Trajero]

So for example, when I add a new category to the left hand menu (see above), I add a storedprocedure to the database

In fact based on your comments that a storedprocedure can return multiple recordsets, I can use a single storedprocedure to return all the recordsets in that left hand menu, especially since your comment about the introduction of cachedwithin to the cfstoredproc in ColdFusion9

That way all the database logic is separate from the webpage code

The webpage simply makes a call to the storedprocedure to return it's results.

[Adam Cameron.]

So for example, when I add a new category to the left hand menu (see above), I add a storedprocedure to the database

No.  Absolutely not.  If you need to alter your SQL (be it in a proc or a vanilla CFQUERY) every time you add a category, you need to redesign your DB.  To get information like this:

fighting (95)

party (46)

puzzle (32)

driving (78)

sports (55)

platform (120)

role playing (33)

You should be calling one SQL statement along the lines of:

SELECT count(id) AS cat_count, cat_name

FROM tbl_category

GROUP BY cat_name

--

Adam

[Dan_Bracuk]

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.

[Dax_Trajero]

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]

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.

[Dax_Trajero]

thanks Owain

Great idea with that switch/flag in the cachedwithin function.

[Owainnorth]

Well you need to be extremely careful - in order for CF to successfully clear the cache, you have to give it *exactly* the same query - same whitespace, same tabs, same everything. Simply replicating the query elsewhere with a negative cache value will not do it.

Therefore only place it makes sense is in the function you're calling it from originally.

[Dan_Bracuk]

Regarding:

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

That's not necessarily relevent.  What is relevent is whether or not the next person to run the query after the data changes will get the latest data or the cache.  Another consideration is the consequences of getting the cache.