Professional opinions please - cfquery vs. Stored Procedures

[wmkolcz]

I am rebuilding a Flex application from the ground up to make it better and more featured. One of the main complaints I get is how slow some of the data takes to get back to the view from the ColdFusion CFC (RemoteObject). I spent a lot of time optimizing the queries to make them lighter and make sure not to grab information if it is not going to be used.

One thing I am playing with is the idea of turning all the simple queries into Stored Procedures on my MySQL database. Now, I KNOW that it is faster for a database to process its own language before translating the query from ColdFusion into it's language, execute it, and return the results. On my local development laptop I don't see any real performance improvements since everything is lightning fast on a local laptop set up as a web server..lol.. but obviously I am looking more for the actual web.

How many people use SP vs cfqueries in their components, Flex/Flash app or HTML? Is there a good amount of performace improvement using cfstoredproc? Is it worth taking the more simple queries out of the component's via cfquery and just put them into stored procs?

I want my Flex app to return data faster and I am looking at all ways to gain performance.

[wmkolcz]

Also, while smart people are chiming in...What about query caching? Can it be done with stored procs or is it just in cfquery? Is one better? When does the cache update? Only after expiring or if when there is a change (CRUD)? Been reading on it but not sure if any performance I might get in stor proc for some of my larger lists that don't change very often might be better served cached. Can both be done?

[Dan_Bracuk]

I favour stored procedures because I do notice a performance gain.  What I specifically notice is that a complex query often takes several seconds to compile.  When you run it the first time it takes around 40 seconds and when you run it immediately afterwards it takes around half a second.  This is the same with both queries and stored procs.  The difference is that if the database runs enough other queries before you try to run yours again, yours will have to be compile again.  With a stored procedure it stays compiled.

Regarding query caching, cold fusion caches the result set for either the time you specified, or until it gets pushed out because the cache is full.  That means changes to the data in the db will not affect your cached results.

I've never checked to see if cfstoredproc has a cache attribute, but you can.  However, I know that cfquery has a cache attribute and that you can run a stored proc from a cfquery tag.  You simply do this:

<cfquery>

execute YourProc @param1 = something, param2 = whatever

[wmkolcz]

What is the performace difference between calling a stored proc via cfstoredproc or cfquery directly? (<cfquery>execute YourProc @param1 = something, param2 = whatever)

[Dan_Bracuk]

Regarding "What is the performace difference between calling a stored proc via cfstoredproc or cfquery directly? (<cfquery>execute YourProc @param1 = something, param2 = whatever)"

My guess, nothing.  If you want to see, write a stored proc and then call it each way inside a loop of say 1000 iterations.  Time each method and see.

[Adam Cameron.]

Dan Bracuk wrote:

Regarding "What is the performace difference between calling a stored proc via cfstoredproc or cfquery directly? (<cfquery>execute YourProc @param1 = something, param2 = whatever)"

My guess, nothing.  If you want to see, write a stored proc and then call it each way inside a loop of say 1000 iterations.  Time each method and see.

As well as vertical load, one should test horizontal load too: use JMeter or something to load test it with simultaneous requests.

I suspect the difference would be inconsequential.

--

Adam

[wmkolcz]

Thank guys! All this is great information and is helping me and my application. Trying to make the next generation of my application as solid as possible!

[BKBK]

wmkolcz wrote:

I am rebuilding a Flex application from the ground up to make it better and more featured. One of the main complaints I get is how slow some of the data takes to get back to the view from the ColdFusion CFC (RemoteObject). I spent a lot of time optimizing the queries to make them lighter and make sure not to grab information if it is not going to be used.

One thing I am playing with is the idea of turning all the simple queries into Stored Procedures on my MySQL database. Now, I KNOW that it is faster for a database to process its own language before translating the query from ColdFusion into it's language, execute it, and return the results. On my local development laptop I don't see any real performance improvements since everything is lightning fast on a local laptop set up as a web server..lol.. but obviously I am looking more for the actual web.

How many people use SP vs cfqueries in their components, Flex/Flash app or HTML? Is there a good amount of performace improvement using cfstoredproc? Is it worth taking the more simple queries out of the component's via cfquery and just put them into stored procs?

A matter of faith and a matter of fact: stored procedures are faster than cfqueries.

I want my Flex app to return data faster and I am looking at all ways to gain performance.

The one thing that usually makes the difference in Flash remoting is: query caching.

[GDI1982]

My experience tells me about next cfstoredproc pros:

1. it can works with few resultsets

That's all.. and keep in mind that in CF8-CF9 (not sure about CF10) you'll be have an issue with cfstoredproc if you try work with more than one resultset and use cfstoredproc caching attribute (it doesn't matter will it be cachedwithin or cacheafter).

About cfquery.. it still allow you call stored procedures on your DB server, e.g. <cfquery>EXEC sp_Foo</cfquery>. Also it allows you set SP params in any order, you cannot do that with cfstoredproc. Some people claim that cfquery syntax less readable in that way but you can use it withing cfscript block and that will allow you put query params as named params (http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSe9cbe5cf462523a0693d5dae123bcd28f6d-7ffb.html ...).