Object Relational Mapping and SQL Profiler
NHibernate is a great technology for developing object orientated programs which interact with databases. Once in place, it provides a layer of abstraction to shield your object-orientated code (for example, an invoice contains a collection of items inside it) from the relationship-orientated mechanics of databases (a row in an invoice table which is linked to rows in an item table via a key). It does this by providing an object-relational mapping (ORM).
When in place and working, developers are free to think about entities as objects rather than rows in tables.
As with most abstraction layers however, they are not perfect, and require careful configuration to ensure they do not start breaking down and expose the database layer underneath. When putting ORM in place, you have to carefully consider what is happening in your database, and how to properly and efficiently map between objects and their tables.
The best way to go about this is to thoroughly understand your ORM tool of choice. A good way to aid your understanding in complex use cases is sometimes to look under the hood and see exactly what’s going on. We can do this with database profiling tools, which monitor the traffic going from and to your database. There is the excellent NHProf for exactly this purpose if you are using NHibernate, however if you are using the full version of SQL Server, you also have SQL Profiler at your disposal for use with any ORM tool.
Start a new trace in SQL Profiler, and choose a default trace. Run your application, and you should see something like this:
First thing you might notice is there’s a lot of meta information, most of which you will not be concerned with when profiling your ORM. It costs valuable brain cycles to mentally sift through the reams of data. To improve on this, we can apply filters to give us just what we need.
Start a new trace, and choose to ‘show all events’ and ‘show all columns’ in the events selection tab. Then select Column Filters, and under ApplicationName, we can add %SQL Server Management Studio% as a ‘Not Like’ filter (this will exclude traffic from SQL Management Studio, which can confuse things). Also, under DatabaseName, we can add the database you are profiling to the ‘Like’ filter (this is useful if it is a shared database with other unrelated traffic going on – you will only get data for your database).
We can also choose to show specific events. I like to include the following for profiling simple NHibernate apps:
- Blocked processes report
- ErrorLog
- EventLog
- Exception
- Execution Warnings
- User Error Message
- RPC Completed
- SQL:BatchCompleted
- TM:Begin Tran completed
- TM:Commit Tran completed
- TM:Rollback Tran completed
- TM:Save Tran completed
Health Warning!
Be careful with your selection of events – you can easily exclude important information! What you want to include will vary depending on what you are doing. For NHibernate, I find the above selection gives me what I need. Most NHibernate traffic is sent using the inbuilt sp_executesql stored procedure and the SQL Batch event, and transactions are also important. Running a trace with no filters at all is a good way to work out what you can exclude and what to include.
Of course, it’s a pain to set this up every time. So you can save your filters in a trace template (do this from the File menu). You can then set this up as your default trace template, and maybe put it on your internal wiki so it’s available to share with other developers.
With these filters in place, the output looks more like this:
Much better. With a trace like this, it is much easier to look under the ORM hood and see how complex queries are being put together. If you know your SQL and understand ORM, you will be able to spot problems and inefficiencies in your code and mapping.
Taking Things Further
Taking things further, using the Database Tuning Advisor alongside SQL Profiler allows us to improve our database schema, particularly the use of indexes.
Run a trace (ideally in a live environment in real use) and save the results to a file or table. If we fire up the Database Tuning Engine (found alongside SQL profiler in SQL Server’s performance tools) and give it the results file, it will analyse the results and recommend any indices it thinks would help.
The following example shows, for example, that the Member table might benefit from an index on the Forename and Surname columns, due to the frequency of lookups on these columns:
Another health warning!
The quality of advice the Database Tuning Advisor gives (I’m calling it advice because that’s what it is – you can’t rely on it being right) will vary depending on how representative your sample trace is. Also remember the usual caveats of using indexes: they may slow down inserts, updates and deletes, and too many can consume disc space and slow things down.
James
