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

[binhjy]

Hi team,

We are using spring boot version 3.1.5. 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)

i tried to implement specification but still same issue.

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? thanks.

[binhjy]

btw the workaround is using nativeQuery = true, more detail is here: https://discourse.hibernate.org/t/performance-is-slow-when-the-query-is-like-below-have-in-condition-and-access-on-last-page-with-large-data/9575

[mp911de]

Have you introspected your query with a profiler to determine which parts of the execution flow do not meet your performance expectations?

[binhjy]

hi @mp911de , can you tell more detail how to "introspected your query with a profiler"?

[mp911de]

For example, use YourKit to introspect how long the query takes and what hot spots (CPU-time-wise) you discover.

Since this ticket isn't actionable, I'm closing it. We can reopen it later on if there is something we can do here.