optimizing Spring Boot JPA query

Question

I'm new to Jpa. I have a List list which has about 10000-50000 client objects in it.

I'm iterating through this list and querying each client if he has made any purchases like this:

List<TransactRepViewModel> temporalList = transactRepViewRepository
        .findByClientIdAndClDateBetween(clieTabModel.getClientId(),
                reportInterval.getStartDate(),
                reportInterval.getEndDate());

TransactRepViewRepository.class method looks like this:

List<TransactRepViewModel> findByClientIdAndClDateBetween(
        String clientId, Date startDate, Date endDate) throws DataAccessException;

I would really like to improve my search time, since iterating through such amount of clients takes quite some time. Are there any technique I could use?

write a new method on your JPA repository. This method could be like: findAllByStartDateEndDate. This link might help: docs.spring.io/spring-data/jpa/docs/1.4.3.RELEASE/reference/… — Lawrence Tierney
– Lawrence Tierney, Commented Nov 4, 2016 at 15:16
Where does this huge list of clients come from? If it comes from another query, then you should use a single query with a join. If it comes from somewhere else, consider using an in clause to pass many client IDs at once. But beware that most databases limit the number of values inside the in clause, and/or the length of the query. Oracle for example has a limit of 1000. Still, that would reduce the number of queries from 50000 to 50. — JB Nizet
– JB Nizet, Commented Nov 4, 2016 at 15:29

GreyBeardedGeek · Accepted Answer · 2016-11-04 15:50:32Z

3

It's difficult to make a concrete recommendation without knowing a lot more about what you're trying to do, but generally:

Do one large query, so, for example, create a repository method that finds all purchases between the dates that you are interested in, and make sure that the result includes the client id. Then match up the results with clients in Java code after you get the results back. This avoids the overhead of doing potentially thousands of database queries. You might want to "order by" the client id and purchase date.
Make sure that you have proper indexes on the table(s) involved, and verify that when jpa executes it's query, the database uses the indexes. A table scan would probably kill performance.

answered Nov 4, 2016 at 15:50

GreyBeardedGeek

30.3k2 gold badges51 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

optimizing Spring Boot JPA query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related