Why is paged query slower than normal with Spring Data?
Given that I have a simple query:
List<Customer> findByEntity(String entity);
This query returns 7k records in 700ms.
Page<Customer> findByEntity(String entity, Pageable pageable);
this query returns 10 records in 1080ms. I know the extra request for a counter for pagination, but still something seems to be. Also one strange thing I noticed is that if I increase the page size from 10 to 1900 the response time will be exactly the same around 1080ms.
Any suggestions?
source to share
It might actually be a counting question. If you insist on finding out about the total number of items matching the collection, unfortunately, this additional query is unfortunately not available. However, there are two ways to avoid the additional overhead if you can sacrifice the information you gain:
- Use
Slice
as return type -Slice
does not provide a method to know the total number of items, but lets you know if the next slice is available.We avoid the count request here by reading one more item than the requested one and using its (un) presence as an indicator availability of the next cut. - Use
List
as a return type . This will simply apply the page options to the request and return a box of selected items. However, it doesn't tell you if the following data is available.
source to share
The paginated method fires two requests:
1) select count(e.id) from Entity e //to get number of total records
2) select e from Entity e limit 10 [offset 10] //'offset 10' is used for next pages
The first request is 7k records slower, IMHO.
The upcoming release of Ingalis from Spring Data will use an improved algorithm for paginated queries ( more info ).
Any suggestions?
I think using a paginated query with 7k is writing it down to no use. You have to limit it.
source to share