I’ve blogged in the past about the nHibernate project that has been going on at my company for the last eighteen months. Prior to today, I have only seen the database generated by nHibernate. But today I finally started seeing some TSQL code. My first impressions… oy!
There are two levels of concern coming out of the gate. First, it appears that some of the programming decisions, completely independent of nHibernate, are going to cause problems. Second, it appears we’re going to be hitting issues with nHibernate.
First, the programming decision. I’ve been using Confio to monitor this server for a few days now (look for upcoming posts on my eval of Confio). Only one day has captured any real activity from the nHibernate team (and yes, I’m basically spying on these guys because they are absolutely insistent that my team stay out of their way). The longest running query was two calls to this (names have been changed to protect my job, there are no innocents):
SELECT BookID
FROM Books WITH (updlock,rowlock)
WHERE BookID = 42
What they’re doing is locking the row so that no other user can get at it while it’s being edited. Total lock time for the first day was 38 seconds for two calls. I’ve posted about this previously, so I won’t go into it again, but this is potentially a disaster.
On to nHibernate. The next query was pretty benign:
SELECT OrgID
,OfficeID
,StartDate
,EndDate
,OtherID
FROM SpecialList
WHERE OfficeID = 42
Unfortunately this table, as created out of nHibernate, has no primary key, no clustered index or any other type of index, so this is just a table scan. But it’s only on 109 rows… in dev, not production, oh, and most of the 109 rows have a null value for OfficeID, but it’s all good I suppose… until I notice that this query also had 38 seconds of wait time, but it was called 394,652 times… oy (and no, I’m not even one of the chosen, but that’s a wonderful expression for this situation). Looking at the data in cache, this query has been called, since it was created in cache on the 2nd, 598351 times with a total elapsed time on the server of 5429689ms. The average then is 9ms, but the max was 29296 or 29 seconds.
The next query up looked like this:
SELECT col1
,col2
,col3
FROM Table1
INNER JOIN Table2
ON Table1.ChildID = Table2.OtherID
WHERE tabl2.ParentID IN (@p1,@p1,@p2…@p99)
Yep. 99 parameters passed and used against an IN statement. I can’t post the execution plans on this stuff without talking it over with the boss, but suffice to say, it’s two table scans and a merge join to get the data back. Oh, and this was called 1,138 times with a minimum elapsed time of 976ms. Yeah, minimum time to call this proc is 1 second and it’s been called over a thousand times.
It went down hill from there. I’m looking at other queries, one that hits seven tables, but instead of using JOINs uses a bunch of sub-selects in the WHERE clause. It has a minimum run time of 9 seconds. Luckily it’s only been called 64 times.
This is a horror show.
A lot of benefit can be reaped if we’re able to go in and create some indexes on these tables, but that’s going to be an issue anyway because I’m seeing lots of implicit data conversions on the parameters being passed in, as I noted a couple of years ago. At this point in time, I would say, at best, I’m very underwhelmed by what nHibernate is producing. To be perfectly honest, I’m concerned for the health of the project.
Grant,
Keep these posts coming. I have a presentation, Why I Use Stored Procedures and You Should Too, and this helps me with some ammo.
This is so wrong that I don’t even know where to begin. I’m not going to say much about the schema and indexing issue outside of this: The schema generation should really only be used as a starting point for getting going rapidly. Someone with DB smarts needs to refactor that design into something that makes sense in the terms of relational design, best practices etc…
As far as the 99 parameters in the the IN statment goes – NHibernate structures the SQL query depending on how the developer structures the NHibernate query. This means the developers have all the power in the world to turn that into an acceptable query. NHibernate’s SQL generation is not a black box.
As far as the N+1 select goes – frankly this can be easily solved by doing an eager fetch with NHibernate. It looks like the devs were clueless on this point, and probably didn’t even realize it was happening. It’s also worth pointing out that this thing happens all the time with hand rolled data access layers with stored procedures. One of the biggest advantages in ORMs IHMO is the ability to easily avoid this scenario.
Perhaps most embarrassing is that they must not have profiled the queries coming out of NHibernate. An ORM is not a replacement for SQL knowledge and profiling.
Yikes!
Jack, I think I can guarantee more info. You may have to give me credit at some point.
Honestly, it sounds to me like your development staff needs some assistance in improving their NHibernate implementation patterns within their application. All of these SQL-interaction (anti-)behaviors are *possible* with NHibernate, but they are all (of course) possible without NHibernate too.
All of these are symptoms are NHibernate implementation anti-patterns and all have (relatively) straightforward solutions to them within the manner in which your developers are using NHibernate.
Improper/ill-advised application of any tool isn’t (inherently) a reason to decry the tool’s general use by people who know how to wield it.
Daniel
I’m not shocked that our developers might not be doing this correctly. Unfortunately, my power to redirect them is probably negative at this point. They long ago decided that the reason it took them so long to develop software was because of us DBA’s. If I were to suggest they were doing things wrong, they’d probably double-down.
But, it’s good to know there may be solutions for this mess.
Steve,
When you consider their first decision, to intentionally open a lock on the database from the client that they’re going to leave open, it’s not surprising the rest that follows.
I’m not trying to denigrate nHibernate. However, it was presented to my company as a way to get rid of all those pesky database problems and make it so those mean old DBA’s don’t slow down development any more. It really is supposed to be a magic bullet for these guys. Do I believe that? No, of course not. It’s just another tool. Like any tool, properly used, no one gets hurt. Improperly used… you get 99 parameters passed to an IN clause.
But the problem we have here is that, because nHibernate has eliminated us from the process (whether it should have or not, that’s what happened), we’re not catching this stuff as it happens. Instead we’re looking at an app that is six weeks away from code complete after an 18 month development cycle and it looks like this.
“But the problem we have here is that, because nHibernate has eliminated us from the process (whether it should have or not, that’s what happened), we’re not catching this stuff as it happens. Instead we’re looking at an app that is six weeks away from code complete after an 18 month development cycle and it looks like this.”
NHibernate has not eliminated your group from from the process – that can only be due to people in your company. If the teams are not working together for the success of the project it is not going to happen regardless of the tools used.
Sorry, somewhat badly phrased. “Because the use of nHibernate has been used as a wedge to eliminate us…”
The core issue is that nHibernate, true or not, is the magic that makes it possible to get DBA’s out of the development team. I know people don’t like that statement, but it’s true. I’m not knocking the software when I say that.
Not surprised at all. From what I’ve seen nHibernate means you need a DBA mroe than ever.
I’m a developer rather than a DBA, but when I joined a project using nHibernate it was immediately obvious that it was going to be a huge problem. the guys who had implemented it believed everything they read and had confidence it was a wonder tool.
I sort of hit the roof when I found a 28 table join, bringing back hundreds of columns form those tables. I stripped it down to find it needed 4 of those tables. The rest were joined in because of object relationships defined in nHibernate – so we had a choice, break the object model or put up with trash SQL.
The final straw was when one of our guys found selecting a different record in the UI sent 3.5Mb (!) from app server to client, utterly horrendous.
There are numerous sins on the code side of things too.
We’ve solved these poor queries by basically ditching nHibernate, a process that will continue. My impression is that its probably good for OLAP stuff and very small scale applicaitons (typical shop/shopping basket website). For the type of apps we develop I don’t believe it has much, if any merit.
Thanks Mark,
That’s a very different point of view coming from a developer. It’s not one I’m used to hearing (being one of those monstrous, evil, DBA types). I’ve been of the opinion, like Maggie Longshor & Steve Bohlen up above, that nHibernate is just a tool, and properly used it should be able to work with large scale, serious systems. Do you feel that statement isn’t quite true?
Although I’m a developer I’ve messed with data quite a bit which has inevitably led to me doing DBA type work – design, queries, procs, using stats & execution plans etc.
Large scale ? Difficult for me to say to be honest. I think that, provided you only want to pull data out of one table (or object, depending on which side of the fence you are on) at a time its fine, but when you need to do summary queries across related tables there will be tears at bedtime, hence my view of small apps & OLAP/warehouse stuff.
One of the previous companies I worked at had a fairly large application & database (1000 tables or so IIRC), I don’t believe nHibernate would have been viable for that – too many summaries, ad hoc queries and data manipulation.
Just to clarify – the work we’ve done to remove nHibernate from critical areas could of been done (in theory) by using HQL (if you haven’t investigated its basically SQL for objects, but not in a good way) and POCOs to hold the result, rather than our standard domain object.. but there is no advantage at all in doing this vs plain SQL & POCOs. Its acknowledged that using nHibernate will cost somewhere between 20-30% over raw ADO.Net, after all it only wraps that functionality and throws in a metric bottomload of reflection for laughs. thats *if* the query you send is decent, which as you are finding, few are.
As a DBA you should “own” the HBM.XML files so you can at least ensure that the schema is in reasonable shape (primary keys, indexes, column length/nullability etc).
Sounds to me like your developers think nHibernate means you don’t need a DBA, which is a bad choice. I’d ask if they’ve done high load performance testing with lots of data. The pessimistic locking doesn’t sound like a great idea for one thing.
let us know how you get on 🙂
Mark,
Thank you very much for the feedback. I will post more events as they occur. At this point in time, no load testing has been done. Some is supposedly scheduled for a later date, but it will be done after code complete… yeah, I know. Thanks again.
Yep, incompetent ORM nonsense like this from one side is what’s feeding the NoSQL nonsense about being faster than SQL from the other side. Which of course helps to self-justify the previous decisions by developers from both side to not include DBAs and SQL development experts in these bad decisions in the first place. … 🙁
Hey Barry,
It does feel like a hole is being dug. Unfortunately, I’m largely a spectator on this.
It is always easier to blame the tool for sloppy work than to do proper analysis. Have been a DBA and love what SQL can do, but am also developing with NHibernate at the moment. Just like with any tool you have to use it to come to understand it. For me the “NHibernate In Action” did explain quite a few things. And as always you first have to see the logical conclusion of certain practices before you change your ways as a developer. They will be burned as well as the DBA for poor performance! All the problems discussed above seem to spring forth from misunderstanding what the NHibernate tool is trying to achieve and how it goes about it. Have myself buid ORM implementations in VB before and it is not easy to get things right. NHibernate took a lot of that pain away, but as always, problems don’t disappear but morph into something else. All specialties are needed when developing applications. So far the magic tool which excludes any softare developers has still to be found. Maybe the DBA should talk with the test team, they will be able to point out what unacceptable performance is?
I would like to expand upon the sentiments expressed by Maggie. Based on the article and the comments I’d say you have a somewhat strained relationship with the developers.
I’ve seen this at several companies I’ve worked at over the years. I’ve seen it be the result of developers who lorded over the DBA because of company politics. I’ve seen seen the DBA boss around the developers, also because of company politics.
People have egos, and technical people as a general rule, have really huge ones. The moment that someone in a meeting makes a snarky comment about the other camp things tend to go downhill. Phrases like “big meanie DBA” are remembered and repeated for months.
Based on the information provided here, it sounds like the development team was looking for a way to not have to deal with a DBA. NHibernate happened to be an ORM tool that they thought allowed them to do this, erroneously so I might add.
However, my point is that decrying NHibernate (or any other tool) isn’t going to fix the distrust that apparently exists between the developers and DBA. You’ll notice all kinds of crazy things being done in this type of environment as each team takes potshots and tries to protect themselves from the other camp.
If I were you, I’d be more concerned about the state of my relationship with my developers than I would be about NHibernate in particular. The developers may be total jackasses and impossible to work with. Then again the DBAs might not be perfect either.
Developer’s need DBAs! A good DBA provides expertise in managing and manipulating data in relational databases and is focused on efficient data access and maintaining data integrity. These are things that the typical developer frequently overlook or take shortcuts for in their applications.
At the same time, DBAs need developers! The data that the DBA is zealously guarding has to be used by an application of some sort to be useful to people making business decisions. The very tools used to manage the data are applications built by a developer somewhere.
It’s a symbiotic relationship and the sooner we all reduce the size of our egos in this industry the better off we’ll all be. We are all supposed to be on the same team after all.
Thanks Nevada,
If I implied, in any way, that I think the DBA’s are virtuous & without flaw, please allow me to correct that now. Being one of the DBA’s involved, I’m all too aware of just how horrifyingly flawed we are. And I agree that most breakdowns in communication are two-way. But we’re in a different situation here, this one time. This development group is being run by an outside consultant who had no prior history with our DBA team. He walked in the door and declared he knew how to solve the problem and the problem, at least in terms of speed of development, was that nasty old database. He brought in several other consultants that have worked with him previously and they have been running for almost two years with very little involvement from our team, positive or negative. In fact, we’ve come to them, several times, as supplicants, begging to be involved and we were rejected.
Whatever ego’s we have (and again, I know they’re over-inflated) and whatever communication issues we have (and since I’m again, one of the communicators, I’m all too aware of our shortcomings) those just weren’t involved in this one, distinct, situation. That doesn’t negate a single word you said, in fact, I’m in total agreement with you.
There are no applications without developers. My posts in and around nHibernate are not meant as attacks on developers, the developer community, nHibernate, the nHibernate community, ORM or the ORM community. I’m trying, evidently unsuccessfully, to post about my experiences with an ORM implementation using nHibernate, that, at least from my point of view as a DBA, is going horribly awry. I’m working within our environment to try to deal with it.
The one argument that I will make, and I’ve said it before, and there is plenty of evidence out there if you look, there is a very strong voice, or group of voices, that are singing the praises of ORM & nHibernate as a way to eliminate the need for DBA’s for anything except backups & consistency checks, because it eliminates the need for relational storage (which, in certain situations, absolutely should be eliminated, just because I have a hammer, doesn’t mean everything is a nail). I’m not trying to set myself up as a voice against ORM or nHibernate, in any way, but I don’t mind adding my voice to the group that says, as you say so well, DBA’s and developers need to work together.
if you use nhibernate, start thinking in nhibernate concepts. If you see it comparing with a database, your project will fail. NHibernate is not magic, it provides tools for improving the way it accesses the database. But you have to know them and make use of them when optimizing your application. You talk about indexes, that meas that you know what indexes are, so you know about databases. Do should know about nhibernate, not just use it, because without knowing you can’t expect something good. Some point of interest for those issues you wrote:
– Batch size
– Fetch mode
– Lazy loading
– Second level cache
– Query Cache
Finally, if somebody sells that nhibernate was a magic bullet, he is a stupid. And if your company bought it, your company is stupid too. And you can’t blame hibernate for that! It’s a tool, an excelent tool. Way better that using datasets or making the mappings one by one in classes in a read world project.
and that presentation “Why I Use Stored Procedures and You Should Too”, aajjjjjj I can’t believe the people continues using stored procedures… which is the magic bullet now?
Thanks for the comments ivowiblo. I don’t claim to know anything about nHibernate. I’m a DBA and a database developer. That’s where I spend most of my time. I’m just looking at what developers have done using the nHibernate tool.
Look at it like this. The concept of an ORM tool is a good one. I agree. The concept of a firearm for self-defense is a good one too. I agree. If my firearm goes off randomly, blowing holes in me, my property, my family and friends, all through more or less appropriate use, as a tool, it’s not meeting the needs of the concept. From what I can tell, to a degree, that’s happening here. It appears that it’s somewhat easy to blow holes in the database using nHibernate. That doesn’t invalidate the tool and it absolutely doesn’t invalidate the concept. I think ORM is a great concept and I support it. I’m just not convinced the tools are mature enough at this point in time.
And as far as anyone being stupid… nah, I won’t comment. Too easy.
“I think ORM is a great concept and I support it. I’m just not convinced the tools are mature enough at this point in time.”
The tool is mature. The people is not. I saw a lot of bad code and holes in database oriented systems and that’s not a reason to believe that databased are not mature.
We started a project using Nhibernate about 6 months ago and I went through the justification process. Fortunately i’m well respected within the management team and my concerns were listened to.
I had massive objections from key members of the front end and middle tier teams who seemed to think that Nhibernate saved the world. I offered to give them my pager, so that when an overnight issue occured they could sort it out. – for some reason they declined.
I also pointed out quite nicely that there would be no really nice way to identify performance Bootle necks within the system , and that basically they were circumventing all of the good things their dba brought them in order to make some quick short term gains. They had forgotten that the objective in the project was to re-write our existing product and make it more scalable (DOH!!!!!)
in short – don’t let them use Native mode. Working with NHibernate in proc mode is painfull, but worth it.
Excellent information, thanks for sharing.
Hi Scary DBA
Liking some of your experiences. If I can make a suggestion, you must have a great opportunity at your company. They have made a strategic decision to use NHibernate, but if you were to do some samples from your experience showing some of the stuff that hasnt worked and show a way if could be done which works much better (regardless if its nhibernate or other) then you could be in an excellent position to guide projects at your company. You could help the project teams with reference and support tomake sure the nhibernate stuff they did worked well and you could also give them guidance on the times when you shouldnt use nhibernate/orm.
This could put you in a really good position. I havent really come across any good DBA’s who really knew how to help a development team deliver a great data access strategy with ORM yet.
Excellent feedback Mike. Thanks.
I actually no longer work for this organization, but you very clearly outlined what I attempted to do while I worked there.
I wasn’t always successful, but it was the goal.
[…] (EF) and it’s fellow ORM tools is that they generate crap code. I know this to be true. I’ve seen it. ORM tools can, and do, generate seriously poor T-SQL. That’s not to mention the N+1 problem […]