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 -----Original Message----- From: Greg Luce [mailto:[EMAIL PROTECTED] Sent: 31 October 2005 02:03 To: CF-Talk Subject: Re: Philosophy Q: SP's or CFQUERY? And what about the security factor? I've always been under the assumption that if your CF only had access to run SPs you were safer from SQL injection. I'd like to hear Neil Robertson-Ravo chime in on this come 8:00 am London time. I didn't know there were so many proponents for inline SQL. I've just always figured MSSQL was better able to run some pre-compiled execution plan in the case of an SP, over ad-hoc SQL queries. Neil? Dave Watts? Greg On 10/30/05, Justin D. Scott <[EMAIL PROTECTED]> wrote: > > > What do you mean "in memory"? > > SQL Server will hold as much of the most accessed data pages as it can in > memory once it's loaded from previous queries. This table gets hit like > crazy, so my guess is that the entire thing is loaded in memory after a > couple of hours. > > > And was your DB running on the same hardware as CF? > > The DB has to do the same work in either case (some > > randomization of a million rows), so the overhead > > of the SP/query should be completely lost in the > > mass of time it'd take to deal with the data. > > Perhaps "ramdom" wasn't the right word. The query isn't pulling "random" > rows, but subsets based on a foreign key. The foreign key that is to be > loaded is not predictable, and there are 10,000+ foreign keys that can be > pulled at any time. > > The problem with having the query come directly from ColdFusion was that > the > query was different for each foreign key, which required the SQL server to > compile a new query plan (which could be thousands of plans that would get > cycled out of memory as new ones were compiled), which took far longer > than > using one plan (from the SP) that was cached. > > Using CFQUERYPARAM on that variable may have helped, but I never tested it > that way. I just went directly from dynamic query to stored procedure and > the CF page response time went down significantly because it no longer had > to wait so long for the SQL server to process the queries. > > Even if I have some of the details wrong, that one change made things go a > WHOLE lot smoother. > > > -Justin Scott > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:222705 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
