Indeed, I used to be in the camp of SPs were faster in ColdFusion but in most cases they are not. What we do now is use them where we actually need them - multiple recordsets for example.
Also one of the major benefits is that they can be shared by our ColdFusion and .NET developers etc.. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Ryan, Terrence To: CF-Talk Sent: Fri Mar 23 02:13:36 2007 Subject: RE: CFQUERY vs SQL Server Stored Procedures There are two reasons my organization encourages/forces all SQL to be in stored procedures. And they don't directly touch on performance. 1. A belief that keeping SQL code in the database is of itself a good thing. By keeping the database interaction in the database you make it easier for DBA's to do their job. Especially when a query goes awry, and your SQL server starts bombing. It's in my humble opinion easier to troubleshoot SQL problems on the SQL server when the actual SQL is present. Especially during a crisis. Now, if you're a one person development team, or all of your CF developers are also doing their own DBA work it might not make a whole lot of sense for this argument. However, having all of the SQL there allows you to more easily use SQL tuning tools, which can improve indexing, which can make either inline queries or stored procedures run faster. 2. It forces developers to concentrate on the database first, as that seems to be the hardest thing to change once an application gets past the planning stage. Granted this is just enforcing good programming practices (taking a good long look at the database in this case) through policy. But considering that the database side of things was causing the majority of our server problems, it was the right call. So... Someone who tells you that all stored procedures are faster than inline queries is wrong. Someone who tells you that all stored procedures are "better", is probably wrong. Someone who tells you that stored procedures are better for their organization, group or specific problem might be right. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology      E-mail:        [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273486 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
