CODEWITHSUNDEEP
javaperformancejakarta-eejpaeclipselink
user1930502
user1930502

Reputation:

Creating JPA Native Query is slow

I use a native query to get results from a table of orders. Each order has a date and I want to select only by year and date. The whole method has this body.

    Query query = entityManager.createNativeQuery("SELECT COUNT(PLACED_ORDER.ID) as POCET_OBJEDNAVEK FROM PLACED_ORDER WHERE ORDERDATE IS NOT NULL AND EXTRACT(YEAR FROM ORDERDATE) = ?1 AND extract(DAY FROM ORDERDATE) = ?2");
    query.setParameter(1, year);
    query.setParameter(2, day);

    return (Long) query.getSingleResult();

This query WORKS properly, but is very slow to execute. By slow, I mean a second or more for every method call. Transaction management is set do Required.

Named queries are executed within few milliseconds, measured from request to response. Calling this method alone is very slow. Is there something that can be done about it ?

EDIT - Reaction to comments:

When I query the database from command line or via MySQL WorkBench, the performance is normal (max a millisecond or a few under heavy load).

I should also metion that SECOND LEVEL CACHE is disabled on the project and there is nothing I can do about it.

MySQL Explain looks like this (there is no easy way for me to enable explain on EclipseLink).

enter image description here

Entity annotations used to determine data type and DB constraints.

@Entity(name = "PLACED_ORDER")
public class Order implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@ManyToOne
@JoinColumn(name = "CUSTOMER_ID")
private Customer customer;
@OneToMany(fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.REMOVE, CascadeType.PERSIST}, orphanRemoval = true)
@JoinColumn(name = "PLACED_ORDER_ID")
private List<OrderItem> items;
@Temporal(TemporalType.TIMESTAMP)
private Date orderDate;
@Enumerated(EnumType.STRING)
@NotNull
private OrderState orderState = OrderState.SEMIFINISHED;
@OneToOne
private Transportation transportation;
@OneToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, orphanRemoval = true)
private Address deliveryAddress;
@Enumerated(EnumType.STRING)
private TransportationType transportationType;

Upvotes: 1

Views: 5603

Answers (1)

user1930502
user1930502

Reputation:

With technologies specified in the question and second level cache disabled, the solution was to write the native query as @NamedNativeQuery, instead of creating it every time method was called. This way, the performance significantly increased.

Upvotes: 1

Related Questions