nhibernate-issues

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

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

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

[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_14780 ]
     
Ayende Rahien commented on NH-860:
----------------------------------

Any particular reason you can just do:

select count(*) from (HQL Query) ??

> 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

[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_14781 ]
     
Luis Ferreira commented on NH-860:
----------------------------------

What you're saying is doing something like

            Dim q2 As IQuery = Session.CreateQuery("select count(*) from (" & q1.QueryString & ")")

But how can I pass the parameter list?

            q2.SetParameterList(????? how can I set them ?????)

As far as I can tell I have access to q1's named parameter NAMES, not the values.

> 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

[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_14790 ]
     
Peter Smulovics commented on NH-860:
------------------------------------

Try the Multiquery feature, there are some examples on using this:

1195    IMultiQuery multiQuery = s.CreateMultiQuery()
1196        .Add(s.CreateQuery("from Item i where i.Id > ?")
1197                .SetInt32(0, 50).SetFirstResult(10))
1198        .Add(s.CreateQuery("select count(*) from Item i where i.Id > ?")
1199                .SetInt32(0, 50));
1200    IList results = multiQuery.List();
1201    IList items = (IList)results[0];
1202    long count = (long)((IList)results[1])[0];

or with named params:

1210    IList results = s.CreateMultiQuery()
1211        .Add(s.CreateQuery("from Item i where i.Id > :id")
1212            .SetFirstResult(10) )
1213        .Add("select count(*) from Item i where i.Id > :id")
1214        .SetInt32("id", 50)
1215        .List();
1216    IList items = (IList)results[0];
1217    long count = (long)((IList)results[1])[0];


> 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

[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_14791 ]
     
Fabio Maulo commented on NH-860:
--------------------------------

You can do something like:
select wv.PlannedGroup.Group, count(wv.Id) 
from AbstractSiteAddress asa, WorkingVisit wv 
inner join wv.PlannedGroup pg
inner join wv.Site st
group by wv.PlannedGroup.Group 
where (st.SiteAddress.id= asa.Id) and
(asa.TerritorySymbol = :lv1Val) and 
(asa.Municipality = :lv2Val)

To set parameter use
q.SetParameter("lv1Val", theValue); // theValue is a object instance
q.SetParameter("lv2Val", theOtherValue);

The method SetParameterList is for a clause like:
where asa.TerritorySymbol IN (:thisIsTheParameterList)

The query that you can see above (the first one) return a list of list tuple.
The first value of tuple is the value of property wv.PlannedGroup.Group and the second is an Int64 that represent count(wv.Id).

An HQL like
select count(wv.Id) from AClass ac where ac.AProperty = :myParam
return a list with only one element of type Int64.

For the first query, if you need a list of your own object type you can use your own NHibernate.Transform.IResultTransformer.

In general all you can do using criteria you can do the same, or better, using query.

I think this is not an issue.


> 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

