Cached queries will execute faster than stored procedures in many cases. The problem of queries inside of ColdFusion deals directly with looping over the data of performing a Query of a Query against the data set.
I tend to stay away from Query of a Query as the performance is not to my liking. The real problem is when you have data from two different data sets from two database servers that are not linked. Unless you perform an extract of the data and move it regularly, you will have an intermediary step of having ColdFusion combined data sets. Cached queries also introduce limitations. You set in the administrator how many cached queries to store in RAM. Database servers do not give you this limitation. In the SQL Server land, the execution plan of a stored procedure is calculated once and stored there. This does not affect your CF server and also gives you flexibility. You do have to query the database, but recalculation similar to the first cached query creation is the cost. If you are on a shared host, this could make your life easier. Now there is the business and model approach. I tend to use CF to handle framework dynamics and display logic. if I cannot abstract business logic into a CFC domain, I will keep it in the stored procedure. I tend to choose one or the other. I would consider this a good practice that I would recommend, but again it is my recommendation and you can take it for a grain of salt. Business logic really should be centralized so you can change configurations, rules and flow easily. As for performance gain, I typically see about a 25-30% performance boost when using cached queries. Now this sounds great, but the data set was 40K records and returned by a cached query and a stored procedure. They both executed in less than 100ms. In more business to business setting, this is negligible for the most part. Facing pages on high performing sites should tweak as much as they can. My other case for stored procedures is the increased functionality and control over my data. TSQL in the SQL Server realm gives me additional logic and commands to get low level information. ETL based tools like DTS and SSIS being invoked from my stored procedures creates automation avenues. Built in mail support can alleviate taxing my CF server on data notifications. On 3/23/07, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > > 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] > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273531 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
