-------- Original Message -------- Subject: cfqueryparam vs cfstoredproc? From: "Marie Taylore" <[EMAIL PROTECTED]>
> I realize with stored procedures you have a lot more power in terms of SQL > scripting, This is not really true. You can put anything you want in a cfquery block. temp tables, CTE's, sp_commandshell, you name it. Procedures to not implicitly allow for any additional functionality other than the possibility of being called easily from other parts of your database. > but for basic queries, is CFQUERYPARAM just as fast as (or faster > than) running CFSTOREDPROC? There are no significant performance differences between running the same piece of sql as a paramaterized cfquery, or as a stored procedure. The biggest difference, is the amount of text that gets sent over the wire to the SQL server. "select * from ..." vs "execute sp_etc" > Stored Procedures - can contain advanced SQL & procedural code. Like above, your stored proc can't do anything your inline query can't > Encapsulate code outside of you application for a layer of abstraction. Now, you're talking. This is, in my opinion, one of the most useful features of stored procs. This is most readily apparent if your app does not use some form of data abstraction layer like DAOs. Additionally, if you have business logic in your SQL, (which is common though I recommend against it) placing that logic in a proc would make it possible for another process (Java, .NET, etc.) to reuse it at the database level. I have worked on applications where the basic API was comprised of hundreds of stored procedures full of business logic. > CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries, > providing speedier and more secured code. You can "encapsulate" much the > same way a stored procedure does with CFCs. Speedier than an ad-hoc query perhaps. Watch out for blanket statements about paramaterized performance though: http://www.codersrevolution.com/index.cfm/2008/7/26/cfqueryparam-its-not-just-for-security-also-when-NOT-to-use-it Wrapping data access in a CFC is my preferred method of abstracting and reusing SQL in my application, however that is really a CFC vs Proc thing and not a cfqueryparam vs proc conversation. > Would love to hear from others on the advantages/disadvantages of each I like stored procs for reusability and organization (SQL code is easier to read and edit in a SQL IDE as opposed to a CF IDE). I however, do not use them for security nor performance. ~Brad ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314612 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