[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

[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_14812 ]
     
Christian Bauer commented on NH-860:
------------------------------------

If NHibernate can use cursors (Hibernate can), the solution is already there:

int count = myQuery.scroll().last();
List result = myQuery.list();


> 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

[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_14813 ]
     
Sergey Koshcheyev commented on NH-860:
--------------------------------------

NHibernate can't use cursors, they are not supported by ADO.NET.

> 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

[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_14814 ]
     
Luis Ferreira commented on NH-860:
----------------------------------

Why cursors? We don't want to be executing the original query and waste all that bandwidth to bring the results.

Reading http://www.hibernate.org/314.html, I see why they wanted to use cursors. It was found that changing the SQL's "select [...] from [...]" to "select count (*) from [...]" was rather hard, and it is. UNLESS you subquery, doing "select count(*) from (select [...] from [...])" instead. Only issue I see: does the database you're using support subqueries? That's why I mentioned a dialect 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

[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_14815 ]
     
Christian Bauer commented on NH-860:
------------------------------------

My cursor solution is going to perform much better than this subquery.


> 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

[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_14816 ]
     
Christian Bauer commented on NH-860:
------------------------------------

By the way, the whole point of the cursor is to _NOT_ retrieve the data into memory, but only the location of the cursor when it is positioned on the last row of the result. 

> 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

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

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

     [ http://jira.nhibernate.org/browse/NH-860?page=all ]

Sergey Koshcheyev updated NH-860:
---------------------------------

    Fix Version: LATER

> 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
>      Fix For: LATER

>
> 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

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

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

    [ http://jira.nhibernate.org/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_15959 ] 

Fabio Maulo commented on NH-860:
--------------------------------

Probably we can implement a solution but is not a guarantee that it can work for any kind of queries.

Your "original" query can include <group by> <order by> and so on, we can do something to remove it before create the "count query".
If your query use some other clause (in a sub-sub-query) not accepted by the clause "count" we can't do something.

For this kind of issue I prefer to use named queries that are optimized to do the right work (the count is, normally, more simple than the original select).
The two queries are named, for example:
MyOriginalQuery
MyOriginalQuery.Count

Then I use the two query separately.

In http://unhaddins.googlecode.com/  you can see this solution for pagination.

Any way for a "partial" solution we can think a specific NH solution (I'm not secure that is a good idea)




> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://jira.nhibernate.org/browse/NH-860
>             Project: NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Priority: Major
>             Fix For: LATER
>
>
> 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

        

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

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

    [ http://jira.nhibernate.org/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_15967 ] 

Luis Ferreira commented on NH-860:
----------------------------------

If it works in 90% of the cases (AND you know in advance which cases work) then it's 90% of the work saved.

Universal solutions are hard to find. I'd say it's worth it. 

> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://jira.nhibernate.org/browse/NH-860
>             Project: NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Priority: Major
>             Fix For: LATER
>
>
> 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

        

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

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

    [ http://jira.nhibernate.org/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_15968 ] 

Ayende Rahien commented on NH-860:
----------------------------------

I think that this violate the principal of the least surprise, because if suddenly breaks on you if you are extending stuff.

> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://jira.nhibernate.org/browse/NH-860
>             Project: NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Priority: Major
>             Fix For: LATER
>
>
> 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

        

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

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

    [ http://jira.nhibernate.org/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_15972 ] 

Fabio Maulo commented on NH-860:
--------------------------------

You are right Ayende... 
That is the issue for my: "I'm not secure that is a good idea"

but have a TransformToRowCount for HQL queries is very interesting even if it can be throw and exception in some case of use... and more interesting now that we have QueryPlan because both queries can be parsed and cached together at sessionFactory build.

If Sergey, and others developers, approve I can work on this issue.

Probably we need to change:
IQueryTranslator
IQuery
xsd for namedQuery (to enable the parse for count)


> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://jira.nhibernate.org/browse/NH-860
>             Project: NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Priority: Major
>             Fix For: LATER
>
>
> 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

        

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

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

    [ http://jira.nhibernate.org/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_15974 ] 

Ayende Rahien commented on NH-860:
----------------------------------

Fabio,
I am all for being to do modifications on the queries, but doesn't this require the AST to be in place?

> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://jira.nhibernate.org/browse/NH-860
>             Project: NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Priority: Major
>             Fix For: LATER
>
>
> 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

        

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

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

    [ http://jira.nhibernate.org/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_15975 ] 

Fabio Maulo commented on NH-860:
--------------------------------

For this issue have the AST is no necessary. We can implement this feature using Classic queryTranslator too.

> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://jira.nhibernate.org/browse/NH-860
>             Project: NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Priority: Major
>             Fix For: LATER
>
>
> 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

        

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

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

    [ http://jira.nhibernate.org/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_16111 ] 

Karl Chu commented on NH-860:
-----------------------------

I don't disagree that it would be a nice feature in principle.  However, I am not entirely convinced that it is a _need_ to have an IQuery.Count() method: Once one has a SQL/HQL written, it is in many cases trivial to translate it to one that counts the number of rows that would be returned.  I don't feel there is enough benefit for the amount of effort required to implement this.

> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://jira.nhibernate.org/browse/NH-860
>             Project: NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Priority: Major
>             Fix For: LATER
>
>
> 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

        

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

[NHibernate J. <nh...@gm...>]

     [ http://nhjira.koah.net/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ayende Rahien resolved NH-860.
------------------------------

      Assignee: Ayende Rahien
    Resolution: Won't Fix

We have other options for handling this issue

> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://nhjira.koah.net/browse/NH-860
>             Project:  NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Assignee: Ayende Rahien
>            Priority: Major
>
> 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://nhjira.koah.net/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[nhibernate-issues] [NHJIRA] Closed: (NH-860) Performant count of HQL query

[Fabio M. (JIRA) <nh...@gm...>]

     [ http://216.121.112.228/browse/NH-860?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Fabio Maulo closed NH-860.
--------------------------


> Performant count of HQL query
> -----------------------------
>
>                 Key: NH-860
>                 URL: http://216.121.112.228/browse/NH-860
>             Project:  NHibernate
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 1.2.0.Beta3
>            Reporter: Luis Ferreira
>            Assignee: Ayende Rahien
>            Priority: Major
>
> 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://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira