Select count(*) in hibernate

[Terry Brady]

I would like to convert a hibernate query (HQL) into a query that performs a select count(*) ...

I have the following code in a pull request:

https://github.com/terrywbrady/DSpace/blob/ds-2583v7/dspace-api/src/main/java/org/dspace/content/dao/impl/ItemDAOImpl.java#L135-L195

This code constructs a DSpace metadata query.  The query might return a small number of objects or a large number of objects.  

In order to manage the number of results that are returned, I set an offset and a limit to the query and paginate the results.

https://github.com/terrywbrady/DSpace/blob/ds-2583v7/dspace-api/src/main/java/org/dspace/content/dao/impl/ItemDAOImpl.java#L136-L137

It would be useful to compute a count of total results.  Is there a proper way to construct a hibernate query (HQL) that will return a count of total results in an efficient manner.

I would like to reuse as much of the HQL as possible without triggering the construction of results when I am only seeking a count.

Terry

[Mark H. Wood]

On Tue, Dec 15, 2015 at 07:36:57AM -0800, Terry Brady wrote:
> I would like to convert a hibernate query (HQL) into a query that performs
> a select count(*) ...
>
> I have the following code in a pull request:
>
> https://github.com/terrywbrady/DSpace/blob/ds-2583v7/dspace-api/src/main/java/org/dspace/content/dao/impl/ItemDAOImpl.java#L135-L195
>
> This code constructs a DSpace metadata query. The query might return a
> small number of objects or a large number of objects.
>
> In order to manage the number of results that are returned, I set an offset
> and a limit to the query and paginate the results.
>
> https://github.com/terrywbrady/DSpace/blob/ds-2583v7/dspace-api/src/main/java/org/dspace/content/dao/impl/ItemDAOImpl.java#L136-L137
>
> It would be useful to compute a count of total results. Is there a proper
> way to construct a hibernate query (HQL) that will return a count of total
> results in an efficient manner.

If you want to do it in HQL, it's pretty much like SQL: SELECT
COUNT(*) FROM...

How efficient COUNT is will depend on how your query aligns with the
available indexes, I think. A good DBMS will not fetch rows if it can
find a cheaper way (such as rummaging in the indexes). Get your DBMS
to give you the query plan it creates for your COUNT query, to see how
costly it is likely to be and whether there is anything reasonable to
be done about that.

But you're using the Criteria style in the referenced code, and you
may want to see if you can just build the query that way, get the
count, and then set your window to start fetching samples. I have not
done this, but it's discussed on StackOverflow:

http://stackoverflow.com/questions/2160259/count-in-hibernate-criteria

You should also consider transaction boundaries (and I am of no
particular help there!) and their effect on the eventual accuracy of
the initial count. You may have to arrange transactions carefully or
accept that the count is merely an estimate.

--
Mark H. Wood
Lead Technology Analyst

University Library
Indiana University - Purdue University Indianapolis
755 W. Michigan Street
Indianapolis, IN 46202
317-274-0749
www.ulib.iupui.edu