I totally agree. I like to put code in stored procs for organisational reasons more than performance ones in most cases.
However, I was recently updating a site that used dynamic SQL to generate some reports and the performance of this was becoming a huge issue. The logic was so complicated that approximately 1MB of SQL code was being sent to the SQL server (cfqueries within cfloops of other queries) - eek! In this instance I chose to rewrite the SQL from scratch (bad code was the biggest flaw) and also put each report into a stored procedure. The performance was an issue and the boost was huge. Dominic On 25/10/2007, Robert Rawlins - Think Blue < [EMAIL PROTECTED]> wrote: > > 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 > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292049 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
