Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,125 | Comments: 45,493

filter by tags archive

NH ProfHow to detect SELECT N + 1

time to read 1 min | 65 words

One of the things that the NHibernate Profiler is going to do is to inspect your NHibernate usage and suggest improvements to them.

Since I consider this to be a pretty important capability, I wanted to stream line the process as much as possible.

Here is how I detect this now:


It is not perfect, but it is pretty close.

More posts in "NH Prof" series:

  1. (09 Dec 2010) Alert on bad ‘like’ query
  2. (10 Dec 2009) Filter static files
  3. (16 Nov 2009) Exporting Reports
  4. (08 Oct 2009) NHibernate Search Integration
  5. (19 Aug 2009) Multiple Session Factory Support
  6. (07 Aug 2009) Diffing Sessions
  7. (06 Aug 2009) Capturing DDL
  8. (05 Aug 2009) Detect Cross Thread Session Usage
  9. (22 May 2009) Detecting 2nd cache collection loads
  10. (15 May 2009) Error Detection
  11. (12 May 2009) Queries by Url
  12. (04 Feb 2009) View Query Results
  13. (18 Jan 2009) Superfluous <many-to-one> update
  14. (18 Jan 2009) URL tracking
  15. (10 Jan 2009) Detecting distributed transactions (System.Transactions)
  16. (06 Jan 2009) The Query Cache
  17. (05 Jan 2009) Query Duration
  18. (24 Dec 2008) Unbounded result sets
  19. (24 Dec 2008) Row Counts



Can you post a good link to the SELECT N + 1 "problem"(?)

Neil Mosafi

It's nice! Can you explain why you picked 4 as the threshold? Was it just arbitrary or based on some experience that if you have less than 4 it's not a problem?



Is the .RawSql '==' operator overridden? If the raw sql is just a string, it would be a lot more useful to compare the actual pieces of the string (and their order) instead of seeing if the two strings are identical.

IE: select * from


should be the same as

select * from something

Ayende Rahien

I had to pick a threshold, and 3 identical queries in the session seemed to be a good max to use.

I don't want to have to false positives.

Ayende Rahien


I don't need to worry about this, since NH produce predictable SQL for all scenarios

Scott White

Why not add these features to the NHibernate add-in for Resharper? I downloaded it recently and am added it. These could be warnings or something like that.

Ayende Rahien


I intend on making this a commercial project. As such, I don't want to limit myself to people having R#.

It is also a very different mindset than what R# is doing. The information is gathered at runtime, from the execution of the code, not from analyzing the source.


Not wanting too pedantic but any 'magic numbers' (i.e. not 0 or 1) should be a constant at least.

Tuna Toksoz

Wouldn't it be possible to catch N+1 using listeners?

Ayende Rahien

I don't think so. You don't have enough information there

Steve Campbell

I don't understand why none of the ORMs have built-in support to dynamically detect and optimize N+1 in a live environment.

The basics would be real easy to write - the ORM can correlate the "N" queries with the "+1" query, and prefetch batches of results when it detects a likely problem. Add in some statistics to enable smart decisions, and pretty soon you have the first ORM in the world to not care about N+1.

Ayende Rahien


I would gladly take a patch for that. I don't consider this an easy problem

Comment preview

Comments have been closed on this topic.


  1. RavenDB 3.5 whirl wind tour: I'll have the 3+1 goodies to go, please - 3 days from now
  2. The design of RavenDB 4.0: Voron has a one track mind - 4 days from now
  3. RavenDB 3.5 whirl wind tour: Digging deep into the internals - 5 days from now
  4. The design of RavenDB 4.0: Separation of indexes and documents - 6 days from now
  5. RavenDB 3.5 whirl wind tour: Deeper insights to indexing - 7 days from now

And 10 more posts are pending...

There are posts all the way to May 30, 2016


  1. The design of RavenDB 4.0 (14):
    05 May 2016 - Physically segregating collections
  2. RavenDB 3.5 whirl wind tour (14):
    04 May 2016 - I’ll find who is taking my I/O bandwidth and they SHALL pay
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats