CODEWITHSUNDEEP
sqloracle-databasespring-bootjpajpql
mat373
mat373

Reputation: 171

Spring Boot JPA - Database query performance

@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.

Upvotes: 1

Views: 3794

Answers (1)

salam_verdim_alana_panyatkasi
salam_verdim_alana_panyatkasi

Reputation: 373

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.

Upvotes: 1

Related Questions