I'd use them only where there is a proven necessity. I.e. where the performance gains (demonstrated by load testing) are worth the trouble, or the security concerns are such that there's no other way.
SPs move your business logic out of your application and into the database, which isn't necessarily a bad thing, but it does split the code, which can make maintenance harder. Careful design can mitigate most of that concern, of course, but it's still added complexity that shouldn't be brought on yourself for no good reason. Single queries, in particular, ought never to be converted to SPs unless it's a security concern, if you ask me. The performance isn't going to be any greater, since you still have to pass the recordset back to CF and let it create a CF query object. cheers, barneyb On 10/29/05, Pete Ruckelshaus <[EMAIL PROTECTED]> wrote: > I'm going back and tweaking a web app that I'm building and have > learned a few stored procedure tricks (my SP skills were pretty basic > before I started to play around). As a result, I'm replacing a number > of CF queries (using cfqueryparam) with SQL Server stored procedures. > Now, these are pretty much limited to the customer-facing (non-admin) > pages, though I'm using SP's in the admin where I already have them > written to return the rest sets that I need. > > Realizing that SP's will give greater performance, the question is > this: Is it OK to write your most frequently run queries as SP's and > leave the rest of the site as straight SQL queries? Or should I just > make the entire site SP's? > > What I've been doing during development is writing straight SQL until > I get what I need, then I rewrite as a SP. I've replaced a dozen or > so queries that are all on the customer-facing end of things, but > there are well over 100 queries still written as SQL on the admin > side, and it would take a bit of effort to convert (or merge into > existing SP's) those queries. > > Pete > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:222660 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
