Ayende @ Rahien

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

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,953 | Comments: 44,410

filter by tags archive

NH ProfHow to detect SELECT N + 1


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:

image

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

Comments

Buckley

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?

Cheers

Bryan

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

 something

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

Bryan,

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

Scott,

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.

Demis

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

Steve,

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.

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats