I have a select query which takes 10 min to complete as it runs thru 10M records. When I run thru TOAD or program using normal JDBC connection I get the results back, but while running a Job which uses Hibernate as ORM does not return any results. It just hangs up ...even after 45 min? Please help
A:
Are you saying you trying to retrieve 10M records using an ORM like hibernate? If this is the case you have one big problems, you need to redesign your application because this is not going to work, and about why it hangs up, well, I bet is because it runs out of memory.
pedromarce
2009-10-22 19:39:51
Sorry about that, the query returns only counts for number of records satisfying the criteria, so we dont get all the records into Memory. It is query itself which takes time. Given that, this is a batch job, we are expecting it to take some time. But once the prepared statement is generated, we dont get the counts back even after the normal runtime or the query in toad (10 mins).
vijay
2009-10-22 19:56:44
A:
Have you enabled SQL output for Hibernate? You need to set hibernate.show_sql to true in order to do that.
Once that's done, compare the generated SQL with the one you're been running through TOAD. Are they exactly the same or not?
I'm going to venture a guess here and say they're not because once SQL is generated Hibernate does nothing fancy - connection is taken from a pool; prepared statement is created and executed - so it should be no different from JDBC.
Thus the question most likely is how can your HQL be optimized. If you need any help with that you'll have to post the HQL in question as well as appropriate mappings / table schemas. Running explain on query would help as well.
ChssPly76
2009-10-22 20:29:18
Thanks for the suggestion, I am running the same query in TOAD which was generated by Hibernate.
vijay
2009-10-22 21:18:04
Can you post the query? Both HQL and SQL? And the code where you're running it in Hibernate? I have the hardest time imagining what would take Hibernate longer to run the **same** query.
ChssPly76
2009-10-22 21:33:37
thanks for responding back, I have a Criteria object which converts into SQL query.The query is a count of objects matching a criteria. When I run the program in dev environment which has like 50,000 records in DB, I get the result. But when I run the same program in Perf. env which has like 10M records the query never comes back. Although, running the same query from TOAD gives results. My query is something like this.
vijay
2009-10-22 22:33:46
select count(distinct A_.A_ID) as y0_, A_.C_ID as y1_ from A A_ inner join B B_ on A_.A_ID=B_.A_ID inner join C C_ on B_.B_ID=C_.B_ID inner join D D_ on B_.A_ID=D_.A_ID inner join E E_ and B_.A_ID=E_.A_ID inner join F F_ on E_.E_ID=F_.E_ID where A_.CRTE_DT between ? and ? and D_.IS_DSW=? and D_.ERR_CD=? and ( C_.S_CD=? or C_.S_CD=? ) group by A_.C_ID
vijay
2009-10-22 22:34:56
And you are sure that the query you're running in TOAD uses the same parameters as the ones Hibernate binds? Did you enable parameter logging to verify that? Another problem with this query is that you're not returning a **single** count; you're returning a grouped-by list. How many elements are in that list? TOAD would not return the whole thing (it will paginate) but Hibernate would. That means instantiating X arrays (or objects if you're using ResultTransformer) which can be rather expensive for big X. Not to mention the time to transfer that data over the wire.
ChssPly76
2009-10-22 22:44:32
thanks for the response, This is the same query I run from TOAD. More over there are very few results obtained at max 10 items. the query is not paginated, also I tried to run a native SQL from my App and still takes long time (atleast 4x times compared to the actual query.)...I wonder if this has to do something with JDBC client. Any response would be appreciated
vijay
2009-10-26 19:26:23
Profile it - that's about all I can suggest. Set hibernate log to DEBUG and profile your query. If you don't see the obvious problem then, post both the profile results and the log to your question.
ChssPly76
2009-10-26 19:44:16