How to fetch thousands of database records efficiently using JPA 2 and QueryDSL?

Question

Currently I have an implementation which fetches 10 thousand records withing 10-12 seconds. Performance of this query can be improved and how ? Following is my code snippet based on QueryDSL and JPA 2.

public List<EntryEntity> getEntries() {

QEntryEntity qEntryEntity = QEntryEntity.entryEntity;

return queryfactory.selectFrom(qEntryEntity).orderBy(qEntryEntity.name.asc()).fetch();
}

Answer 1

You're probably missing an index on that NAME column:

CREATE INDEX solve_all_problems ON entry_entity (name);

This works well because an index pre-orders all your data as it is a ordered data structure, so the database no longer needs to do any ordering work when you run a query like the one you're running right now. Use-the-index-luke has a nice explanation on this topic.

Side note: Be careful with adding indexes. While they drastically speed up read operations, each index will slow down write operations on that column. Each index is a tradeoff.