{"id":264,"date":"2009-12-08T18:34:08","date_gmt":"2009-12-08T16:34:08","guid":{"rendered":"http:\/\/www.limilabs.com\/blog\/?p=264"},"modified":"2016-09-09T10:31:11","modified_gmt":"2016-09-09T08:31:11","slug":"nhibernate-ignores-setmaxresults-in-sql","status":"publish","type":"post","link":"https:\/\/www.limilabs.com\/blog\/nhibernate-ignores-setmaxresults-in-sql","title":{"rendered":"NHibernate ignores SetMaxResults in SQL"},"content":{"rendered":"

\"nhibernate\"Recently me and my team realized that several our queries, that include maximum limit on the recordset, are limited in the web application and not<\/strong> on the Oracle side.<\/p>\n

First thought was that somebody simply forgot to add an invocation of SetMaxResults method, but our repository code was very obvious about it:<\/p>\n

\r\nIQuery query = session.CreateQuery(hqlQuery);\r\nquery.SetMaxResults(100);\r\n<\/pre>\n
After some investigation it turned out that NHiberante is not able to use ROWNUM on the SQL side, when there is a fetch join on the collection.<\/p>\n
\"EmployeeDatas\"<\/p>\n
This is HQL query we use:<\/p>\n
\r\nselect\r\n    user\r\nfrom\r\n    User user\r\n    \/* next line causes no rownum check *\/\r\n    inner join fetch user.EmployeeDatas EmployeeData\r\n    left join fetch EmployeeData.Country country\r\n<\/pre>\n
And some NHibernate code:<\/p>\n
\r\npublic IList List(ISessionImplementor session, QueryParameters queryParameters)\r\n{\r\n\t\/\/ ...\r\n\tif ( hasLimit && ContainsCollectionFetches )\r\n\t{\r\n\t\tlog.Warn( "firstResult\/maxResults specified with collection fetch; applying in memory!" );\r\n<\/pre>\n
NHibernate simply ignores the limit when executing the query and applies it on the result.<\/p>\n
At some point I thought that it is not possible to even create such SQL query, but I was proven to be wrong. The query would look similar to this proof-of-concept:<\/p>\n
SELECT
\n\t   *
\nFROM
\n(
\n \tSELECT
\n\t\t   *
\n    FROM
\n\t\t TUSER u
\n\tWHERE
\n\t\t  \/* Add conditions here: *\/
\n\t\t  EXISTS(…)
\n\t\t  \/* Here is DB-side limit: *\/
\n \t\t  AND ROWNUM <= 14\n) AS u\n\/* And now fetch all collections needed *\/\nINNER JOIN TEMPLOYEEDATA d ON u.ID = d.USERID\n[\/sql]\n\nSo it's clear that it is possible to improve this in NHibernate.\n\nIn our case change was simple:\nWe changed one-to-many<\/em> mapping to one-to-one<\/em>.<\/p>\n
But if you are not able to do this because of your requirements I would use custom SQL view and mapped it in NHibernate. The second idea would be to use NHibernate detached criteria and sub-queries.<\/p>\n
Finally the unit test that we used to check if the limit is done on SQL database side:<\/p>\n
\r\n[Test]\r\npublic void FindUsersBy_QueryWithLimit_LimitsOnSQLSide()\r\n{\r\n    using (LogChecker logChecker = new LogChecker("NHibernate", Level.Warn))\r\n    {\r\n        IList<user> users = this._context.ExecuteInTransaction(() =>\r\n        {\r\n            UserQuery q = new UserQuery();\r\n            q.UserId = "ILMT";\r\n            q.MaxNumberOfRecords = 14;\r\n            return this._context.UserRepository.FindUsersBy(q);\r\n        });\r\n        Assert.IsEmpty(logChecker.Messages);\r\n        Assert.AreEqual(14, users.Count);\r\n    }\r\n}\r\n<\/pre>\n
Here you can see the implementation of the LogChecker log4net log reader<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Recently me and my team realized that several our queries, that include maximum limit on the recordset, are limited in the web application and not on the Oracle side. First thought was that somebody simply forgot to add an invocation of SetMaxResults method, but our repository code was very obvious about it: IQuery query = […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[38],"class_list":["post-264","post","type-post","status-publish","format-standard","hentry","category-programming","tag-nhibernate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts\/264"}],"collection":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/comments?post=264"}],"version-history":[{"count":4,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts\/264\/revisions"}],"predecessor-version":[{"id":5059,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/posts\/264\/revisions\/5059"}],"wp:attachment":[{"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/media?parent=264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/categories?post=264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.limilabs.com\/blog\/wp-json\/wp\/v2\/tags?post=264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}