NH-1001 - Select statement issued for each not-found=ignore

[nhibernate-bot]

Tomer Avissar created an issue — 7th May 2007, 7:22:43:

When using the not-found=ignore attribute in a one-to-many/many-to-many, NHibernate is issuing a query for every element not found in the collection, even when using an outer join.

In some cases this causes a significant performance overhead.

---

justme84 added a comment — 7th May 2007, 7:42:57:

And what do you want it to do?

---

Tomer Avissar added a comment — 7th May 2007, 8:09:30:

I want it to skip the subsequent queries for the child objects.

Since an outer join was performed, NHibernate already "knows" the child records don't exist, so there is no use in requerying the table.

---

justme84 added a comment — 31st May 2007, 0:31:57:

I need a test case.

---

Tomer Avissar added a comment — 10th June 2007, 5:58:51:

This test case is against the Oracle "HR" schema, the file db.xls displays the relevant rows (I tried to create the test case on the xsl directly, but apparently excel doesn’t support join).

The case is demonstrated in the Form1_Load method.
the "sql generated.txt" file (in the zip) contains the sql generated, taken from the VS output window.

---

Jesse added a comment — 13th June 2007, 15:22:30:

This issue also shows up in many-to-one assosciations that are eagerly fetched. So I assume it shows up anywhere that you can specify not-found=ignore.

So the mapping below should not product an additional query when no result is initially found.

<many-to-one name="Stock" access="nosetter.camelcase-underscore" class="RentalStock" column="intProductId" lazy="false" fetch="join" not-found="ignore" />

If the association is not found on the initial query, an additional query will be issued to the database to try and retrieve the association again. There is no need for any extra query since this should be determined by the first query. Or is there some reason that a second query needs to be issued?

There is no second query when the association is found on the first query.

This problem magnifies itself if batching is enabled. It will issue a batch query multiple times for the number of proxies in the session to find out if the entity exists.

---

Karl Chu added a comment — 11th November 2007, 15:59:13:

Added test. r3093

---

canoby added a comment — 20th January 2010, 1:56:55:

This bug is causing serious performance issues in some of our applications. Because of the extra select statements generated when an entry is not found a query that should take 10-20ms takes close to 2 seconds.

In the description of this bug it is mentioned it affects one-to-many/many-to-many but it also affects one-to-one mappings (using References())

---

Data Hero added a comment — 25th February 2011, 5:17:17:

Version 2.1.2.4000 is also affected

---

Tomer Avissar added a comment — 28th February 2011, 7:40:29:

It's been a while since I logged this, but if I recall correctly, the issue is if you like to view it that way, is that the first level cache does not work for non existing records. Meaning, when NHibernate queries an entity (either by ID or through join) and receives no records, it does not save this information and therefore will query this entity over and over again, even though in each time it will get no records.

For example if I have a "Employee" entity with a many-to-one "Department" entity, if I change the many-to-one fetch mode to join, Instead of getting only one single joined statement, I will still get many select statements for each employee that did not exist in the employee table.

BTW: One more problem that the not-found="ignore" brings, is that if I had an Employee object with DEPARTMENTID=5 that does not exist, It will result in a NULL value in the Department property. Upon flush, it will update the DEPARTMENTID value to NULL.
I would expect NHibernate to not update this column in this case, but a guess that's a matter for a new issue.

---

Fabio Maulo added a comment — 10th May 2011, 16:06:45:

Yes Tomer....
The solution is clean the DB from invalid relations.

---

bpipe added a comment — 8th February 2012, 12:29:41:

Funny, it is not fixed yet
Fabio, it not always possible to clean a DB sometimes this non existing FK have special meaning, and you can't simply remove it or add PK record.

---

Nick Neal added a comment — 7th March 2012, 23:00:13:

Signed up just to vote for this issue, I'm using NHibernate 3.1. My use case seems to fall within the unlucky trifecta of invalid foreign keys -> eager fetching -> read-only database.

The NHibernate devs have gone to such great lengths to account for legacy databases, it seems a shame to have this issue as a stumbling block. Clearing the invalid foreign key (or indeed any data) is rarely an option with legacy databases. If it were then no-one would bother to do so and simply migrate the legacy data to a better schema.

In case this bug is never fixed, my workaround is to remove all associations to the infected table and work directly with SQL queries where necessary. YMMV.

---

Cole Werner added a comment — 8th March 2012, 3:07:07:

I am in a situation where I have several high traffic tables that have nullable foreign keys. I use the not-found = ignore mapping so that I don't get the exceptions thrown when the nullable many-to-one is referenced. Doing this unfortunately causes a query to be performed on each many-to-one that is mapped like this. I have one table right now that has 4 of these nullable foreign keys. So it does 4 queries on each row that I retrieve.

This is becoming an increasing problem for me.

---

Sÿl added a comment — 29th March 2012, 13:55:45:

Same here, just signed it to vote for this, issue. I'm using nhibernate 3.2 I'm mapping an odd db in which database is inconsistent.
Would be great if it could fixed soon

---

Taavi Kõosaar added a comment — 4th May 2012, 16:16:41:

A temporary workaround for guys with legacy database issues and cant fix the schema always (im in the same pain).
The Nullable Tuplizer approach works nicely, but needs some maintenance.

http://nhforge.org/blogs/nhibernate/archive/2011/01/28/how-to-use-0-instead-of-null-for-foreign-keys.aspx

Use that approach instead of not-found="ignore". In my case i maintain a list of bad reference cases such as where in database row value can be 0 or-1 to indicate that there is no reference (i.e. supposed to be NULL value in row).

---

Johan Haest added a comment — 14th February 2013, 11:41:29:

Taavi, this is true when you are lazy loading, but what if you are eagerly fetching the row? I can't get any work around for this. This is creating so many queries.

---

Mark Seefeldt added a comment — 22nd March 2013, 17:04:06:

Posted a one-to-one scenario on SO. Hoping someone who knows a workaround has some ideas. http://stackoverflow.com/questions/15575378/mapping-an-extension-table-that-might-not-have-a-row