This is my first time posting, so please let me know if I am missing anything vital or required in my post. And, thanks in advance!
OverviewI am working on a project where we are using Java 1.6, Hibernate 3, and an Oracle 10g database.
There is a very large table with >200 million rows, that is indexed by Time, UniqueIdentifier, and several other columns frequently used for access.
There are many entries for a given UniqueIdentifier at different Times. Here is the raw SQL for the query we are having issues with:
Code:
select * from Table
where Time = (select max(Time) from Table where UniqueIdentifier='value')
and UniqueIdentifier='value'
As you can see, our goal is to retrieve the most recent record for a particular UniqueIdentifier.
The IssueWhen directly logged into the Oracle DB, this SQL query takes
<1 sec to return in all cases. When issued via Hibernate using .createSQLQuery(), or createQuery(), or createCriteria() the query also takes
<1 sec to return.
However,
when no entry exists in the database for the given UniqueIdentifier, Hibernate takes >10 MINUTES to return, regardless of the method used to generate and issue the query.
I have taken the inner query...
Code:
select max(Time) from Table where UniqueIdentifier='value'
...and issued it as a separate Hibernate query via all of the previously mentioned methods, and found that this is where the bottleneck lies (i.e. it takes
>10 MINUTES for this inner query to return).
Any Ideas?Is there any setting I can tweak in Hibernate, or Oracle, or Java to eliminate this insanely long (and seemingly unnecessary) query time? Or maybe there is a known issue that might cause these symptoms? I'd be happy to post whatever additional information you feel might help in debugging this strange and opaque issue. Many hours wasted on it already. Thank you!
Login
FAQ
Search