Menu
▾
▴
nhibernate-issues
|
From: Gabe M. (JIRA) <nh...@gm...> - 2010-08-19 17:15:00
|
Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
----------------------------------------------------------------------------------------------
Key: NH-2296
URL: http://216.121.112.228/browse/NH-2296
Project: NHibernate
Issue Type: Bug
Components: DataProviders / Dialects
Affects Versions: 3.0.0.Alpha2
Reporter: Gabe Moothart
Priority: Critical
Attachments: nhib_test.zip
Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
A simple example, from the attached reproduction:
var qry = sess.CreateQuery("select o from Order o")
.SetMaxResults(2)
.List<Order>();
// trigger lazy-loading of products, using subselect fetch.
string sr = orders[0].Products[0].StatusReason;
Generates the following sql:
NHibernate: select TOP (@p0) [...]
from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
NHibernate: SELECT [...] FROM [Product] products0_
WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: Gabe M. (JIRA) <nh...@gm...> - 2010-08-19 17:28:05
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19683#action_19683 ]
Gabe Moothart commented on NH-2296:
-----------------------------------
I forgot to include instructions for running the test-case:
1. Assuming you have an instance of SqlServer2008 express installed, just create a new database called "test_db"
2. compile and run the test app. This will create the right tables. Ignore the exception you'll get on line 56.
3. Add data to test_db using the data.sql script
4. run again and observe results.
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Attachments: nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2010-08-21 12:01:41
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19691#action_19691 ]
Fabio Maulo commented on NH-2296:
---------------------------------
Thanks for the instructions to run the test.
Can you have a look to the "Welcome" message of this site ?
http://216.121.112.228/secure/Dashboard.jspa
Thanks.
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Attachments: nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2010-08-23 14:52:11
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Maughan updated NH-2296:
-------------------------------
Attachment: NH2296.Test.patch
I've attached a proper test. Seems to reproduce the problem described, but I haven't looked into it any further than that.
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Attachments: NH2296.Test.patch, nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2010-08-27 15:08:58
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19727#action_19727 ]
Julian Maughan commented on NH-2296:
------------------------------------
Related to http://opensource.atlassian.com/projects/hibernate/browse/HHH-2666
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Attachments: NH2296.Test.patch, nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2010-09-27 12:10:01
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19938#action_19938 ]
Fabio Maulo commented on NH-2296:
---------------------------------
Gabe,
while we find a fix, is better if you use the batch-size for the collection of Order.Products.
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Attachments: NH2296.Test.patch, nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: AJCS (JIRA) <nh...@gm...> - 2010-10-09 00:37:43
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20004#action_20004 ]
AJCS commented on NH-2296:
--------------------------
Isn't this related to NH-1123, NH-1653 and NH-2215 ?
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Attachments: NH2296.Test.patch, nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2011-05-30 14:07:47
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21220#action_21220 ]
Fabio Maulo commented on NH-2296:
---------------------------------
The solution is in a TODO inside
QueryLoader.PrepareQueryCommand
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Attachments: NH2296.Test.patch, nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2011-06-18 13:00:04
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fabio Maulo resolved NH-2296.
-----------------------------
Resolution: Fixed
Fix Version/s: 3.2.0Beta2
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Fix For: 3.2.0Beta2
>
> Attachments: NH2296.Test.patch, nhib_test.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
|
From: AJCS (JIRA) <nh...@gm...> - 2011-06-25 03:44:42
|
[ http://216.121.112.228/browse/NH-2296?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
AJCS updated NH-2296:
---------------------
Attachment: SubselectFetchFixture.zip
It still fails if the main query has an Order clause. I'm attaching a test that I've added to the ones in SubselectFetchFixture.cs
> Subselect fetching strategy with a "SetMaxResults" query generates *extremely* inefficient sql
> ----------------------------------------------------------------------------------------------
>
> Key: NH-2296
> URL: http://216.121.112.228/browse/NH-2296
> Project: NHibernate
> Issue Type: Bug
> Components: DataProviders / Dialects
> Affects Versions: 3.0.0.Alpha2
> Reporter: Gabe Moothart
> Priority: Critical
> Fix For: 3.2.0Beta2
>
> Attachments: NH2296.Test.patch, nhib_test.zip, SubselectFetchFixture.zip
>
>
> Observed in NHibernate 3.0 Alpha 2, and also 2.1.2.GA, with the SqlServer2008 dialect.
> Assume you have a master table with a one-to-many association on a detail table, and that association has the subselect fetching strategy.
> If you issue a simple query against the master object using SetMaxResults to restrict the number of rows returned, and then access one of the detail objects (triggering the subselect fetch query), the subselect sql will not be generated with a "TOP". This results in a query that returns an unbounded number of unnecessary records!
> A simple example, from the attached reproduction:
> var qry = sess.CreateQuery("select o from Order o")
> .SetMaxResults(2)
> .List<Order>();
> // trigger lazy-loading of products, using subselect fetch.
> string sr = orders[0].Products[0].StatusReason;
> Generates the following sql:
> NHibernate: select TOP (@p0) [...]
> from [Order] order0_;@p0 = 2 [Type: Int32 (0)]
> NHibernate: SELECT [...] FROM [Product] products0_
> WHERE products0_.Order_id in (select order0_.Id from [Order] order0_)
> You can see that the subselect in the second query is unbounded and returns every Product attached to *any* order, not just the top two! I've attached a simple solution containing a reproducible test case. Run the console application and inspect the SQL that is output. You can clearly see what I am talking about.
> This makes subselect fetching practically useless for most scenarios. In my case it returns 20,000 rows when 20 or so would do. I consider it a critical issue.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X