Great article as always Dave. When you spend a lot more time with nHibernate there are plenty of interesting/challenging things to find. I forget the details now but when we made the changes about 2 years ago to use the 'prepare_sql' option we managed to kill our site so we had to undo that change quickly. I'll admit our developers use of this ORM tool seems to me as pretty bad as I have had a long standing battle to get them to either use it properly or bin it and go with something better. Don't get me wrong, we've got some extremely good developers and some are the best I've worked with. However, SQL remains a black box for most of them.

The queries you get when tracing are nigh on unreadable and needs a fair bit of formatting before you can actually work with it and I find that a lot of the queries I see are very long so this makes this more time consuming. When you look at the way it approaches aliases, it's not something easy on the eye. It often lumps statements together but there is no visible seperation/formatting in the code.

One of the main problems I see with nHibernate is the way it approaches parameters for IN statements. I fully admit it's most likely our developers way of using it, but essentially when you pass in a list of ID's they come through in the underlying query as 'SELECT blah from myTable WHERE Id in (@p0, @p1, @p2.....)'. This ends up with a different hash of the execution plan for each variation of the same query. If I pass in 100 parameters I get one variation. If I pass in 101 I get another identical plan, albeit with a different hash so technically it's a different plan. As such we spend most of our time compiling execution plans that we only use once. Imagine the above scenario whereby we get up to 2000 params and you can see the problem (2000 potential different variations on a query). Yes, we've actually maxed out on params passed to a statement. I reckon we use 80% of the plans once before they are aged out. I've been told that the more recent versions have addressed this but I haven't seen it yet. The width of an nVarChar seems to cause it confusion. If I have a param with 7 characters I get one variation with the query using a param of nVarChar(7). If I change the value being passed in to 8 characters it changes the param in the query to nVarChar(8). Combine the two of these comments above and it gets silly. I haven't yet found anyone to agree to setting this to (and here is my crazy idea) the width of the column.

When I run a side by side comparison with properly sized params and passing in a table variable or even a varbinary and parsing that into a table variable the performance difference is huge. Then with plan re-use, a proper version becomes so much more scalable and performant.

I challenged our developers to find a suitable solution and that I would buy lunch for whoever solved it in nHibernate. My money remains in my pocket.

I found an interesting read on http://www.codeproject.com/KB/database/NHibernate_Perf2.aspx and figured that I could use nHibernate's HBM file to set a really performant query in the config and map the output to the object that the developers wanted. For me this seemed to work. The developers I've spoken to have said no, because they don't want to alter the HBM config file. Argghhhh ! It's a shame because if I'm right this would solve it for us all and allow the query to be source controlled along with the code.

The end conclusion I've come to after just over 2 years is that ultimately it does save time when coding, but for a high concurrency site it gives us a glass ceiling that will hamper us at some point. I can counter a decent chunk of this, but we give up a lot of tuning options, diagnosis, performance and so on. I've had to resort on some occasion to figuring out patterns of index use over time intervals to try and reverse engineer things so that I can guess what query is likely to be used and then trying to help figure out where the query came from so that we can track it back to the code. Do-able, but not exactly fun. Finding and diagosing a problem nHibernate query turns it into finding a needle in a stack of needles or death by a thousand cuts.

Overall, it's fine for gaining development speed but developers seem to forget the trade off of them doing this work (they only want the initial development benefit, not the full cost) so we effectively give up any hope of making the queries perform well when you throw load at them. I DO think it can work, but I haven't seen anything impressive from the DBA side of things yet. It's school dinner coding. Everyone gets fed, but it's not exactly Claridges is it ?

Recently, we've been putting together something new so I took the approach of tuning the hell out of everything before seeing if they could get nHibernate to match it. The answer was quick and simple as it was 'no'. We went with using a call to a stored proc. It was surprisingly easy to call this in code.