I would also echo Dawn's feedback. I have been on both sides of the fence with different companies regarding SQL's overall role in app dev. Where I am at now, we're very SQL-heavy, and employ a lot of views & procs vs a separate DAO layer within CFCs or query heavy templates. I like this method personally. It utilizes SQL well and it also provides direct access for those who need to work on the reporting or data analysis side -- vs, those folks having to dig through CF templates to figure out what's going on from a data perspective.
Also on the subject of SQL in general, I wanted to mention Redgate's SQL Developer Bundle for managing deployments within MS SQL. These tools have been a godsend for us for managing SQL in source control, pushing new db object changes to production, etc. While they are pricey, I highly recommend these tools. http://www.red-gate.com/products/sql-development/sql-developer-bundle/ -Steve Duys On Wed, May 22, 2013 at 11:02 AM, Dawn Hoagland <dawnhoagl...@gmail.com>wrote: > My 2 cents..... > > I've seen more poorly put together Hibernate implementations than I can > count. If you don't REALLY know the data, how it's put together, how it's > used and keep the objects updated, Hibernate will automatically generate > the worst path imaginable to get the data you need. I've also seen more > developers who couldn't write an efficient SQL Query if their life depended > on it..... > > Some implementations may REQUIRE stored procedures for efficiency. > > Study ALL of the tools available including their strengths and weaknesses > and choose the proper tool for the solution. I've written a lot of all 3. > If I could ONLY choose one method (and there have been instances of that) > - stored procs are the most versatile. Just like anything else, the source > should be in source control. It's no different than putting a process in > place for any other code. You just do it. > > --Dawn > > > On Wed, May 22, 2013 at 9:58 AM, MCG <mger...@gmail.com> wrote: > >> Let me add, no DBA here at all. So same guys updating stored proc and >> code. So we are making the assumption that they have the same skills to >> update things (which may or may not be true); the code from what I >> understand is MVC-ish. Didn't realize that stored procs were cached as >> well. It sounds like it boils down to 'it depends'. I'm partial to views, >> namely because my back end, deep down skills are weaker. And access is >> just easier. You only have to write a funky query once. Do not fear the >> outer join! :) >> >> >> mcg >> >> >> On Tue, May 21, 2013 at 6:23 PM, Bill Brock <bbr...@tech-projects.com>wrote: >> >>> Mary-Catherine,**** >>> >>> ** ** >>> >>> I’m with Jason, for the most part, and would only add these notes for >>> whatever it is worth:**** >>> >>> ** ** >>> >>> **· **Stored procedures do cache their execution plans, and >>> that can lead to greater efficiency.**** >>> >>> **· **Stored procedures are a nice way of separating church and >>> state for the DBA’s**** >>> >>> **· **In-line SQL is part of the beauty of ColdFusion, and is >>> optimal for testing and is also fine, in my opinion, for production as long >>> as parameters are consistently wrapped in queryparams. For everyone >>> except the DBAs, in line is probably preferable. It is certainly >>> easier to edit / maintain, as well as to read (ie, you can figure out what >>> your query is doing without having to go open another file)**** >>> >>> **· **Nothing beats the MVC framework in the long run for >>> consistent application structure and for the incredible flexibility (think >>> model callbacks) offered via ORM.**** >>> >>> ** ** >>> >>> Best wishes,**** >>> >>> >>> Bill**** >>> >>> ** ** >>> >>> ===================================**** >>> >>> *Bill Brock* >>> >>> *Technology Projects***** >>> >>> 1014 Dallas Road, Suite 102**** >>> >>> Chattanooga, TN 37405**** >>> >>> p 423.267.7375**** >>> >>> ** ** >>> >>> *From:* ad...@acfug.org [mailto:ad...@acfug.org] *On Behalf Of *Jason >>> Delmore >>> *Sent:* Tuesday, May 21, 2013 11:15 AM >>> *To:* discussion@acfug.org >>> *Subject:* Re: [ACFUG Discuss] SQL Server Question**** >>> >>> ** ** >>> >>> I don't think there is anything wrong with using stored procs over >>> inline SQL to retrieve data. However, in my view of the world this is >>> application behavior and not reporting. Application behavior should be >>> modeled in objects and the best way is to use persistent components >>> (Hibernate/ORM.)**** >>> >>> ** ** >>> >>> The code would then look like**** >>> >>> claim = entityload("claim", {claimid=1234});**** >>> >>> documents = claim.getDocuments(); // this uses ORM to generate SQL >>> that really just does select * from documents where claimid=1234**** >>> >>> document = documents[1];**** >>> >>> formfields = document.getFormFields();**** >>> >>> ** ** >>> >>> IMHO, there is no value of inline SQL over stored procs other than >>> perhaps that people seem to do a poor job of version control on stored >>> procs. An additional benefit of a stored proc is that database guys will >>> go in and review and tune a stored proc, but are unlikely to go and edit >>> inline SQL as that falls in the application space. I would say leave your >>> stored proc alone unless you are going to go all the way to ORM.**** >>> >>> ** ** >>> >>> But... that is just my view of the world.**** >>> >>> ** ** >>> >>> Jason**** >>> >>> ** ** >>> >>> ** ** >>> >>> On Tue, May 21, 2013 at 9:43 AM, MCG <mger...@gmail.com> wrote:**** >>> >>> Yes it's been a while, but I've gotten pulled into database design, and >>> this group has always been the best place to turn to for technical >>> knowledge. >>> >>> The team here inherited a crap database, over 200+ tables for an >>> unexciting claim system, and is now adding new features. Yes, views are >>> better than straight SQL in the code in the case of many tables for your >>> query. Maybe I've just been out of the game, but the team is using stored >>> procedures to build queries to output data on a page, which seems odd to >>> me. Maybe it is fear of the outer join, unions, subqueries. **** >>> >>> But do I have a case for pushing the funky view SQL versus running >>> stored procedures? The case here is nothing exceptional, we are getting >>> data for the list of all documents related to a claim and then the one >>> record for form fields for a single document for a claim.**** >>> >>> Thanks! >>> >>> Mary-Catherine**** >>> >>> ** ** >>> >>> ------------------------------------------------------------- >>> To unsubscribe from this list, manage your profile @ >>> http://www.acfug.org?fa=login.edituserform >>> >>> For more info, see http://www.acfug.org/mailinglists >>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >>> List hosted by FusionLink <http://www.fusionlink.com> >>> ------------------------------------------------------------- >> >> >> > > > -- > Dawn >
