CFC vs SQL Stored Proceedure

[BigRedFan]

I'm not real familiar with SQL stored proceedures so I'm wondering if they have any speed or resource benefits over CFCs? Do they help take a load off of CF and are they any faster or just another way to query? I have a number of queries that get used over and over that are currently in CFCs. I'm trying to determine if it makes sense to convert them to stored proceedures.

Any thought?

Thanks!

[Newsgroup_User]

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.

[Guest]

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

[MarkAltenbernd]

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.