I was told setItems no longer use count but I’m seeing it in the log. I’m using @Transactional(readOnly = true) public Page<CustomerEntity> list(Pageable pageable, Specification<CustomerEntity> filter) { return repository.findAll(filter, pageable); }. But still the logs says Hibernate: select c1_0.id, c1_0.address, c1_0.city, c1_0.contact_name, c1_0.country, c1_0.created_date, c1_0.customer_name, c1_0.last_modified_date, c1_0.postal_code from customer_entity c1_0 offset ? rows fetch first ? rows only Hibernate: select count(c1_0.id) from customer_entity c1_0
Spring is doing the count query, you can see the discussion here: Don't use findAll(Pageable pageable) · Issue #103 · vaadin/starters · GitHub
Also, the count query is optional, but it’s better to provide it if you don’t have a performance reason not to
what is the use of this count query ?
If you provide a count the scroll bar will have the correct size. Without the scroll bar might shrink while scrolling
so we’ve to live with this
when there are thousands of records, there is a huge impact on the performance
the progres bar keeps runing for about 1 min
Sounds like you have done no database optimization like reduced columns, added indexes or reduced joins / queries
For your initial question, the count is used to fill the Page information, if you return a List or a Stream then you don’t need the count query.
( as it’s written in the link). Of course you can also try to optimize your database or query. A thousand records is nothing for a database, so there must be something else.
We have millions of records without an issue. How does your grid look like and how long takes your query?
yes it is returning stream. Should I try to create custom repo as given in the link ?
it is a simple table having 7 columns and it takes 1-2 minutes
Do you have Joins or complex where condition?
no. simple SELECT with two WHERE conditions
it checks for a matching STRING value
Lower / upper casing, like, binary field or something else that “intensive” to compute?
yes lower with like operator
Create a proper index for that