Webmaster Forum / ColdFusion / Advanced Techniques / September 2007

A SQL stored procedure is going to put the work on the database server
where database work should be.  The ultimate answer to your question is
'it depends'.  What type of logic is in the cfcs?  What kind of
processing is going on there?

If you are just doing basic select, update, insert queries making them
stored procedures is not going to provide much of a performance boost,
but you may get a separation of logic that is worth the effort to some
developers.

If you are dealing with more complex data logic where you are getting
multiple record sets with complex parameters and combining them into
sophisticated structures, a stored procedure may provide significant
boost in performance.  Since all the heavy lifting will be done in the
database with one request, rather then several requests to get all the
pieces of data and assembling them in the ColdFusion memory.

If your logic is mostly business logic that is not all about complex
data manipulation then the CFC is going to be a more logical place to do
this in my mind.  Databases are not any more efficient at basic loops
and branches then ColdFusion if data manipulation is not involved.

To completely answer your question is going to require a good
understanding of your requirements and a benefits versus costs analysis
of developing separate layers to maximize efficiency or a simpler single
layer solution that may be a few cpu cycles slower.

I have been using the cfstoreproc within cfccomponent since those tags
introduced in CF 6.0. All what I can say, that it make it much easier,faster
process, better organizing my application logic, and better security since I am
using SQL storeproc.  for sure it is faster to use storeproc for all the four
functions (insert, select, update and delete).  there are many research approve
that the spend using SP is faster and much efficient .

Good luck
Mamdoh

CFCs and stored procs are not mutually exclusive. Stored procs can be called
from within a CFC.

A stored procedure will always provide a performance boost over a query that
submits SQL to the database server. The reason for this is that the server must
parse and analyze the incoming SQL, produce an execution tree, optimize the
tree, and do a few other things, as well. With a stored procedure, these
operations are done once, when the procedure is compiled; with SQL that is
passed in a query , these expensive operations must be repeated each time the
query is used.

In addition, there are potentially some significant security benefits from
using stored procedures.