1

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.

pikimota
  • 241
  • 1
  • 4
  • 15
  • Look at the generate queries if you didn't do that already, if something is 4 times faster there is definitely more going on than java overhead. And probably most important: how did you measure that it's 4 times faster? – Shadov Feb 26 '18 at 11:13
  • 1
    I looked at response time in chrome Developer tools: named query takes about 20 second, criteria builder 4 seconds – pikimota Feb 26 '18 at 11:22
  • I thought it was like 10ms vs 40ms, but this difference is huge, definitely look at the generated queries. https://stackoverflow.com/questions/2536829/hibernate-show-real-sql – Shadov Feb 26 '18 at 11:28

0 Answers0