I’m getting my first look at a full-fledged nHibernate database developed by consultants for our company. I thought I’d share my initial impressions. I’ll be capturing trace events from the database over the next couple of weeks, so I’ll be following up on the behavior of nHibernate within this database as well.
The first thing I saw & thought was, “Foreign key constraints. Thank the gods.” That really is good news. I was frankly concerned that they might go with the “let the code handle it” approach. There are quite a few null columns. I’m also seeing tons & tons of nvarchar(255) which must the default string size. Lots of bit fields too. They also used bigint in a lot of places too. None of this is definitively good or bad, just observations.
There are tables that lack a primary key. That raises a bit of a concern. The primary keys I’ve looked at have all been clustered, which isn’t too problematic since that’s probably the primary access path. There are a few unique constraints on some of the tables too.
Overall though, I don’t see anything that, at first glance, makes me want to run screaming from the room (or pick up a big stick & start looking for developers). The devil is no doubt in the details. Time to get started looking at the trace events.
I have been having to deal with SQL Profiler a good bit lately on an application to figure out what it is doing (or actually what it is NOT doing). Would you be able to share what you configure your trace to capture in order to get a good picture of what the database is doing (if it is other than a default trace)?
Currently I’m just running a slightly modified default trace. I only care about RPC Complete and SQL Batch Complete for initial data collection. Just make sure that you’re not running those through the GUI and you’re outputing to file and you’ll effectively have no impact on the system you’re monitoring at all. You just need to deal with the data you collect.
After that, it really depends on what you need to look at. For example, we have systems that get quite a few deadlocks, so we capture deadlock graphs in the trace, but other systems don’t need it. Check out Brad McGehee’s excellent book for more details: http://www.amazon.com/Mastering-Server-Profiler-Brad-McGehee/dp/1906434158/ref=sr_1_1?ie=UTF8&s=books&qid=1266327665&sr=8-1
Hi
Great and subtle post.
A few comments:
-There are quite a few null columns.
You should define it in the mappings. NHibernate doesn’t know what you want if you don’t tell him.
– I’m also seeing tons & tons of nvarchar(255)
It’s the default, you could change it in the mapping
-Lots of bit fields too
It’s for booleans. You can change the type in the mapping and add a substitution in the configuration.
– They also used bigint in a lot of places too.
I suppose you have properties o type long, that’s correct?
– There are tables that lack a primary key.
Mhhh. Many-to-Many relationships doesn’t force pk’s, at least if you use collection type. That’s because you could have repeated stuff in a collection. I think you could manage this in the mapping.
– There are a few unique constraints on some of the tables too.
You can add your unique constraints by using NaturalId mapping: I read somewhere that it’s useful in caching, but I don’t know.
Hope it helped!
Useful info thanks. I’m still exploring this stuff, so information is good.