Ayende @ Rahien

Refunds available at head office

NH Prof: How 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.

Comments

Buckley
10/23/2008 12:40 PM by
Buckley

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

Neil Mosafi
10/23/2008 01:15 PM by
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
10/23/2008 01:25 PM by
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
10/23/2008 01:27 PM by
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
10/23/2008 01:29 PM by
Ayende Rahien

Bryan,

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

Scott White
10/23/2008 04:06 PM by
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
10/23/2008 06:40 PM by
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
10/24/2008 02:15 AM by
Demis

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

Tuna Toksoz
10/29/2008 12:49 PM by
Tuna Toksoz

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

Ayende Rahien
10/29/2008 12:56 PM by
Ayende Rahien

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

Steve Campbell
10/30/2008 05:01 PM by
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
10/30/2008 10:12 PM by
Ayende Rahien

Steve,

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

Comments have been closed on this topic.