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
