[FIXED] PagingAndSortingRepository first page loads very slow

Issue

I'm using PagingAndSortingRepository for pagination. With a few data everything works fine.

When I have larger datasets with over about 1000 entries, I have performance issues while loading the first page. All other pages are loaded a lot faster (Order of requests doesn't have any impact on performance).

My Repository looks like this:

@Repository
@Transactional(readOnly = true)
public interface MyRepository extends PagingAndSortingRepository<MyObject, Integer>, JpaSpecificationExecutor<MyObject> {
}

I'm calling it this way:

Specifications<MyObject> filter= getMyFilter();
Pageable pageable = PageRequest.of(page, size, sort);
Page<MyObject> page = this.myRepository.findAll(filter, pageable);

My first assumption was that for page 1 some expensive SQL queries are generated. But there was no big difference between the statements for page 1 and page 2. Also, they are both doing a count. First, I thought this could be the reason.

I guess the problem is within the repository. Did anybody have the same issue?

Solution

The problem were some statistics in the mssql database. After i deleted them the performance issues was solved.

Answered By - max
Answer Checked By - Robin (JavaFixing Admin)