title: Creating JPA Native Query is slow
body: <ul>
  <li>Database: MySQL 5.1</li>
  <li>JPA implementation: EclipseLink</li>
  <li>Container: Glassfish 4</li>
  <li>Java: JDK 7u55</li>
  </ul>
  
  <p>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.</p>
  
  <pre><code>    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();
  </code></pre>
  
  <p>This query <strong>WORKS</strong> 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.</p>
  
  <p>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 ?</p>
  
  <p>EDIT - Reaction to comments:</p>
  
  <p>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).</p>
  
  <p>I should also metion that <strong>SECOND LEVEL CACHE</strong> is <strong>disabled</strong> on the project and there is nothing I can do about it.</p>
  
  <p>MySQL Explain looks like this (there is no easy way for me to enable explain on EclipseLink).</p>
  
  <p><img src="https://i.stack.imgur.com/ig2ZZ.png" alt="enter image description here"></p>
  
  <p>Entity annotations used to determine data type and DB constraints.</p>
  
  <pre><code>@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&lt;OrderItem&gt; 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;
  </code></pre>