• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Hibernate slow on refresh

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, moving swiftly on to transactions now, armed with Java Persistence with Hibernate.

Being new to this its probably not too surprising that its much, much slower than my handcoded JDBC stuff, unfortunately.

It is quite a simple app really as far as the data is concerned, single user only. To that end, having the app open is considered one 'conversation' here - a Session object gets created on startup and it stays in play until the app is closed, whereupon some tidying code is called. I think that this is the right way to go about it - ie, session per conversation. HibernateUtil has been tweaked so it holds on to a reference to that Session object, so getSession() is always referring to the same object.

There is a real bottleneck at this part of the code, which updates the figures in memory with the data in the DB every so often :-



There are some threading issues too as this is triggered every minute by a refresher thread - but the problem is it takes forever. Putting show_sql in the .xml file reveals that Hibernate is making a huge number of tiny little select statements and then slowly chuntering through them - taking a good 15-20 seconds. Oddly enuogh the actual tr.commit() seems to be very fast, its the s.refresh() calls that seem to take half a second apiece.

ie

Hibernate: select transmitte0_.TX_ID as TX1_5_, transmitte0_.TX_STATUS1 as TX2_5_, transmitte0_.TX_LASTRECD as TX3_5_, transmitte0_.TX_LATEAFTER as TX4_5_, transmitte0_.TX_LOCATION as TX5_5_, transmitte0_.RX_ID as RX7_5_, transmitte0_.TX_ADDRESS as TX6_5_, transmitte0_.TXTYPE_ID as TXTYPE8_5_ from TX transmitte0_
Hibernate: select transmitte0_.TX_ID as TX1_5_0_, transmitte0_.TX_STATUS1 as TX2_5_0_, transmitte0_.TX_LASTRECD as TX3_5_0_, transmitte0_.TX_LATEAFTER as TX4_5_0_, transmitte0_.TX_LOCATION as TX5_5_0_, transmitte0_.RX_ID as RX7_5_0_, transmitte0_.TX_ADDRESS as TX6_5_0_, transmitte0_.TXTYPE_ID as TXTYPE8_5_0_ from TX transmitte0_ where transmitte0_.TX_ID=?

...repeated many times.

The session is set to flush mode manual and the batch size is set to 50. I expected this to zing through at great speed until it hit the commit(), then send it all to the DB in one volley which would be the slow bit - though not so slow the user is going to notice. Clearly it isn't doing that so I presume I'm doing something badly wrong.

I'm not entirely sure my grasp of this transaction stuff is secure anyway. I presumed that the Session object which manages all these objects sits in memory until explicitly told to contact the DB to handle some transaction, but it doesn't appear to be doing that...
 
Robert Fry
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Incidentally the HSQL database Hibernate is connecting to is about 100ms away on the internet - so there is some delay in there anyway, its not over a LAN.

This is mostly for stress testing purposes, see how it does on a slowish connection as the code might need to be able to work on something other than Ethernet LAN speeds - but 100ms is surely actually pretty fast in the great scheme of things. I presume this isn't an unusual situation for Hibernate to be used in?
 
Robert Fry
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm beginning to think Hibernate simply can't do what I want it to do... just about everything has been tried to no avail!

And the problem seems quite fundamental.

Basically the code



creates one SQL statement for every transmitter object, and it takes a good second, over the internet connection, for that statement to return. 20 transmitters -> far too slow.

I've fiddled around with things like FetchMode.JOIN, and that does condense the number of queries down greatly, but the irreducible step seems to be getting this list of transmitters. However I cut it, its one query per. And it apparently refuses to even batch those queries.



Surely there is a solution? it seems a very basic issue to me.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not sure I follow your design. Are you intending to update every Sensor and Transmitter object whenever your user uses your application?

From the JavaDocs forSession.refresh() say:


It is inadvisable to use this to implement long-running sessions that span many business tasks. This method is, however, useful in certain special circumstances. For example

* where a database trigger alters the object state upon insert or update
* after executing direct SQL (eg. a mass update) in the same session
* after inserting a Blob or Clob



Are you doing any of the above? You say your application is single user, so I'm guessing no?
 
Robert Fry
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Sturrock wrote:I'm not sure I follow your design. Are you intending to update every Sensor and Transmitter object whenever your user uses your application?

From the JavaDocs forSession.refresh() say:


It is inadvisable to use this to implement long-running sessions that span many business tasks. This method is, however, useful in certain special circumstances. For example

