Yep, I think like with queryparam, you have to look outside of the performance benefits when working with stored procedures. The other benefits come when you have very complex queries that are accessed from several locations within an application, or multiple applications, this means not having to duplicate the query SQL code, it also helps with maintenance of the query down the line if needs be.
There are probably performance benefits, but I'd imagine that if performance is a REAL concern to you, there are plenty of other places within your application where your time would be better spent optimizing, you'll likely get better performance benefits by considering things like caching of queries and objects and so on. Like the other guys have picked up on, the biggest performance on queries will come from good SQL code, you're best off spending some time in your database studio checking query performance times and looking at execution plans, playing with the SQL and table Indexing. Rob -----Original Message----- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: 25 October 2007 09:50 To: CF-Talk Subject: Re: cfqueryparam and dynamically-created SQL I whole-heartedly agree and when I get some time I will do some testing. What I imagine to be the case is that the more complex the SQL required, the more likely it is that a stored procedure is beneficial but perhaps this is wrong (it is certainly blind assumption). Regardless of that, I like to contain all my data logic inside the database itself - feels clear in my head that way. Anyways, this is drifting off topic - just wanted to give an alternative to the OPs solution to his problem ;) Dom On 24/10/2007, Mark A Kruger <[EMAIL PROTECTED]> wrote: > Dom, > > To know this you have to test. SPs are marginally faster in "most" cases - > and just like cfqueries they have to be well written. There is not enough > of a boost in performance (when comparing SPs to well written queries > using > cfqueryparam to bind the data) to make a hard and fast rule that SPs are > "best practice" in all cases - that's my .02. > > Having said that, in a team enviornment there is some division of labor > benefits. > > -Mark > > > > -- > Blog it up: http://fusion.dominicwatson.co.uk ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292046 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
