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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase Contribute 3 from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=53 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2361 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
