1

In my Spring Repository Class, I have the following query (kind of analytics query) running on a Postgresql 9.6 server : 

@Query("SELECT d.id as departement_id, COUNT(m.id) as nbMateriel FROM Departement d LEFT JOIN d.sites s LEFT JOIN s.materiels m WHERE "
        + "(s.metier.id IN (:metier_id) OR :metier_id IS NULL) AND (s.entite.id IN (:entite_id) OR :entite_id IS NULL) "
        + "AND (m.materielType.id IN (:materielType_id) OR :materielType_id IS NULL) AND "
        + "(d.id= :departement_id OR :departement_id IS NULL) "
        + "AND m.dateLivraison is not null  and (EXTRACT(YEAR FROM m.dateLivraison) < :date_id OR :date_id IS NULL) "
        + "AND ( m.estHISM =:estHISM OR :estHISM IS NULL OR m.estHISM IS NULL) "
        + "GROUP BY d.id")
List<Map<Long, Long>> countByDepartementWithFilter(@Param("metier_id") List<Long> metier_id,@Param("entite_id") List<Long> entite_id,@Param("materielType_id") List<Long> materielType_id,
        @Param("departement_id") Long departement_id, @Param("date_id") Integer date_id,
        @Param("estHISM") Boolean estHISM);

The problem is : this query is called several times with different combination of parameters, and after 5-6 calls, time execution go from 20 ms to 10 000 ms

From what I have read, what cause this is the use of prepared statements which is not suited to analytics queries, where there are number of parameters whose values can change a lot. And indeed, running the above query directly is always fast (20 ms).

Question 1 : How can I say to Spring JPA not to use prepared statements for this specific query ?

Question 2 : If Question 1 not possible, what workaround can I have ?

tacou_st
  • 109
  • 1
  • 9

1 Answers1

1

There are some tips in general to enhance query performance both from JPA / DB POV:

1- use @NamedQuery instead of @Query

2- For reporting queries, don't run it inside a transaction

3- You can set the flush mode to COMMIT if you don't need to flush the persistence context before the query runs

4- check the generated query, take it and run it on SQL developer od TOAD, check its cost and run strategy, you can also consult your DBA if you can enhance it with some native DB functions / provcedures , hence use a native query instead of JPQL query

5- if data returning is large, consider making this query a DB view or materialized view and calling it directly

6- make use of query hints to activate a certain index for example, note that indexes may be ignored in case of JPQL

7- you can use native query if the query hint didn't work on JPQL

8- While comparing the query on SQL Developer with that from the code make sure that you are comparing right , the query might run very quickly initially on DB directly but takes loong time to fetch all the data , and you might be comparing this initial short time with the application data fetch time

9- use fetch size hint according to your provider

10- According to my knowledge, you might escape prepared statement if you use native non parametrized query (thus using manual placeholders and replacing values manually) but generally this should be used with care and avoided as much as possible because of SQL injection vulnerabilities and also disallows the DB query engine from as well as the hibernate engine from precompiling the queries

osama yaccoub
  • 1,884
  • 2
  • 17
  • 47
  • Thx. However, I do see the generated queries in my DB logs and the associated execution times. That is why I am pretty sure the issue comes with prepared statement that should not be used. Your answer does not give me tips on how pass-by prepared statements and execute SQL directly – tacou_st Feb 07 '18 at 09:58
  • welcome bro .. I added some points to my answer, you can consider point 10 as answer to quest 1 and all the other points as answer to quest 2 – osama yaccoub Feb 07 '18 at 10:37
  • I used point 10 as I wrote the SQL query, mapped to a DTO => No more performance problem. – tacou_st Feb 07 '18 at 14:55