I have two tables that looks like:
Table_0
ID   Description
5    description5
6    description6
7    description7
8    description8
Table_1
ID Table_0_ID Status
1    5       LOADED
2    6       LOADED
3    7       LOADED 
4    7       LOADED
Table_2
 ID    Table_1_ID
    1      1
    2      2
    3      3
    4      4
my expected result when I invoke findAll query is that it eliminates duplicates so result will return (for both tables) three rows (table_1 id from 1 to 3).
I wrote both named query and criteria builder query but the last one seems to be by 4 times faster. I wonder why. Am I making a mistake?
Here is the code:
query = "SELECT OBJECT(sb) FROM Table_2 sb WHERE sb.Table_1.id IN (SELECT MAX(maxsr.id) FROM Table_1 maxsr WHERE maxsr.status = LOADED GROUP BY maxsr.Table_0.id)")
criteria builder:
final EntityManager em = getEntityManager();
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Table_2> criteriaQuery = cb.createQuery(Table_2.class);
final Root<Table_2> root = criteriaQuery.from(Table_2.class);
Predicate p = getPredicateOnList(data, cb, root, gateSession);
if (p != null) {
    criteriaQuery.where(p);
}
final Query q = getEntityManager().createQuery(criteriaQuery);
return q.getResultList();
}
method getPredicateOnList
 private Predicate getPredicateOnList(final PaginationData data, final CriteriaBuilder cb, final Root<Table_2> root) {
        final Join<Table_2, Table_1> readingJoin = root.join("Table_1");
        boolean filterUnloaded = false;
        boolean selectMax = true;
        for (KeyValuePair pair : data.getRequestParams()) {
            if (pair.getKey().equalsIgnoreCase("filterUnloaded")) {
                filterUnloaded = ParsingUtils.parseBoolean(pair.getValue(), false);
            }
            if (pair.getKey().equalsIgnoreCase("selectMax")) {
                selectMax = ParsingUtils.parseBoolean(pair.getValue(), true);
            }
        }
        Predicate predicate = null;
        if (selectMax) {
            List<Long> maxReadingIds = getMaxReadingIds(gateSession.getId(), filterUnloaded);
            if (maxReadingIds == null || maxReadingIds.isEmpty()) {
                //do nothing
            } else {
                predicate = readingJoin.get("id").in(maxReadingIds);
            }
        }
        return predicate;
    }
method getMaxReadingIds
 private List<Long> getMaxReadingIds(Long sessionId, boolean filterUnloaded) {
        final CriteriaBuilder cb = em.getCriteriaBuilder();
        final CriteriaQuery<Long> maxReadingIdQuery = cb.createQuery(Long.class);
        final Root<Table_1> Table_1Root = maxReadingIdQuery.from(Table_1.class);
        final Path<Long> idGet = Table_1Root.get("id");
        maxReadingIdQuery.select(cb.greatest(idGet));
        final Join<Table_1, Table_0> join = Table_1Root.join("Table_0");
        maxReadingIdQuery.groupBy(join.get("id"));
        Predicate predicate = null;
        if (filterUnloaded) {
            predicate = cb.equal(Table_1Root.get("status"), LOADED);
        }
        //omiss sessionId parameter
        if (predicate != null) {
            maxReadingIdQuery.where(predicate);
        }
        final Query q = getEntityManager().createQuery(maxReadingIdQuery);
        return q.getResultList();
    }
(I made some semplification, if code is not well defined please tell me and I supply more information) Both results are correct but criteria builder is faster.
