Michael Dinowitz wrote: > Thank you for pointing that out. It looks like I missed that. So basically, > the order of cfprocparams being passed is all that matters and no parameter > can be missed when writing cfprocparam tags. > > Is there any performance (i.e. binding) savings to using the CFSTOREDPROC > tag vs. a CFQUERY with CFQUERYPARAMs? Is the CFSTOREDPROC call as efficient? > Better? What's the advantage? Anyone from MM want to comment? > Thanks >
I've seen some strange behavior with <cfstoredproc>, and our DBA has requested that we don't use it. If you trace your database (tested on sql2k), you will see cfstoredproc creates and compiles a procedure, calls that procedure a number of times (depending on how many recordsets you are expecting), then destroys the temp proc. For this reason, you will have degraded performance on <cfstoredproc> than you will calling a stored procedure from a <cfquery> block. We've seen the same behavior with client variables, so we don't use them. It's also my understanding that <cfquery> with <cfqueryparam> values can be as fast as stored procedures. The benefit, of course, comes when the SQL statements you are running are large enough to effect bandwidth between the web and database servers. You'll get some delay when you have to write 200 lines of SQL to your DB, vs 3 or 4 using a stored procedure. -nathan strutz http://www.dopefly.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188457 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
