cfquery vs cfstoredproc

[JRock]

In my experience, the SPs execute a little faster then the queries. you can use
to test on your own. <CFSET start=GetTickCount()> ... some CFML code ...
<CFSET end=GetTickCount()> <CFSET executiontime=end-start>

[anclarke]

My question was more along these lines: let's say I have a stored procedure
called usp_updateMyTable. I can call that in one of two ways: <cfquery ...>
call usp_updateMyTable ('option1', 'option2') </cfquery> or <cfstoredproc
procedure='usp_updateMyTable' ...> <cfprocparam value='option1'
dbVarName='row1' cfsqltype='cf_sql_varchar' null='no' /> <cfprocparam
value='option2' dbVarName='row2' cfsqltype='cf_sql_varchar' null='no' />
</cfstoredproc> If you make an error in the cfstoredproc and look at the error
information, you'll see that it's actually internally creating a call
statement. I've always thought that cfstoredproc would be faster than cfquery
in this sort of case (when you're calling a stored procedure), but after
looking at the error logs, I can't see any reason why it would be faster. In
fact, it theoretically could be slower, as it has to translate it into a 'call
statement' first. I've also never noticed an ad hoc cfquery select statement
to be slower than its stored procedure counterpart, but I suppose I could do
some more benchmarking on that one. - Andrew.