1
@Language("SQL")
@Query(
    """
    SELECT DISTINCT o.*, ooc.*
    FROM WHITE_TABLE o
        LEFT JOIN BLUE_TABLE oow ON o.BLUE_ID= oow.ID
        LEFT JOIN RED_TABLE ooc on o.ID = ooc.RED_ID
        LEFT JOIN GREEN_TABLE ce on ce.GREEN_ID= ooc.RED_ID
        LEFT JOIN ORANGE TABLE d on d.ORANGE_GREEN_ID= ce.ID
     WHERE d.STATE= 'A'
        AND o.NO_MATTER IN :#{#param.setIds1}
        AND o.NO_MATTER IN :#{#param.setIds2}
        AND ooc.NO_MATTER IN :#{#param.setIds3}
        AND oow.NO_MATTER IN :#{#param.setIds4}
        AND (
                (
                    o.TIME is not null AND 
                    trunc(cast(o.TIME as DATE)) BETWEEN 
                    :#{#param.time_value} AND :#{#param.time_value2}
                ) OR 
                (
                    o.EXPIRE_TIME is null AND 
                    :#{#param.time} BETWEEN 
                    trunc(cast(d.time_value as Date)) AND trunc(cast(d.time_value2 as Date))
                )
        )
        """, nativeQuery = true
)

I have a query with several inner joins and I have a performance problem. The query even if I fetch 2 objects in postman the loading time is 2/3 seconds. What possibilities do you know to optimize the query ? In this case I have no possibility to use "NON native query". GREEN and ORANGE tables do not have their entity representation in the code.

Reportedly TRUNC CAST takes a lot of time and is inefficient but how else to write it ?

How can I speed up the query? Reflection ? Views? Do you have any examples?

I will add that the database is Oracle and there are indexes.

jarlh
  • 42,561
  • 8
  • 45
  • 63
mat373
  • 171
  • 4
  • 13
  • 1
    I would start by doing an Explain plan on your query to see where bottlenecks are. Check which indexes are being used (or adding more indexes). Not sure if creating a materialized view that has the string dates already transformed in dates will help. – pringi Feb 22 '22 at 10:46
  • You question has not too much to do with JPA it is only query performance. Check and post the [execution plan](https://stackoverflow.com/a/34975420/4808122), also highly relevant: how many records are filtered with the `NO_MATTER` predicates? How many rows returns the query? Whats is the data type of `time_value`, why do you cast it to `date`? – Marmite Bomber Feb 22 '22 at 19:18
  • NO_MATTER - It may have several or even thousands. time_value is ZonedDateTime type. Query may return serval or thousands. It depends on how large the date range is and the number of search parameters. – mat373 Feb 22 '22 at 21:06

1 Answers1

1

If you have dynamic queries like this, I recommend using the Criteria Builder API. What if your database version changes? You will have to return and make some changes in your query.and this will cause a waste of time or a nervous breakdown and there is some cases to speed up for your queries .

https://ubiq.co/database-blog/how-to-speed-up-sql-queries/

and I suggested using Views can improve performance.

https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query#:~:text=Yes%2C%20views%20can%20have%20a,that%20Views%20can%20improve%20performance.

salam_verdim_alana_panyatkasi
  • 303
  • 3
  • 11