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 -- You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/dspace-tech. For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: Digital signature
