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 -----Original Message----- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 5:06 PM To: CF-Talk Subject: Re: cfqueryparam and dynamically-created SQL Why I believe it to be true (though quite open to it not being the case): 1. I believe stored procs are generally faster than sending the same query to SQL Server due to query optimisation(?) 2. Less data is being sent to the SQL server 3. Less CF application process before the code is run on the SQL server 4. WHERE @foo = 0 is a very simple instruction to process and I assume the server does a good job of optimising it ( I realise to assume makes an ass out of u and me) Would you think otherwise? Dom On 24/10/2007, Dave Watts <[EMAIL PROTECTED]> wrote: > > I quite often do something similar with stored procs but usually > > with numerical values, i.e. Default them to 0 in the proc and do > > 'where (@foo = 0 OR myCol = @foo)'. I'm quite sure that is faster > > than building a dynamic query and sending it to the server. > > Why are you quite sure? Have you done any load testing? > > Very often, things don't work out the way we might expect them to. > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ > > Fig Leaf Software provides the highest caliber vendor-authorized > instruction at our training centers in Washington DC, Atlanta, > Chicago, Baltimore, Northern Virginia, or on-site at your location. > Visit http://training.figleaf.com/ for more information! > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292024 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
