Spring Data Pageable Performance

Question

Well, this is quite annoying and I really don't know how to solve this, so here is the thing.

Got an application which return paged data as follows:

@Query(value="SELECT DD FROM Document DD "
        + " WHERE DD.deletedByUsr IS NULL "
        + " AND DD.deleteTime IS NULL "
        + " AND DD.version = (SELECT MAX(D.version) FROM  Document D WHERE D.code = DD.code AND D.status = :status  AND DD.status =:status)")
public Page<Document> getLatestByStatus(@Param("status") DocumentStatus status, Pageable pageable);

Which is working fine.

As a result to test application performance we have added to database 20K data do this table and even I create a Pageable limited with 5 rows as follows:

Pageable pageable = PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "id"));

And calling the repository as follows:

Page<Document> latestByStatus = repository.getLatestByStatus(DocumentStatus.APPROVED, pageable);

It is taking forever to retrieve results.

However if I call like (using the same pageable object instatiated above):

Page<Document> latestByStatus = repository.findByStatus(DocumentStatus.APPROVED, pageable);

And on repository is:

public Page<Document> findByStatus(DocumentStatus status, Pageable pageable);

Results comes instantly.

I have run the following SQL directly on database (MSSQLSERVER) as a result to check how long it takes:

 SELECT 
DD .*
FROM DOCUMENT DD
WHERE DD.DELETED_BY_USER_ID IS NULL
AND DD.DELETE_TIME IS NULL
AND DD.STATUS IN (2)
AND DD.VERSION = (SELECT MAX(D.VERSION) FROM  DOCUMENT D WHERE D.CODE = DD.CODE AND D.STATUS IN (2)) AND DD.STATUS IN (2)
ORDER BY DD.ID 
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

And, also, results comes instantly.

I assume, regarding first case which takes forever, which Spring first retrieve all data (without limit rows) and then returns a Page with results quantity informed.

Don't know if theres something todo with @Query annotation.

I am using 2.3.0.RELEASE Spring Boot version by the way.

Anyways, why one takes forever to retrieve data and the other takes instantly to retrieve data?

Do you guys have any idea to solve this? Maybe there is a best way to write the query on @Query annotation?

Thanks in advance.

Answer 1

After several debugs and logging, I figured out that Spring Data does added paggination on hibernate generated query ( offset 0 rows fetch next 5 rows only), so the main problem it was on SQL query on @Query annotation.

I have changed to:

@Query(value="SELECT DD FROM Document DD "
            + " WHERE DD.deletedByUsr IS NULL "
            + " AND DD.deleteTime IS NULL "
            + " AND DD.status =:status "
            + " AND DD.version = (SELECT MAX(D.version) FROM  Document D WHERE D.code = DD.code AND D.status = :status  AND D.deleteTime IS NULL AND D.deletedByUsr IS NULL )")

(have added DD.status =:status and D.deletedByUsr IS NULL and D.deleteTime IS NULL on inner query).

Thank you anyway.