I posted this question over at SQL Server Central, just like my last post, I’m also posting it here. I need some help. I’ve been trying to research this and I can’t find good, hard facts. Any help would be deeply appreciated.
It looks like we might be facing a large project shifting over to using ORM methods through nHibernate. I’m trying to get a read from the database community on what exactly I should expect in terms of issues, challenges and headaches during the development process. I’m also interested in any long term maintenance issues, troubleshooting problems, etc. If your developers implemented ORM all the way down to storing object data on the database in a non-normalized/object oriented fashion, how did that affect you? Did it muck up reporting? What benefits did you realize on the database side of the house? I’m really as interested in benefits as I am costs.
I’m really looking for real-world, hands on information. Complaints or speculation about how stupid a lowest common denominator set of dynamic queries might be… well, I’ve got that complaint well in hand. I need as much hard data as I can collect so that I communicate enough information to my boss, his boss and his boss in order for them to make informed decisions about this and go into it with their eyes fully pinned open.
I can not offer much concrete information, but I am facing the same set of questions and I too am looking for detailed information. One of my concerns that perhaps is unfounded but related to my minimal ORM knowlege is that of performance. In addition to security issues, stored procedures provide a parsed, compiled and optimize mechanism to access data. If ORM represents data as objects it would seem that data access would require adhoc queries which would be very costly in terms of performance. What is not clear is if perhaps the ORM mechanisms create and or stored query plans to allow optimal access of the data. It would be good to know if there have been any comparitive studies of this ORM paradigm as compared to the classical paradigm.
I still haven’t found a solid white paper or study on the impact of ORM methods and tools on database performance. The information I have found shows that the tools used for ORM such as nHibernate, LINQ and Entity Framework are all capable of working with normalized data quite well. All them also can work with stored procedures. I’ve also found plenty of information indicating that going with an object model inside your database leads to all the types of problems that we can imagine; loss of integrity, poor performance, security holes, etc. Since the tools can readily map between a normalized structure and objects, I don’t see why they shouldn’t be used that way so that the database isn’t made into a stupid storage medium. If that’s really all you need, the file system is still available.
“I’ve also found plenty of information indicating that going with an object model inside your database leads to all the types of problems that we can imagine; loss of integrity, poor performance, security holes, etc. ”
I would argue that this is purely down to bad design, the right tools being used in the wrong hands.
But which part, the object model inside the database? I agree. Everything I’ve read, so far, says that using these ORM tools is a godsend. Providing you don’t do exactly what is being performed with way too many of the systems I’m seeing, use the object all they way into the data storage. I’m absolutely not knocking, or questioning, ORM. I am questioning the use of it. Like a hammer, a chainsaw, a rifle or a howitzer, it’s a tool. Use appropriately, good and constructive things can be done. Used inappropriately… BOOM!
[…] I’m really looking for the real world, hands on information. Complaints or speculation about how stupid a whole lowest common denominator of dynamic queries could was… well, I have this much complaint in hand. I need as much data as I can collect so I communicate sufficient information to my boss, his boss and his boss that they go inside with their eyes all the pinned open and make informed decisions about it. refering: https://www.scarydba.com/2008/04/11/nhibernate/. […]