[jira] [Updated] (OPENJPA-2920) CriteriaBuilder generating suboptimal code

[Rik Schaaf (Jira)]

     [ 
https://issues.apache.org/jira/browse/OPENJPA-2920?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Rik Schaaf updated OPENJPA-2920:
--------------------------------
    Description: 
*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&gt;com.github.cc007.headsplugin.integration.database.entities.CategoryEntity</class&gt;
    
<class&gt;com.github.cc007.headsplugin.integration.database.entities.DatabaseEntity</class&gt;
    
<class&gt;com.github.cc007.headsplugin.integration.database.entities.HeadEntity</class&gt;
    
<class&gt;com.github.cc007.headsplugin.integration.database.entities.SearchEntity</class&gt;
    
<class&gt;com.github.cc007.headsplugin.integration.database.entities.TagEntity</class&gt;
    <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}
 

 

  was:
*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}
 

 


> 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&gt;com.github.cc007.headsplugin.integration.database.entities.CategoryEntity</class&gt;
>     
> <class&gt;com.github.cc007.headsplugin.integration.database.entities.DatabaseEntity</class&gt;
>     
> <class&gt;com.github.cc007.headsplugin.integration.database.entities.HeadEntity</class&gt;
>     
> <class&gt;com.github.cc007.headsplugin.integration.database.entities.SearchEntity</class&gt;
>     
> <class&gt;com.github.cc007.headsplugin.integration.database.entities.TagEntity</class&gt;
>     <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)