[nhibernate-issues] [JIRA] Commented: (NH-860) Performant count of HQL query

[NHibernate J. <mik...@us...>]

     [ http://jira.nhibernate.org/browse/NH-860?page=comments#action_14811 ]
     
Luis Ferreira commented on NH-860:
----------------------------------

I wasn't clear enough.

What we are proposing is for NHibernate to provide us with an ability to GENERICALLY solve the pagination problem for HQL queries. A critical requisite is that you should not have to code anything specific to get the count, once you had coded the original query. Pagination is almost an *aspect* in your application, and you don't want to be solving the same thing every time for any specific query. 

If, given ANY query prepared for execution, with all parameters set (named or unnamed), NHibernate could provide you with a low cost query to count(*) the results, wouldn't it be really great? That would be much better than our current (sloppy) implementation (please excuse the VB - I didn't choose the implementation language!):

        Public Shared Function CalculateQueryRowCount(ByVal Session As ISession, ByVal Query As IQuery, ByVal FirstResultToPreserve As Integer, ByVal MaxResultsToPreserve As Integer) As Integer
            Query.SetFirstResult(0)
            Query.SetMaxResults(-1)

            Dim nCount As Integer
            nCount = Query.List().Count

            Query.SetFirstResult(FirstResultToPreserve)
            Query.SetMaxResults(MaxResultsToPreserve)

            Return nCount
        End Function

I'm thinking in a Query.Count method, instead of Query.List().Count. From a distance, and having partially implemented a persistence framework, and abandoning it for NHibernate, bless the day, I risk venturing that the new Query.Count method would just have to generate the SQL just like Query.List() does, then wrap a "select count(*) from ( <generated sql query> )" around it, and setting the same parameters for this resulting query as Query.List() would.

Probably this could only be done in some dialects (i.e. those that support subquerying). But I may be horribly wrong and it could be much more complicated. But if it isn't, it would be a very elegant and performant solution for a common problem.

> Performant count of HQL query
> -----------------------------
>
>          Key: NH-860
>          URL: http://jira.nhibernate.org/browse/NH-860
>      Project: NHibernate
>         Type: Improvement
>   Components: Core
>     Versions: 1.2.0.Beta3
>     Reporter: Luis Ferreira

>
> We are implementing pagination in our Web Project and we'd like to have a way of calculating the count(*) for each of the HQL queries we have already implemented *without writing specific queries for that* AND *without returning the results as a list* and then getting its count property.
> A member of the team has attempted, with some success, to use NHibernate to
> generate the SQL for our HQL queries and then wrapping it with a select
> count(*) from (<generated SQL>). But to do that we have had to use
> Reflection to access protected methods, and filling query parameters for
> parametrized queries is still a problem. To really tackle the issue it would
> be necessary to have some changes in NHibernate, namely public exposure in
> IQuery of methods in AbstractQueryImpl / QueryImpl regarding access to named
> parameters names and values.
> Of course a really great solution would be for NHibernate to provide that
> count(*) funcionality (in a way similar to what has been done with
> projections for ICriteria? haven't used that much, we've got really hairy
> queries to write). It seems to be an issue for a lot of people. For
> databases that support subquerying it wouldn't be too difficult to
> implement I guess, but maybe the team doesn't want to impose that kind of
> restrictions.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://jira.nhibernate.org/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira