title: JPA native query to QueryDSL
body: <p>I am using the following query to fetch records from MySQL database:</p>
  
  <pre><code>SELECT m.manuf_code
       , m.manuf_display_name
       , m.record_timestamp
       , m.record_status 
    FROM manufacturers m 
    JOIN
       ( SELECT manuf_code
             , MAX(record_timestamp) AS maxdate 
          FROM manufacturers 
             WHERE record_status = 'N' OR record_status = 'U' 
           GROUP 
            BY manuf_code) mn
      ON m.manuf_code = mn.manuf_code 
     AND m.record_timestamp = mn.maxdate 
  </code></pre>
  
  <p>There are about 10 million records in DB.</p>
  
  <p>These records are fetched from a Java Application, which is causing Timeout.
  The same query works well on Database level and I was able to optimize this using Indexes.</p>
  
  <p>Is it worth trying to convert this query to JPAQuery (QuerDSL) and the give a try to fetch records. Will it be any faster ?
  Assuming there are Foreign key references, which JPA fetches internally.
  If it is good enough to try, please suggest the equivalent Query DSL for the native query !</p>
  
  <p>EDIT:</p>
  
  <p>This Query fetches the latest records from the table.
  This table will have around 2 Million records, so when through JPA repository, I make a call to fetch these many records, it goes in waiting and eventually times out.
  One solution is to use the same native query and perform Pagination while fetching records.</p>
  
  <p>I was wondering if there is a way apart from paginating to fetch those many records.</p>
  
  <p>I might be very wrong. I now realize this is not an issue with my query but with the fact that fetching 2 million records from DB is not a good choice.</p>