FWIW, Joe added a setRestriction() method on Criteria objects - you can restrict which of
the 2 queries you want to invoke. So, for example if you only want a count but don't
care about the actual data, you can restrict to run only the count query.
I don't think this is directly related to this thread since this isn't criteria
related, but I wanted to bring it up in case you are using criteria and want to limit the
queries you want to invoke. See the Criteria Restriction javadoc/code for more.
----- Original Message -----
Hi,
in many places we have something like (query behind the AD portlet):
Query queryCount = PersistenceUtility.createCountQuery(entityManager,
Resource.QUERY_FIND_QUEUED_PLATFORMS_BY_INVENTORY_STATUS);
Query query = PersistenceUtility.createQueryWithOrderBy(entityManager,
Resource.QUERY_FIND_QUEUED_PLATFORMS_BY_INVENTORY_STATUS, pc);
queryCount.setParameter("inventoryStatuses", statuses);
long count = (Long) queryCount.getSingleResult();
query.setParameter("inventoryStatuses", statuses);
List<Resource> results = query.getResultList();
here we fire 2 queries:
- first counting total number of items that may match
- second getting a subset of those matching items.
This may turn out problematic
- we always do two roundtrips to the DB
- we always fire two slightly different DB queries
as they are in the same transaction and in direct succession, the
relevant tables
may still be in memory.
I propose for queries like the above, where the default result is 0
items, to check for 0 results after the first query
and only to fire the 2nd one if there are items matching.
The counterpart to this are queries with an unlimited PC - meaning
that everything
should be returned. Here the count query is not needed and should not
be fired.
Hand stopped for the AD portlet, this simple check for 0 cuts off
~300% time used and quite
some cpu load on the DB (the above query is entirely CPU bound).
--
Reg. Adresse: Red Hat GmbH, Otto-Hahn-Strasse 20, 85609 Dornach bei
München
Handelsregister: Amtsgericht München HRB 153243
Geschaeftsführer: Brendan Lane, Charlie Peters, Michael Cunningham,
Charles Cachera
_______________________________________________
rhq-devel mailing list
rhq-devel(a)lists.fedorahosted.org
https://fedorahosted.org/mailman/listinfo/rhq-devel