Rik Schaaf created OPENJPA-2920:
-----------------------------------
Summary: 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
*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).
*Expectation:*
When I run code that uses this, I would expect it to generate a {{SELECT
h.headOwner FROM heads h WHERE h.headowner IN (...);}}
*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)
