Performance issue in a query due to compareTo calls

Hello,

we have a question again regarding query performance.

The query:

SELECT v FROM Values v WHERE v.step.stepNumber = ?

is very slow in comparing to the query:

SELECT v FROM Values v WHERE v.stepNumber = ?

Can you explain why the first query needs so much time or do you have a hint for optimization of the query or entities?

For the first query I attached the profiling images with the bottlenecks of the query execution.
Why the compareTo() calls need so much time?

@Entity

@Access (AccessType.FIELD)
public class TCStep extends Identifiable {

    @Index
    private long stepNumber;

    @Basic
    private String comment;

    @Basic
    private boolean isVisible;

    @Basic
    private int numberOfDefinedValues = 0;

    @Basic
    private int lastIdentifierIndex = -1;

    @Deprecated
    @Removed (version = EPVersion.EP2_5_0)
    @ElementCollection
    private Map<Integer, String> values = new HashMap<>();

    @OneToOne (fetch = FetchType.LAZY, cascade = CascadeType.REMOVE)
    private Values valuesRef;
}

@Index (members = {"step.stepNumber"})

@Entity

@Access (AccessType.FIELD)
public class Values extends Identifiable {

    @Index
    private long stepNumber;   

    @OneToOne (fetch = FetchType.EAGER)
    private TCStep step;

    //For performance reasons, we use a list instead of a map as put operations are more expensive as the add operations
    //per index in the list
    @ElementCollection (fetch = FetchType.EAGER)
    private Map<Integer, String> values = new HashMap<>();
}

The query:

SELECT v FROM Values v WHERE v.step.stepNumber = ?

is actually a short form of a JOIN query:

SELECT v FROM Values v JOIN v.step s WHERE s.stepNumber = ?

JOIN queries are always slower because multiple entities are involved.

You can improve the performance of queries by using indexes but the index step.stepNumber is invalid as only data in a single entity can be indexed and this index requires collecting data from 2 entities.

The step.stepNumber index could be valid if TCStep would be defined as @Embeddable rather than as @Entity (this may possibly improve performance in general but will not allow direct queries on steps and sharing steps).

Alternatively you can keep both classes as entities but replace the invalid step.stepNumber index with 2 separate valid indexes: step in Values and stepNumber in TCStep. Note that it may take some time for a new index in a large database to become active as it requires background indexing of existing data.

If you try either of these changes please report whether or not it improves performance.

But what is needed to define an index on the field 'step'?


@Entity

@Access (AccessType.FIELD)
public class Values extends Identifiable {

    @OneToOne (fetch = FetchType.EAGER)
    private TCStep step;

    @javax.jdo.annotations.Index
    private TCStep step;

See examples on this manual page.