+972 52-548-6969
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:
- (09 Dec 2010) Alert on bad ‘like’ query
- (10 Dec 2009) Filter static files
- (16 Nov 2009) Exporting Reports
- (08 Oct 2009) NHibernate Search Integration
- (19 Aug 2009) Multiple Session Factory Support
- (07 Aug 2009) Diffing Sessions
- (06 Aug 2009) Capturing DDL
- (05 Aug 2009) Detect Cross Thread Session Usage
- (22 May 2009) Detecting 2nd cache collection loads
- (15 May 2009) Error Detection
- (12 May 2009) Queries by Url
- (04 Feb 2009) View Query Results
- (18 Jan 2009) Superfluous <many-to-one> update
- (18 Jan 2009) URL tracking
- (10 Jan 2009) Detecting distributed transactions (System.Transactions)
- (06 Jan 2009) The Query Cache
- (05 Jan 2009) Query Duration
- (24 Dec 2008) Unbounded result sets
- (24 Dec 2008) Row Counts
Comments
Can you post a good link to the SELECT N + 1 "problem"(?)
ayende.com/.../...SelectN1ProblemInNHibernate.aspx
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
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
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.
Bryan,
I don't need to worry about this, since NH produce predictable SQL for all scenarios
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.
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.
Not wanting too pedantic but any 'magic numbers' (i.e. not 0 or 1) should be a constant at least.
Wouldn't it be possible to catch N+1 using listeners?
I don't think so. You don't have enough information there
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.
Steve,
I would gladly take a patch for that. I don't consider this an easy problem
Comment preview