Is this problem solvable?
---
package debug; import static java.lang.System.out; import static java.time.Duration.between; import static java.time.Instant.now; import static javax.persistence.Persistence.createEntityManagerFactory; import java.io.File; import java.time.Duration; import java.time.Instant; import java.util.List; import java.util.TreeMap; import javax.persistence.Embeddable; import javax.persistence.EmbeddedId; import javax.persistence.Entity; import javax.persistence.EntityManager; import javax.persistence.TypedQuery; /** * @author Stanislav Jakuschev 28.02.2023 * * EntityManager.find(entityClass, primaryKey) is slow when accessing * non-existent IDs. * * Select queries process non-existent IDs fastest. * */ public class OdbEmFindById { public static final int EVALUATION_LIMIT = 1000000; @Entity public static class X { @EmbeddedId public XId id; public X() { } public X(XId id) { this.id = id; } public X(int a, int b, int c) { id = new XId(a, b, c); } @Override public String toString() { return "X [id=" + id + "]"; } } @Embeddable public static class XId { public int a, b, c; public XId() { } public XId(int a, int b, int c) { set(a, b, c); } public void set(int a, int b, int c) { this.a = a; this.b = b; this.c = c; } @Override public String toString() { return "XId [a=" + a + ", b=" + b + ", c=" + c + "]"; } } public static void clear(EntityManager em) { Instant t = now(); em.clear(); out.println("clear\t" + between(t, now()).toString()); } public static void write(EntityManager em, TreeMap<Duration, String> order) { Instant t, t2; out.println("\nWRITE\n"); t = t2 = now(); em.getTransaction().begin(); int i; for (i = 1; i < EVALUATION_LIMIT; i++) { em.persist(new X(i, i, i)); if ((i % 100000) == 0) { em.getTransaction().commit(); out.println("write\t" + i + "\t" + between(t2, now()).toString()); clear(em); t2 = now(); em.getTransaction().begin(); } } em.getTransaction().commit(); Duration d = between(t, now()); String s = "write\t" + (i - 1) + "\t"; order.put(d, s); out.println(" " + d + "\t" + s); } public static void iterate(EntityManager em, boolean existing, String mode, TreeMap<Duration, String> order) { Instant t = now(); X x; XId xid = new XId(0, 0, 0); int i, found = 0; for (i = existing ? 1 : EVALUATION_LIMIT; i < (existing ? EVALUATION_LIMIT : 2 * EVALUATION_LIMIT); i++) { xid.set(i, i, i); x = em.find(X.class, xid); if (x != null) found++; } Duration d = between(t, now()); String s = "read\t" + found + "/" + ((existing ? i : i / 2) - 1) + "\t" + mode; order.put(d, s); out.println(" " + d + "\t" + s); } public static void select(EntityManager em, TypedQuery<X> q, boolean existing, String mode, TreeMap<Duration, String> order) { Instant t = now(); q.setParameter(1, existing ? 1 : EVALUATION_LIMIT); q.setParameter(2, existing ? EVALUATION_LIMIT : 2 * EVALUATION_LIMIT); List<X> l = q.getResultList(); order.put(between(t, now()), "read\t" + l.size() + "/" + (EVALUATION_LIMIT - 1) + "\t" + mode); Duration d = between(t, now()); String s = "read\t" + l.size() + "/" + (EVALUATION_LIMIT - 1) + "\t" + mode; order.put(d, s); out.println(" " + d + "\t" + s); } public static void warmup(EntityManager em, TypedQuery<X> q, TreeMap<Duration, String> order) { Instant t = now(); out.println("\nWARMUP\n"); clear(em); iterate(em, false, "cold\titerate\t\tuncached\tunexisting", order); iterate(em, true, "cold\titerate\t\tuncached\texisting", order); clear(em); select(em, q, true, "warm\tselect all\tuncached\texisting", order); select(em, q, true, "warm\tselect all\tcached\t\texisting", order); out.println("warmup\t" + between(t, now()).toString()); } public static void selectFirst(EntityManager em, TypedQuery<X> q, TreeMap<Duration, String> order) { out.println("\nSELECT FIRST\n"); clear(em); select(em, q, false, "hot\tselect first\tuncached\tunexisting\t<- 3", order); select(em, q, true, "hot\tselect first\tuncached\texisting\t<- 8", order); select(em, q, false, "hot\tselect first\tcached\t\tunexisting\t<- 1", order); select(em, q, true, "hot\tselect first\tcached\t\texisting\t<- 6", order); } public static void selectDeep(EntityManager em, TypedQuery<X> q, TreeMap<Duration, String> order) { out.println("\nSELECT DEEP\n"); clear(em); select(em, q, false, "hot\tselect deep\tuncached\tunexisting\t<- 4", order); select(em, q, true, "hot\tselect deep\tuncached\texisting\t<- 7", order); select(em, q, false, "hot\tselect deep\tcached\t\tunexisting\t<- 2", order); select(em, q, true, "hot\tselect deep\tcached\t\texisting\t<- 5", order); } public static void iterate(EntityManager em, TreeMap<Duration, String> order) { out.println("\nITERATE\n"); clear(em); iterate(em, false, "hot\titerate\t\tuncached\tunexisting\t<- incomparable with 3, 4. Is that solvable?", order); iterate(em, true, "hot\titerate\t\tuncached\texisting\t<- comparably effective as 7, 8", order); iterate(em, false, "hot\titerate\t\tcached\t\tunexisting\t<- incomparable with 1, 2. Is that solvable?", order); iterate(em, true, "hot\titerate\t\tcached\t\texisting\t<- comparably effective as 5, 6", order); } public static void main(String[] args) { String dbName = OdbEmFindById.class.getSimpleName() + ".odb"; new File(dbName).delete(); new File(dbName + "$").delete(); EntityManager em = createEntityManagerFactory(dbName).createEntityManager(); TreeMap<Duration, String> order = new TreeMap<>(); write(em, order); warmup(em, em.createQuery("select x from X x", X.class), order); selectFirst(em, em.createQuery("select x from X x where x.id.a >= ?1 and x.id.a < ?2", X.class), order); selectDeep(em, em.createQuery( "select x from X x where x.id.a >= ?1 and x.id.a < ?2 and x.id.b >= ?1 and x.id.b < ?2 and x.id.c >= ?1 and x.id.c < ?2", X.class), order); iterate(em, order); out.println("\nORDERED\n"); for (Duration d : order.keySet()) out.println(" " + d + "\t" + order.get(d)); } }
---
11:00:49.520 [main] DEBUG org.jboss.logging - Logging Provider: org.jboss.logging.Log4j2LoggerProvider 11:00:49.561 [main] INFO org.hibernate.jpa.boot.internal.PersistenceXmlParser - HHH000318: Could not find any META-INF/persistence.xml file in the classpath 11:00:49.562 [main] DEBUG org.hibernate.jpa.HibernatePersistenceProvider - Located and parsed 0 persistence units; checking each 11:00:49.562 [main] DEBUG org.hibernate.jpa.HibernatePersistenceProvider - Found no matching persistence units WRITE write 100000 PT0.468S clear PT0.024S write 200000 PT0.226S clear PT0.012S write 300000 PT0.18S clear PT0.014S write 400000 PT0.215S clear PT0.012S write 500000 PT0.142S clear PT0.013S write 600000 PT0.176S clear PT0.014S write 700000 PT0.148S clear PT0.012S write 800000 PT0.246S clear PT0.013S write 900000 PT0.143S clear PT0.012S PT2.217S write 999999 WARMUP clear PT0.013S PT4.199S read 0 cold iterate uncached unexisting PT3.495S read 999999 cold iterate uncached existing clear PT0.301S PT3.369S read 999999 warm select all uncached existing PT0.266S read 999999 warm select all cached existing warmup PT11.647S SELECT FIRST clear PT0.204S PT0.01S read 0 hot select first uncached unexisting <- 3 PT1.514S read 999999 hot select first uncached existing <- 8 PT0S read 0 hot select first cached unexisting <- 1 PT0.331S read 999999 hot select first cached existing <- 6 SELECT DEEP clear PT0.211S PT0.017S read 0 hot select deep uncached unexisting <- 4 PT2.682S read 999999 hot select deep uncached existing <- 7 PT0.001S read 0 hot select deep cached unexisting <- 2 PT0.286S read 999999 hot select deep cached existing <- 5 ITERATE clear PT0.185S PT3.805S read 0 hot iterate uncached unexisting <- incomparable with 3, 4. Is that solvable? PT2.269S read 999999 hot iterate uncached existing <- comparably effective as 7, 8 PT5.983S read 0 hot iterate cached unexisting <- incomparable with 1, 2. Is that solvable? PT0.224S read 999999 hot iterate cached existing <- comparably effective as 5, 6 ORDERED PT0S read 0 hot select first cached unexisting <- 1 PT0.001S read 0 hot select deep cached unexisting <- 2 PT0.01S read 0 hot select first uncached unexisting <- 3 PT0.017S read 0 hot select deep uncached unexisting <- 4 PT0.224S read 999999 hot iterate cached existing <- comparably effective as 5, 6 PT0.266S read 999999 warm select all cached existing PT0.286S read 999999 hot select deep cached existing <- 5 PT0.33S read 999999 hot select first cached existing <- 6 PT0.331S read 999999 hot select first cached existing <- 6 PT1.514S read 999999 hot select first uncached existing <- 8 PT2.217S write 999999 PT2.269S read 999999 hot iterate uncached existing <- comparably effective as 7, 8 PT2.682S read 999999 hot select deep uncached existing <- 7 PT3.369S read 999999 warm select all uncached existing PT3.495S read 999999 cold iterate uncached existing PT3.805S read 0 hot iterate uncached unexisting <- incomparable with 3, 4. Is that solvable? PT4.199S read 0 cold iterate uncached unexisting PT5.983S read 0 hot iterate cached unexisting <- incomparable with 1, 2. Is that solvable? ---
thank you