When NHibernate prepares SQL queries for SQL Server, it passes them to sp_execsql. Upon first call, the query speed is lackluster as it parses and executes the SQL; on subsequent calls with the same query syntax, the speed gets a bit faster. But even on subsequent calls, the response time is much slower than that of a stored procedure. The problem is, by default, NHibernate does not fully qualify table names within its queries sent to SQL Server. To leverage sp_execsql to its fullest, requests must include fully qualified table names.
You can fully qualify NHibernate queries within your app/web.config by including the following attribute in the nhibernate settings:
<add key=“hibernate.default_schema“ value=“your_DB_name.dbo“ />
(Obviously, “dbo“ should also be replaced as necessary.)
How big of a difference does this make? We're realizing an order of magnitude difference in the execution speed of queries called subsequent times. A test query originally costing 255 reads went down to 12 reads. On more data intensive pages, this can result in an overall performance gain of 33% or more.
(A big thanks out to Pete Weissbrod for researching this!)