How to improve JPA Query performance and response time on multiple Join tables and conditions

I am very fresh java beginner and I am trying to implement possibility to review documents. User, who belongs to a certain group can review documents of particular types that are assigned to that group’s review document types. User can not review own documents. When user belongs to many groups that can review many document types, sometimes I get about 1500~ documents that meet query condition. I tried paging, but still there are some visible latency in front page. When I fetch all documents (~1500) it takes about 3000+ ms. I am not sure if it is a lot or not. My JPA query is below.

    @Query("SELECT d FROM Document d WHERE d.id IN (SELECT DISTINCT d.id FROM Document d" +
        " JOIN d.documentType dt" +
        " JOIN dt.reviewUserGroups rug" +
        " JOIN rug.users u WHERE u.username = :username" +
        " AND d.documentState = it.akademija.wizards.enums.DocumentState.SUBMITTED" +
        " AND u <> d.author" +
        " AND (lower(CONCAT(d.author.firstname, ' ', d.author.lastname)) like %:searchFor% " +
        " OR lower(d.title) like %:searchFor%" +
        " OR lower(d.description) like %:searchFor%" +
        " OR lower(d.id) like %:searchFor%" +
        " OR lower(dt.title) like %:searchFor%))")
    Page<Document> getDocumentsForReview(@Param(value = "username") String username,
                                     @Param(value = "searchFor") String searchFor,
                                     Pageable pageable);

Please let me know what is the best way to improve performance, because I have some feeling that this type of search using “like” multiple times in query and joining through many Entities is not the right one.

Answer

Use fulltext search instead of like. Create Fulltext index for that, see http://www.h2database.com/html/tutorial.html?highlight=fulltext&search=fulltext#fulltext

Advertisement

Answer