* where a database trigger alters the object state upon insert or update
* after executing direct SQL (eg. a mass update) in the same session
* after inserting a Blob or Clob



Are you doing any of the above? You say your application is single user, so I'm guessing no?



No, not doing any of those.

First off, some of the persistent objects are actually going into a JTree, so the first thing that happens is they all have to be loaded so the tree can be built up. The JTree building process means the data in the objects needs to be in there (so transmitters can go in the right place and have the right LED colour depending on their status), which means step 1 is loading pretty much everything in the SQL DB into memory so that JTree can be built . ATM I'm using queries like the above to do this - loading up all Transmitters or whatever into a list and then building up the tree - but they always seem to be painfully slow.

The other big problem re. slowness is that the data in the SQL database is shared and is updated by another program - this app I'm working on is concerned with displaying that data.

So the data in memory periodically needs refreshing from the SQL database. As it isn't particularly time critical stuff, a refresher thread runs every sixty seconds at the moment. Still, that means every sixty seconds it has to poll every transmitter and every sensor in the database and update each object with the new values, if any - there will likely be of the order of a hundred or so objects of type Transmitter or Sensor which need to have their values updated. Also the connection is fairly slow, at least the one used for testing is (deliberate worst case scenario) , so having a hundred single statement updates every minute really isn't good, even when it's all threaded - the connection is just jammed with constant SQL as it works through the list.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK. Well what should be happening is behind the scenes Hibernate will "Hydrate" the object, where by your mapped type will be populated based on the results on whatever JDBC result set you are using. Given your initial HQL query:

will translate into a SQL query like:

you can be sure (give or take the implementation specifics of whatever database/driver you are using) the state of your ResultSet should not need Hibernate to go back to the database. My guess is the extra selects you are seeing are the result of your association mapping. Can you post your annotated POJOs?

Also, there will be an overhead because you wrap everything in a transaction. I'd ditch that part an only use transactions when updating the data.
 
Robert Fry
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Sturrock wrote:OK. Well what should be happening is behind the scenes Hibernate will "Hydrate" the object, where by your mapped type will be populated based on the results on whatever JDBC result set you are using. Given your initial HQL query:

will translate into a SQL query like:

you can be sure (give or take the implementation specifics of whatever database/driver you are using) the state of your ResultSet should not need Hibernate to go back to the database. My guess is the extra selects you are seeing are the result of your association mapping. Can you post your annotated POJOs?

Also, there will be an overhead because you wrap everything in a transaction. I'd ditch that part an only use transactions when updating the data.



Thanks for the reply.

Here are the mapped objects. I've cut everything except the annotated bits as there is a lot of code on them which is nothing to do with Hibernate. Bear in mind that a lot of the fetch types, lazy loading being set or not, @Generated annotations, batch sizes and that sort of thing are only there as a result of me hacking at it today so don't really mean much.









(TransmitterLocation is a class as originally a transmitter could be in multiple places - that requirement is gone now but may resurface, so that one-to-one relation there might change.)







The code I'm using to initialise and refresh things is currently all just a variant on



ie "from X".

When you actually fire it up and it starts loading, with show_sql enabled you see stuff like this :-

