How to decrease query execution time using JPQL query

Question

I am trying to select some document table columns and map them into new object instead of returning the document as a whole. I am quite concerned about the execution time of the below query as it takes around ~4200 ms to execute for ~35,000 entries. What would be best solution to decrease the execution time? Is 4 seconds good time for 35,000 entries?

    @Query("SELECT DISTINCT new it.homepackage.models.document.DocumentGetReviewCommand(" +
        "d.author.firstname, d.author.lastname, d.id, d.title, d.description, d.documentType.title, d.submissionDate)" +
        " FROM Document d" +
        " WHERE d.documentType IN :docTypes" +
        " AND d.documentState = it.homepackage.enums.DocumentState.SUBMITTED" +
        " AND :username <> d.author.username")
Page<DocumentGetReviewCommand> getDocumentsForReview(@Param(value = "username") String username,
                                                     Pageable pageable, 
                                                     @Param(value = "docTypes") List<DocumentType> docTypes);

Get the real SQL query, get an execution plan for it and see where you can improve it. Impossible to tell what the problem is from what you posted. — Strelok, Mar 01 '19 at 02:09
This isn't going to change everything, but why do you need `DISTINCT` on this query? Also, how large is that `:docTypes` list? — Lukas Eder, Mar 01 '19 at 09:25
Yes, you are right. I forgot to remove it after refactoring code. :typeList varies from 5 to 20 — Andyally, Mar 01 '19 at 12:20

score 0 · Answer 1 · answered Mar 01 '19 at 14:28

You can start organizing the JOINs. When you use complex "train wreck" among the entities on JPQL (like entity1.entity2.entity3.name), Hibernate can generate weird (sometimes, invalid) SQLs.

First, the query could be:

SELECT DISTINCT new it.homepackage.models.document.DocumentGetReviewCommand(" +
        "author.firstname, author.lastname, d.id, d.title, d.description, documentType.title, d.submissionDate)" +
        " FROM Document d" +
        " JOIN d.author author " +
        " JOIN d.documentType documentType " +
        " WHERE d.documentType IN :docTypes" +
        " AND d.documentState = it.homepackage.enums.DocumentState.SUBMITTED" +
        " AND :username <> author.username

If the query is still slow, probably the cause is the IN clause. IN is equivalent to OR. Many itens on IN means a lot of OR on the SQL, what is generally difficult to the database optimize.

If the query is still slow, the second place to look is the DISTINCT. DISTINCT can sometimes be replaced by the GROUP BY for performance gain.

How to decrease query execution time using JPQL query

1 Answers1