True and not true. This is only the case when you are adding input params. With inline SQL you can only bring back 1 resultset at a time and I think this is major drawback. You are also limited T-SQL wise on what you have within it i.e. performing a select and then immediately afterward @@rowcount or perform an update and then use @@identity (or alike) - you have to do two blocks which gives you two result sets unnecessarily.
I personally think the use of SP;s make it easier to manage an application than one which utilises cfquery throughout. SP's are more efficient no question as if you were to perform say a data retrieve some data, update a datetime stamp, and then insert a new record all 3 tasks once a second as ad-hoc SQL this would result in 259,200/day independent database request vs. 86,400/day if all were encapsulated in a stored procedure. Using an SP is a more effective use of network bandwidth. SP's also have a cached execution plan where as inline SQL does not - so you will gain overall speed increase. Again it is preference if you like inline SQL go for ;-) -----Original Message----- From: Ray Thompson [mailto:[EMAIL PROTECTED] Sent: 15 August 2005 13:32 To: SQL Subject: RE: Using IN clause with a stored procedure One downside to stored procedures is that you have to visit two places to make any changes to the code. First place is the CFM template; second place is the DB (provided both require changes). To me that negates any real (or perceived) speed differences between SP and inline CF. With a SP SQL does not have to compile the query each time. But that time is very short and with the speed of today's boxes is even shorter. Remember that a lot of the conventional wisdom was developed on 200 MHZ machines. With dual 2.8 gig CPU's these time differences become trivial. I/O is the big time chunk. People time is expensive. Having all the code necessary to accomplish the task saves that time. So I think the SP/Inline argument is nothing more than personal preference and not a real performance issue. Ray Thompson Tau Beta Pi (www.tbp.org) The Engineering Honor Society 865-546-4578 -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, August 14, 2005 3:04 AM To: SQL Subject: Re: Using IN clause with a stored procedure Neil Robertson-Ravo wrote: > More secure, well they can be encrypted - the better level of security > resides with the fact they are held server side and not inline to the CF > page and can have a greater degree of control placed on them within the > SQL Server/Windows Security model. Sure they can be decrypted fairly > easily but are harder to get to than inline SQL. The bottom line is that a user can do what you give him permissions to do. If you don't want him to see 90% or change 99% of the data in the database, you should not give him permissions on those data. Putting a DML layer writen in stored procedures in front of that and giving permissions on that layer is avoiding the real issue. But I must admit that adding a layer of stored procedures is usually easier then defining row and column level permissions throughout the database. > They generally are faster (though granted, in some cases they may not be > - cursors springs to mind). In cass where using prepared queries means you are shuffling a huge amount of data between the client and the server, while you only need a little bit as the final result, stored procedures are definitely faster. But for all other cases, I don't really see the difference between stored procedures and prepared queries. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Protect your mail server with built in anti-virus protection. It's not only good for you, it's good for everybody. http://www.houseoffusion.com/banners/view.cfm?bannerid=39 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2362 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