Hibernate: select transmitte0_.TX_ID as TX1_5_, transmitte0_.TX_STATUS1 as TX2_5_, transmitte0_.TX_LASTRECD as TX3_5_, transmitte0_.TX_LATEAFTER as TX4_5_, transmitte0_.TX_LOCATION as TX5_5_, transmitte0_.RX_ID as RX7_5_, transmitte0_.TX_ADDRESS as TX6_5_, transmitte0_.TXTYPE_ID as TXTYPE8_5_ from TX transmitte0_
Hibernate: select receiver0_.RX_ID as RX1_6_0_, receiver0_.RX_NAME as RX2_6_0_ from RX receiver0_ where receiver0_.RX_ID=?
Hibernate: select transmitte0_.TXTYPE_ID as TXTYPE1_0_0_, transmitte0_.TXTYPE_NAME as TXTYPE2_0_0_ from TX_TYPE transmitte0_ where transmitte0_.TXTYPE_ID in (?, ?, ?, ?, ?)
Hibernate: select transmitte0_.TX_PLANPLACE_ID as TX1_7_6_, transmitte0_.FLOOR_ID as FLOOR5_7_6_, transmitte0_.TX_PLANPLACE_ORIGINAL_SIZE as TX2_7_6_, transmitte0_.TX_ID as TX6_7_6_, transmitte0_.TX_PLANPLACE_X as TX3_7_6_, transmitte0_.TX_PLANPLACE_Y as TX4_7_6_, floor1_.FLOOR_ID as FLOOR1_3_0_, floor1_.BUILDING_ID as BUILDING2_3_0_, floor1_.FLOOR_TREEINDEX as FLOOR3_3_0_, floor1_.FLOOR_IMAGE_SOURCE as FLOOR4_3_0_, floor1_.FLOOR_NAME as FLOOR5_3_0_, floor1_.FLOOR_ORIGIN_X as FLOOR6_3_0_, floor1_.FLOOR_ORIGIN_Y as FLOOR7_3_0_, floor1_.FLOOR_SCALE_MULTIPLIER as FLOOR8_3_0_, transmitte2_.TX_ID as TX1_5_1_, transmitte2_.TX_STATUS1 as TX2_5_1_, transmitte2_.TX_LASTRECD as TX3_5_1_, transmitte2_.TX_LATEAFTER as TX4_5_1_, transmitte2_.TX_LOCATION as TX5_5_1_, transmitte2_.RX_ID as RX7_5_1_, transmitte2_.TX_ADDRESS as TX6_5_1_, transmitte2_.TXTYPE_ID as TXTYPE8_5_1_, receiver3_.RX_ID as RX1_6_2_, receiver3_.RX_NAME as RX2_6_2_, sensorlist4_.TX_ID as TX7_8_, sensorlist4_.Sensor_ID as Sensor1_8_, sensorlist4_.Sensor_ID as Sensor1_4_3_, sensorlist4_.Sensor_AlmHi as Sensor2_4_3_, sensorlist4_.Sensor_AlmLo as Sensor3_4_3_, sensorlist4_.Sensor_PncHi as Sensor4_4_3_, sensorlist4_.Sensor_PncLo as Sensor5_4_3_, sensorlist4_.TX_ID as TX7_4_3_, sensorlist4_.SType_ID as SType8_4_3_, sensorlist4_.Sensor_LastValue as Sensor6_4_3_, sensortype5_.STYPE_ID as STYPE1_1_4_, sensortype5_.SType_EU as SType2_1_4_, sensortype5_.SType_Format as SType3_1_4_, sensortype5_.SType_MaxValue as SType4_1_4_, sensortype5_.SType_MinValue as SType5_1_4_, sensortype5_.SType_Type as SType6_1_4_, transmitte6_.TXTYPE_ID as TXTYPE1_0_5_, transmitte6_.TXTYPE_NAME as TXTYPE2_0_5_ from TX_PLANPLACE transmitte0_ inner join FLOOR floor1_ on transmitte0_.FLOOR_ID=floor1_.FLOOR_ID inner join TX transmitte2_ on transmitte0_.TX_ID=transmitte2_.TX_ID left outer join RX receiver3_ on transmitte2_.RX_ID=receiver3_.RX_ID left outer join Sensor sensorlist4_ on transmitte2_.TX_ID=sensorlist4_.TX_ID left outer join Sensor_Type sensortype5_ on sensorlist4_.SType_ID=sensortype5_.STYPE_ID left outer join TX_TYPE transmitte6_ on transmitte2_.TXTYPE_ID=transmitte6_.TXTYPE_ID where transmitte0_.TX_ID=?

etc.etc.etc.


There are a lot of joins in there, as I've been playing with FetchType. It does make it a bit faster doing it that way, but its still far too slow.

SensorType and maybe TransmitterLocation could be (should be?) @Embeddable I imagine, not sure if that is involved with this problem though.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah. When you said your were using fetch stuff, I assuming you were using it in your HQL. One annoying feature of Hibernate is if you use an HQL query the majority of your fetching annotations/mappings will be ignored. Try using a Criteria query or the "eager fetch join" stuff in your HQL.
 
Robert Fry
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Sturrock wrote:Ah. When you said your were using fetch stuff, I assuming you were using it in your HQL. One annoying feature of Hibernate is if you use an HQL query the majority of your fetching annotations/mappings will be ignored. Try using a Criteria query or the "eager fetch join" stuff in your HQL.



Well, thats a revelation!

I'm using something like this now :-



That seems to work a lot better. It's down to 7 queries on startup now - still takes a while but at least it doesn't hang for a minute anymore.

I shall play some more and see if I can reduce it still further.

Thanks for your help!
 
reply
    Bookmark Topic Watch Topic
  • New Topic