CODEWITHSUNDEEP
javahibernatejpaspring-data-jpapersistence
codezoner
codezoner

Reputation: 1054

Retrieve specific number of records from database - JPA (Impove performance)

I found several solutions on internet, but in performance aspect those are not what i searched for. I have a table with records more than 10,000,000 records. I need to read only last 150 results.

query.select(trailRoot).distinct(Boolean.TRUE)
    .where(buildPredicateForAuditTrail(auditTrailCriteria, criteriaBuilder, trailRoot));

final TypedQuery<AuditTrail> typedQuery = entityManager.createQuery(query);
return typedQuery.setFirstResult(auditTrailCriteria.calculateFirstResultIndex())
    .setMaxResults(150).getResultList();

This code does the job. But it still reads all 10,000,000 records from db which makes a huge overload in web server. Can I simply limit the query like,

SELECT *FROM Persons LIMIT 5;

Is there a way to limit the result by query itself ?? Any help is highly appreciated.

Thank you.

Upvotes: 2

Views: 1711

Answers (1)

Talorias
Talorias

Reputation: 29

Why can't you set first result to a specific offset?

query.setFirstResult(firstResult);
query.setMaxResults(maxResults);

Have a look at LazyCarDataModel.java http://www.primefaces.org/showcase/ui/data/datatable/lazy.xhtml

Upvotes: 1

Related Questions