Geeks With Blogs
Billy McCafferty whatever (but really just .NET)

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!)

Posted on Wednesday, March 8, 2006 10:46 AM ASP.NET , NHibernate | Back to top


Comments on this post: Giving NHibernate a serious performance boost

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
can you explain how you were testing the number of reads? i would like to test in my app and share the results with you.
Left by sean on Apr 01, 2006 7:03 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
From Pete Weissbrod, another developer I work with who specializes in performance... Perfmon (using the sql counters) and SQL profiler are great tools for
identifying bottlenecks in the data layer. Perfmon's sql:lock counter serves
as a birds eye view for finding potential scalability issues. SQL profiler
allows you to examine speed costs on a query-by-query basis.
For effective performance tests, get the application and the db in an
ISOLATED environment (i.e. your local laptop if possible). Run the debugger
on a transaction of interest in one window. In another window, run sql
profiler, watching the local db. Break before queries. The profiler will
display to you the actual sql that nhibernate is sending to the dbms, along
with the time in ms for execution, and the number of reads necessary to
complete.
The number of reads refers to the number of times the dbms had to "traverse"
its data hierarchy to get to the correct data. In sql server, data is stored
in a b*-tree (multiple indexing), where each step down the tree is a "read"
(index seek). If searching on a non-indexed value, the dbms is forced to do
a naïve "scan" thereby increasing the number of reads. Take one of the
expensive queries from profiler and use query analyzer to see the "plan";
this will tell you if you should consider adding a new index to avoid
"scanning". Optimizing the query is sometimes a matter of coaxing the
optimizer to run the query in the way you envision.
Left by Billy on Apr 03, 2006 8:28 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Was this against sql server 2005!?
Left by chad on Jul 31, 2006 7:48 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
SQL 2000 actually. I haven't actually done anything with NHibernate/SQL 2005. I assume you may have some comments on the subject?

Billy
Left by Billy on Aug 01, 2006 8:50 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
I tried this and my site became 33% slower..

Are you sure this is the right way?

I added this in nhibernate.config file in properties section.
Left by Prashant on Sep 20, 2006 10:56 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Would this setting affect MySQL as well ?

If so, what would be the value of the setting? Just the database name, since there is no dbo thing in mysql...

Thanks
Left by Martin Bittner on Dec 28, 2006 2:07 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Although I can't attest to the performance gain for MySQL, I'd recommend adding it in, nonetheless. (And please let me know if you end up doing any benchmarking to confirm a performance difference!)
Left by Billy on Jan 03, 2007 2:14 PM

# NHibernate ja sp_execsql
Requesting Gravatar...
Billy McCafferty jagab oma blogis head n&#245;u NHibernate-i j&#245;udluse parandamiseks andmebaasidega, kus koormus on v&#245;rreldes tavaliste rahulike veebikeste ja intranetidega suhteliselt &#228;&#228;rmuslik. See trikk ei puutugi ehk otseselt niiv&#228;ga NHibernate-i k...
Left by DT blogi on May 22, 2007 5:13 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Great article, thanks you for sharing
Left by unibet on Dec 28, 2009 5:34 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Thanks for sharing !
Left by giochi per ragazze on Jan 18, 2010 2:29 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Thanks a lot my friends
Left by psychologue à domicile on Mar 09, 2010 6:15 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Nice, but crazy, thank you my friend
Left by ACN on Apr 25, 2010 10:34 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
thanks buddy!:
Left by paris sportif on Jun 03, 2010 5:53 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
that's good thanks!
Left by jouer casino on Aug 21, 2010 9:39 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
i like your blog
Left by machine à sous gratuit on Aug 21, 2010 9:44 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
I really loved reading your blog. It was very well authored and easy to understand. Unlike additional blogs I have read which are really not good. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he enjoyed it as well!
Left by coco chanel on Dec 05, 2010 9:58 PM

# windows 7 key
Requesting Gravatar...
I find html too complicated, so its really nice of you to post this as I have been looking for html code for setting up a calendar.Office 2010 Key
Left by Office 2010 Product Key on Dec 09, 2010 1:37 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...

Hello friends, I am new in this blogb. Hope I will enjoy here a lot...
Left by jocuri cu hannah montana on Dec 12, 2010 10:20 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
NICE BLOG
Left by Jocuri pentru copii on Jan 01, 2011 5:52 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
i like this blog is verry interesant..
Left by Jocuri de gatit on Feb 18, 2011 5:54 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
I liked this blog. Thanks.

Does default_schema with NHibernate 2.0 and Oracle 10g will give performance boost?

Thanks,
Rao
Left by Rao on Feb 23, 2011 12:05 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
The number of reads refers to the number of times the GAMES
Left by skill GAMES on Mar 11, 2011 2:00 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
I am new in this SITE. Hope I will enjoy
Left by JOCURI LOGICE on Mar 11, 2011 2:02 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Unlike additional blogs I have read which are really not good
Left by JOCURI MASINI on Mar 11, 2011 2:04 PM

# This is my first time i visit here. I discovered so numerous interesting stuff in your weblog especially its discussion. From th
Requesting Gravatar...
interesting post, pretty much covered it all for me, thanks.
Left by Windows 7 Key on Mar 30, 2011 2:38 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
keep up the wonderful work , I read few articles on this web site and I believe that your web site is rattling interesting and holds lots of superb info
Left by onyx cup series on Mar 31, 2011 7:07 PM

# NoutatioutatiGiving NHibernate a serious performance boost
Requesting Gravatar...
Nice, but crazy, thank you my friend
Left by Noutati on Apr 03, 2011 12:49 PM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
Interesting article
Left by poker freeroll on Sep 07, 2011 5:34 AM

# re: Giving NHibernate a serious performance boost
Requesting Gravatar...
WOW, thanks for that very very simple improvement - great. Didnt know about this simple performance tip :-)
Regards
Left by Lelala on May 04, 2013 2:04 PM

Your comment:
 (will show your gravatar)


Copyright © Billy McCafferty | Powered by: GeeksWithBlogs.net | Join free