Spring Boot JPA - Database query performance

Question

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

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
– pringi, Commented Feb 22, 2022 at 10:46
You question has not too much to do with JPA it is only query performance. Check and post the execution plan, 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
– Marmite Bomber, Commented Feb 22, 2022 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
– mat373, Commented Feb 22, 2022 at 21:06

salam_verdim_alana_panyatkasi · Accepted Answer · 2022-02-22 10:45:21Z

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.

answered Feb 22, 2022 at 10:45

salam_verdim_alana_panyatkasi

3834 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mat373 Over a year ago

In this solution what i need to prepare i need to use SQL query like in my question

salam_verdim_alana_panyatkasi Over a year ago

Then you need to talk to your reporters, who work to in this area. :)

mat373 Over a year ago

Think I achieved what i wanted. Creating a view helped a lot. Btw thanks for answers ;)

Marmite Bomber Over a year ago

Your view links points to SQL Server not Oracle database.

salam_verdim_alana_panyatkasi Over a year ago

The View is the general meaning does not mean which database you will use. @MarmiteBomber

|

Collectives™ on Stack Overflow

Spring Boot JPA - Database query performance

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related