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