Sadly the terms “ORM” and “performance problems” often travel together. By hiding the underlying SQL from the developers, ORMs can offer a huge productivity boost. Unfortunately they also make it easy to generate ridiculously bad queries without realizing it. Usually database administrators can find the offending code by cross-referencing an offending stored procedure with the code that calls it. But with the dynamically generated SQL that ORMs rely on, that simple trick rarely works. So we are going to take a spin around some beefier tools for ORM profiling.
NHibernate
NHibernate Profiler (NHProf) includes what you would expect from a base-line product. In addition to keeping overall statistics, it shows a list of recent queries with the full SQL being called and back-traces to the code calling it complete with stack traces. The interface is clean and heavily cross-referenced, making it easy to switch from examining an interesting query to, for example, to looking at all the queries a block of code uses.
The real winner for NHibernate Profiler is alerts feature. Some alerts indicate immediate problems like database errors or accidentally using a session from multiple threads. Other alerts indicate possible issues like a large number of individual writes that probably should be grouped into a single batch. There are also alerts that warn of designs that lend themselves to future problems like unbounded result sets.
An unbounded result set is where a query is performed and does not explicitly limit the number of returned results using SetMaxResults() with NHibernate, or TOP or LIMIT clauses in the SQL. Usually, this means that the application assumes that a query will always return only a few records. That works well in development and in testing, but it is a time bomb waiting to explode in production.
The down-side of NHibernate Profiler is that it is somewhat invasive. You cannot attach to a running process, it has to be configured up front. While the recommended way is to include a reference to NHibernate Profiler directly in your code, users of log4net can also activate the profile via an app.config setting.
NHibernate Profiler is a commercial product that starts at about $300 dollars per user.
LINQ to SQL
DataContext logging is the first thing developers will probably consider when looking into LINQ to SQL issues. The DataConext object has a Log property that will route all SQL it generates to the TextWriter of your choice. Though free and easy, the sheer volume of raw SQL it spites out makes it of questionable use for non-trivial applications.
LINQ to SQL Profiler (L2SProf) is by the same company that built NHibernate Profiler and appears to work in a similar fashion. In addition to real-time profiling, you can configure it to write log files to be analyzed later. Like NHProf, it runs about $300 dollars per user. Currently it is in beta.
Huagati Query Profiler is another invasive profiler. They flat out say that “the runtime components are designed to be integrated and distributed with applications that are using either Linq-to-SQL, or LLBLGen Pro for accessing SQL Server.” Fortunately the profiler can be turned on and off at runtime as needed.
The user interface for Huagati is downright archaic. It consists of a single massive grid taking up half the screen with the SQL and stack traces beneath it. It lacks all of the niceties that NHProf/ L2SProf have like alerts and summary screen, but it does have one compelling feature for the hard-core developer, detail.
Huagati’s profiler doesn’t just give absolute durations and row counts, it really digs deep and pulls out all the information SQL Server makes available. Instead of just the round-trip time, you get to see important information like how much time was spent compiling the query verses executing it. I/O is included and you can even see the actual execution plan.
Huagati Query Profiler is a commercial product that starts at $50 dollars for a limited version and $120/user for the full version.
ADO.NET Entity Framework
The story here is downright pitiful. First of all, you don’t even get basic SQL logging like what’s available in LINQ to SQL. Moreover, it appears that no one is making profilers that work with it.
Conclusions
There are plenty of other ORM frameworks for .NET, but they are all pretty much in the same boat as ADO.NET Entity Framework. Aside from the two profiling tools mentioned here, there is little or no support for anything above using your database built-in profiler.