Am I that predictable with SP's ;-) They are a strange beast.... Stored Procedures are in general more efficient than inline SQL on a variety of frontiers. Firstly are generally more secure than inline SQL as of course you can adhere granular levels of security to them - you cannot do this with inline SQL (unless you mount different DSN's). They also provide a greater control of abstraction from the underlying DB table which helps security - and does not require you to error trap and data preserve by using CFTRY/CFCATCH/CFLOCK every DB call but on the downside of that is you lose the portability which CF gives you.
They are in most cases faster to process as their execution plans are 'cached' which are optimized - you do not get this kind of control with inline. They are also far easier to modify and document - which in a code world is faster. The problem with CF and inline SQL is that the DataDirect driver is actually pretty good at inline SQL parsing in as far as speed is concerned and terrible at SP execution and return ;-) until that changes The benefits could be only slightly marginal (if at all) in as far as speed is concerned - only in some cases will you find a huge performance boost in a SP; and that is likely to be with very large complex operations which would require multiple CF code-blocks to perform (and you cannot rely on QoQ in CF - well not in 6.1 you cannot). SP's simply allow you to perform decent and complex operations in the database - for some SP's are out reach as either they cannot use them based on the RDBMS or they just cant code them - and when they do they are not very efficient. The double edged sword for CF allows uses to get up and running with DB calls quickly and easily, but this has a downside in that a lot of SQL code which is produced is poor, very poor as hardly anyone would need to understand the beef of what goes on inside the SQL Engine (as in most cases, why would the...) or understand just exactly what T-SQL can do. I use SP's as its out standard and we prefer the abstraction as it allows out .NET apps etc to use them - we ery rarely use inline (we use QoQ on occasion). The case here is, if you can use SP's, then I would use them simply for the re-usability and added security, but obviously you can get just as much security from inline if coded correctly - performance at present is for the most part a redundant subject, but in some cases an SP will certainly be faster than inline if only the drivers could handle it- running a SQL Profiler will give you that info. N This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222706 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
