Hi,
I have a "complex/big" SQL query that is rather slow and probably unoptimized. It takes a lot of time for the database to process and return the answer. The tables included in the search are also rather big (> 1 000 000 rows).
For testing I've tried to split up the giant SQL question and run the queries separately, which takes considerably less time. However, databases are usually very good at optimizing queries but either way I thought it was a good idea to ask if there is a way to rephrase the query to speed it up? or some Hibernate specific optimizations that can be done. It's an Oracle database btw.
Code:
session.createQuery("select substr(Loan.branchId,1,2), DeviationHistory.text from Loan as Loan, "
+ "LoanHistory as DeviationHistory, LoanHistory as ToVaultHistory where Loan.id = DeviationHistory.loanId "
+ "and DeviationHistory.loanId = ToVaultHistory.loanId "
+ "and DeviationHistory.description.id = :deviation "
+ "and DeviationHistory.eventDate between :startDate and :endDate "
+ "and ToVaultHistory.description.id = :sent "
+ "and ToVaultHistory.eventDate between :startDate and :endDate");
Regarding indexes for the database, indexing the eventDate column only speeds up the question marginally.
Login
FAQ
Search