Does prefetching some query results in Hibernate makes sense?

Question

While exploring my new project I have encountered some legacy code, which has the meaning of explicit prefetching some query results into Hibernate cache.

The task solved by that code is to select entity basing on some attribute value.

For example:

    SELECT c
    FROM MyEntity c
    LEFT OUTER JOIN c.relatedEntity relatedEntity
    WHERE c.field1 = :param1 AND relatedEntity.field1 = :param2 AND relatedEntity.field2 = :param3

The suggested improvement is to prefetch all the entities before running this query, i.e. run one of the following queries before the main one:

    SELECT c, relatedEntity FROM MyEntity c LEFT OUTER JOIN c.relatedEntity relatedEntity

or

    SELECT FROM MyEntity;
    SELECT FROM RelatedEntity;

What do you think about the optimizations of this kind? Can it be useful? If "yes", in which situations?

score 0 · Accepted Answer · edited May 23 '17 at 11:55

0

Sure it can be useful. If your ehcache and application code is configured properly, this is some kind of "cache warm-up". This will make your application benefit immediately from the second level cache and maybe even from the database cache, improving "first-time" performance.
We are actually doing a lot of cache warm-up in order to expose a freshly started host directly to traffic without having our users experience performance degradation due to cache initilization.

edited May 23 '17 at 11:55

Community

1
1

answered Apr 04 '13 at 14:02

skirsch

1,640
12
24

Thanks skirsch! But looks like your answer relates only to special case of redeploying your system. What about usual everyday operation - do we need this, if the system runs stably day after day? – KutaBeach Apr 04 '13 at 15:02
If with "usual everyday operation" you mean your servers are running days or weeks without being shut down and/or you are able to slowly route traffic to fresh-set-up hosts, cache warm-up looses it's usefulness. And after re-reading your question, I'm not sure if I got you right from the start. Are you saying, that these "warm-up" queries are executed (and its results being discarded) each time before the actual query is being made? – skirsch Apr 04 '13 at 15:12
Yes, exactly, skirsch, each time. This looks odd. But you understood my question right: its more wide, I wonder if the correct usage of this technique may help in some situation. – KutaBeach Apr 04 '13 at 15:58
Oh, that certainly is odd. I mean, this certainly fills the second level cache; and maybe (a really small maybe though) this even causes some "improvements" on the database side regarding caches, execution plans etc. But I can't imagine that all the fuzz plus the "real" query is faster than just running the "real query"; I would honestly be astonished if it's not notably slower. I wonder why it was built in, though. At some point it may have made sense. Measure & Change I'd suggest! – skirsch Apr 04 '13 at 16:30

Does prefetching some query results in Hibernate makes sense?

1 Answers1