Performance is slow when the query is like below (have in condition) and access on last page with large data

Hibernate ORM
1

Hi team,

We are using spring boot version 3.1.5 (hibernate 6.2.13.Final). Encountered slow performance when access some of the last page (> 100) (page < 100 is fast in 1s), in our data, the last page is 205 and 800, both are slow around 1 minute. Moreover when i execute the query directly in db, it’s fast for last page, so i would say the query is ok.

this is the query

@Query(value = "select cgfd "
	+ "from MockEntity cgfd "
	+ "where "
	+ "cgfd.managerCode = :managerCode "
	+ "and cgfd.clientGroupNumber like %:clientGroupNo% "
	+ "and (:currentPricingGuidance = null or cgfd.currentPricingGuidance = :currentPricingGuidance) "
	+ "and (:wfStatusList = null or cgfd.status in :wfStatusList) "
			 + "and (:outOfScope = null or cgfd.outOfScope = :outOfScope)")
	Page<MockEntity > queryData(@Param("managerCode") String managerCode,
			@Param("clientGroupNo") String clientGroupNo,
			@Param("currentPricingGuidance") Integer currentPricingGuidance,
			@Param("wfStatusList") List<String> wfStatusList,
			@Param("outOfScope") Boolean outOfScope,
			Pageable pageable);

this is how we query

Page<MockEntity> list = repository.queryData(
					search.getManagerCode(),
					search.getClientGroupNo(),
                                   search.getCurrentPricingGuidance(),
				    wfStatusList,
				    isOutOfscope,
					PageRequest.of(search.getPage(), commonProperties.getPageSize(), sort)
			);

note: already changed some of the name above but the structure is the same.
MockEntity: this is the view actually.
pageSize is 20.
last page (search.getPage()): 204.
total: around 4k rows.

sql server
managerCode: nvarchar (eg: RM001), not null
clientGroupNumber: nvarchar (e.g: “”, empty string)
currentPricingGuidance : int (e.g: null)
status: nvarchar (e.g: null)
outOfScope: bit (e.g: null)

if the data MockEntity is around 200 rows in total, no issue.
if i use @EntityManager to build the exact query, no issue.

the query is fast when i remove condition: "and (:wfStatusList = null or cgfd.status in :wfStatusList) "
so i think might something wrong with in condition.

can you check if that’s issue, can you try to reproduce that? (maybe this is spring jpa issue?)
thanks.

2

Hello,

To address the slow performance issue with your query in Spring Data JPA, try using a native query for pagination. Here’s how you can modify your repository to use a native query:

Repository Interface

public interface MockEntityRepository extends JpaRepository<MockEntity, Long> {
Query(value = "SELECT * FROM MockEntity WHERE "
+ "managerCode = :managerCode AND "
+ "clientGroupNumber LIKE %:clientGroupNo% AND "
+ "(:currentPricingGuidance IS NULL OR currentPricingGuidance = :currentPricingGuidance) AND "
+ "(:wfStatusList IS NULL OR status IN :wfStatusList) AND "
+ “(:outOfScope IS NULL OR outOfScope = :outOfScope)”,
nativeQuery = true,
countQuery = "SELECT COUNT(*) FROM MockEntity WHERE "
+ "managerCode = :managerCode AND "
+ "clientGroupNumber LIKE %:clientGroupNo% AND "
+ "(:currentPricingGuidance IS NULL OR currentPricingGuidance = :currentPricingGuidance) AND "
+ "(:wfStatusList IS NULL OR status IN :wfStatusList) AND "
+ “(:outOfScope IS NULL OR outOfScope = :outOfScope)”)
Page queryData(Param(“managerCode”) String managerCode,
Param(“clientGroupNo”) String clientGroupNo,
Param(“currentPricingGuidance”) Integer currentPricingGuidance,
Param(“wfStatusList”) List wfStatusList,
Param(“outOfScope”) Boolean outOfScope,
Pageable pageable);
}

Service to Test Query

Service
public class MockEntityService {
Autowired
private MockEntityRepository repository;

public Page<MockEntity> searchEntities(String managerCode, String clientGroupNo, Integer currentPricingGuidance,
                                       List<String> wfStatusList, Boolean outOfScope, int page, int size) {
    Pageable pageable = PageRequest.of(page, size, Sort.by("id"));
    return repository.queryData(managerCode, clientGroupNo, currentPricingGuidance, wfStatusList, outOfScope, pageable);
}

}

Usage

Page list = repository.queryData(
search.getManagerCode(),
search.getClientGroupNo(),
search.getCurrentPricingGuidance(),
wfStatusList,
isOutOfscope,
PageRequest.of(search.getPage(), commonProperties.getPageSize(), sort)
);

By switching to a native query, you can bypass potential inefficiencies in how Hibernate handles the IN clause and pagination, improving performance for your larger datasets.

I hope my suggestion is helpful to you.

Best Regard,
angela683

1 Like
3

thanks a lot, it works.

i didn’t know that with nativeQuery mode, the parameter can check null also. btw in my case don’t need to rewrite the count query.
also it seems i can not keep the check null for :wfStatusList IS NULL (encountered error), so i have to remove that and update the logic to initialize all possible wfStatusList before.

this is the updated query that works

@Query(nativeQuery = true,
			value = """
select * from schema.MockEntity cgfd
where cgfd.manager_code = :managerCode
 and cgfd.client_group_number like %:clientGroupNo%
 and (:currentPricingGuidance is null or cgfd.current_pricing_guidance = :currentPricingGuidance)
 and cgfd.status in :wfStatusList
 and (:outOfScope is null or cgfd.out_of_scope = :outOfScope)
					""")
	Page<MockEntity> queryData(@Param("managerCode") String managerCode,
			@Param("clientGroupNo") String clientGroupNo,
			@Param("currentPricingGuidance") Integer currentPricingGuidance,
			@Param("wfStatusList") List<String> wfStatusList,
			@Param("outOfScope") Boolean outOfScope,
			Pageable pageable);

But i’m still want to know if this is a bug of hibernate? or already fixed/handled in which version?

4

There are great articles on the internet about this that explain in detail why offset pagination (which is what you’re essentially using behind the scenes) is going to perform bad: We need tool support for keyset pagination

Using keyset pagination is a way to improve the performance if you have an appropriate index, but ultimately, you shouldn’t even bother with users that ask for the 100th page. Tell them to use filters to reduce the result list further instead of going through hundreds of pages.

5

@beikov btw my team mate fixed with another approach by adding some indexes. (actually the view MockEntity come from some other views). but i don’t get why execute the query in db client is fast but from hibernate, it is slow.