[
https://issues.apache.org/jira/browse/OPENJPA-2920?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17866921#comment-17866921
]
Maxim Solodovnik commented on OPENJPA-2920:
-------------------------------------------
As far as I can see OpenJPA code works as designed. It was initially written to
use concatenation of {{OR A == B}} statemamnts instead of {{IN (....)}}
I'll try to rewrite it, but I'm not very optimistic so far :(
> CriteriaBuilder generating suboptimal code
> ------------------------------------------
>
> Key: OPENJPA-2920
> URL: https://issues.apache.org/jira/browse/OPENJPA-2920
> Project: OpenJPA
> Issue Type: Bug
> Components: criteria
> Affects Versions: 3.2.0
> Reporter: Rik Schaaf
> Priority: Major
>
> *Background:*
> I have the following entity:
> {code:java}
> package com.github.cc007.headsplugin.integration.database.entities;
> import lombok.AccessLevel;
> import lombok.Getter;
> import lombok.NoArgsConstructor;
> import lombok.Setter;
> import lombok.ToString;
> import javax.persistence.CascadeType;
> import javax.persistence.Column;
> import javax.persistence.Entity;
> import javax.persistence.FetchType;
> import javax.persistence.GeneratedValue;
> import javax.persistence.GenerationType;
> import javax.persistence.Id;
> import javax.persistence.Index;
> import javax.persistence.ManyToMany;
> import javax.persistence.Table;
> import javax.persistence.Version;
> import java.util.Collections;
> import java.util.HashSet;
> import java.util.Set;
> @Entity
> @Table(name = "heads",
> indexes = {
> @Index(name = "heads_headowner_index", columnList =
> "headOwner"),
> @Index(name = "heads_name_index", columnList = "name")
> }
> )
> @Getter
> @Setter
> @ToString
> @NoArgsConstructor
> public class HeadEntity {
> @Id
> @Column(name = "id", nullable = false)
> @GeneratedValue(strategy = GenerationType.AUTO)
> @Setter(AccessLevel.NONE)
> private long id;
> @Version
> @Column(name = "version", nullable = false)
> private long version;
> @Column(name = "headOwner", unique = true, nullable = false)
> private String headOwner;
> @Column(name = "name", nullable = false)
> private String name;
> @Column(name = "value", length = 1023, nullable = false)
> private String value;
> @ToString.Exclude
> @ManyToMany(
> fetch = FetchType.EAGER,
> cascade = {CascadeType.PERSIST, CascadeType.MERGE},
> mappedBy = "heads"
> )
> @Getter(AccessLevel.NONE)
> @Setter(AccessLevel.NONE)
> private Set<DatabaseEntity> databases = new HashSet<>();
> @ToString.Exclude
> @ManyToMany(
> fetch = FetchType.EAGER,
> cascade = {CascadeType.PERSIST, CascadeType.MERGE},
> mappedBy = "heads"
> )
> @Getter(AccessLevel.NONE)
> @Setter(AccessLevel.NONE)
> private Set<TagEntity> tags = new HashSet<>();
> @ToString.Exclude
> @ManyToMany(
> fetch = FetchType.EAGER,
> cascade = {CascadeType.PERSIST, CascadeType.MERGE},
> mappedBy = "heads"
> )
> @Getter(AccessLevel.NONE)
> @Setter(AccessLevel.NONE)
> private Set<CategoryEntity> categories = new HashSet<>();
> @ToString.Exclude
> @ManyToMany(
> fetch = FetchType.LAZY,
> cascade = {CascadeType.PERSIST, CascadeType.MERGE},
> mappedBy = "heads"
> )
> @Getter(AccessLevel.NONE)
> @Setter(AccessLevel.NONE)
> private Set<SearchEntity> searches = new HashSet<>();
> public Set<DatabaseEntity> getDatabases() {
> return Collections.unmodifiableSet(databases);
> }
> public Set<TagEntity> getTags() {
> return Collections.unmodifiableSet(tags);
> }
> public Set<CategoryEntity> getCategories() {
> return Collections.unmodifiableSet(categories);
> }
> public Set<SearchEntity> getSearches() {
> return Collections.unmodifiableSet(searches);
> }
> } {code}
> Note the {{{}unique = true{}}}, {{nullable = false}} (and the
> {{{}heads_headowner_index{}}})
> I have created a couple of functions using a CriteriaBuilder:
> {code:java}
> public List<String> findAllHeadOwnersByHeadOwnerIn(Collection<String>
> headOwners) {
> final var resultList = new ArrayList<String>();
> final var headOwnerGroups =
> CollectionUtils.partitionCollection(headOwners,
> configProperties.getDatabase().getChunkSize());
> for (final var headOwnerGroup : headOwnerGroups) {
> final var query = querySelectionByCondition(HeadEntity.class,
> root -> root.get("headOwner"), String.class,
> (criteriaBuilder, root) ->
> root.get("headOwner").in(headOwnerGroup)
> );
> resultList.addAll(query.getResultList());
> }
> return resultList;
> }
> public <E, T> TypedQuery<T> querySelectionByCondition(
> Class<E> entityType,
> Function<Root<E>, Path<T>> selection,
> Class<T> selectPropertyType,
> BiFunction<CriteriaBuilder, Root<E>, Predicate> whereCondition
> ) {
> final var criteriaBuilder = entityManager.getCriteriaBuilder();
> final var criteriaQuery = criteriaBuilder
> .createQuery(selectPropertyType);
> final var root = criteriaQuery.from(entityType);
>
> criteriaQuery.select(selection.apply(root)).where(whereCondition.apply(criteriaBuilder,
> root));
> return entityManager.createQuery(criteriaQuery);
> }
> public <E> Optional<E> getSingleResult(TypedQuery<E> query) {
> try {
> return Optional.of(query.getSingleResult());
> } catch (NoResultException e) {
> return Optional.empty();
> }
> }
> public <T> List<T> getMutableResultList(TypedQuery<T> query) {
> return new ArrayList<>(query.getResultList());
> } {code}
>
> Note the {{root.get("headOwner").in(headOwnerGroup)}} that is used in the
> {{findAllHeadOwnersByHeadOwnerIn}} function.
> (FYI: I use a chunksize of 500, because it was causing issues in the past
> when I didn't partition or used anything above 1000).
> *Expected behavior:*
> When I run code that uses this, I would expect it to generate a {{SELECT
> h.headOwner FROM heads h WHERE h.headowner IN (...);}}
> I also expected the query to be fast, even in a database with 10000s of rows,
> due to the index.
> *Actual behavior:*
> The resulting typed query is this:
> {code:java}
> SELECT t0.headOwner FROM heads t0
> WHERE (
> (
> t0.headOwner = ?
> OR t0.headOwner = ?
> OR t0.headOwner = ?
> OR t0.headOwner = ?
> -- in total 500 t0.headOwner comparisons
> OR t0.headOwner = ?
> OR t0.headOwner = ?
> OR t0.headOwner = ?
> )
> AND t0.headOwner IS NOT NULL
> ) {code}
> Note the {{AND t0.headOwner IS NOT NULL}} even though headOwner is not
> nullable.
> In a database with 50000-ish rows, this query takes about 1500-2500ms to
> complete.
> If I ran the query, without the AND statement, so:
> {code:java}
> SELECT t0.headOwner FROM heads t0
> WHERE (
> (
> t0.headOwner = ?
> OR t0.headOwner = ?
> OR t0.headOwner = ?
> -- in total 500 t0.headOwner comparisons
> OR t0.headOwner = ?
> OR t0.headOwner = ?
> OR t0.headOwner = ?
> )
> -- without the AND t0.headOwner IS NOT NULL
> ){code}
> This query only takes 150ms to complete.
> If I were to use {{SELECT h.headOwner FROM heads h WHERE h.headowner IN
> (...);}} with 500 values in the {{IN()}} expression, then it only takes
> 80-100ms
> *Versions:*
> * OpenJPA: 3.2.0
> * HSQLDB: 2.7.1
> * Java: OpenJDK 17.0.2
> *Config:*
> {code:java}
> <?xml version="1.0" encoding="UTF-8"?>
> <persistence version="2.1"
> xmlns="http://xmlns.jcp.org/xml/ns/persistence";
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
> xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
> http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd";>
> <persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
>
> <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
>
> <class>com.github.cc007.headsplugin.integration.database.entities.CategoryEntity</class>
>
> <class>com.github.cc007.headsplugin.integration.database.entities.DatabaseEntity</class>
>
> <class>com.github.cc007.headsplugin.integration.database.entities.HeadEntity</class>
>
> <class>com.github.cc007.headsplugin.integration.database.entities.SearchEntity</class>
>
> <class>com.github.cc007.headsplugin.integration.database.entities.TagEntity</class>
> <exclude-unlisted-classes>true</exclude-unlisted-classes>
> <properties>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema(SchemaAction='add', ForeignKeys=true)"/>
> <property name="openjpa.ConnectionURL"
> value="jdbc:hsqldb:file:plugins/HeadsPluginAPI/data/heads;hsqldb.lock_file=false"/>
> <property name="openjpa.ConnectionDriverName"
> value="org.hsqldb.jdbcDriver"/>
> <property name="openjpa.ConnectionUserName" value="sa"/>
> <property name="openjpa.ConnectionPassword" value=""/>
> <!-- <property name="openjpa.Log" value="DefaultLevel=WARN,
> Tool=INFO"/>-->
> <property name="openjpa.Log" value="DefaultLevel=WARN, Tool=INFO,
> SQL=TRACE"/>
> </properties>
> </persistence-unit>
> </persistence> {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
