Hmmm. Thats a good analysis, thanks. I don't really see why Hibernate is calling setQueryTimeout() if it's about to close the statement - that makes no sense at all. It sounds like it's trying to work around a bug in some other DB, and consequently causing problems here. That is something that should rather be fixed in Hibernate.
We go back to the information_schema, because that's where we store the persistent settings for things like this. We have to do this because otherwise, if change the global query timeout, it would not become "visible" to Statement objects until new ones were created, which is also a problem. On Sat, Aug 18, 2012 at 6:52 PM, Steve Ash <[email protected]> wrote: > When examining the trace of an application that I am building using Hibernate > 4.1 and H2 1.3.168 I noticed that SELECT VALUE FROM > INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'QUERY_TIMEOUT' was taking up quite > a bit of my overall execution time: > > > -- 69% 69% 4558 3972 14982 select spilledsna0_.sequentialId as > sequenti1_2_, spilledsna0_.entityId as entityId2_, spilledsna0_.snapshot as > snapshot2_ from SpilledSnapshot spilledsna0_ where spilledsna0_.sequentialId > in (select spilledsna1_.entitySequentialId from SpilledSnapshotInBlock > spilledsna1_ where spilledsna1_.blockId=?); > -- 19% 89% 1284 23954 23954 SELECT VALUE FROM > INFORMATION_SCHEMA.SETTINGS WHERE NAME=?; > -- 6% 96% 436 14982 14982 insert into SpilledSnapshotInBlock > (entitySequentialId, blockId) values (?, ?); > > > After doing a little digging I noticed that when hibernate closes > PreparedStatements or Statements it calls: > > if (stmt.getQueryTimeout() != 0) stmt.setQueryTimeout(0) > > H2 delegates this getQueryTimeout to JdbcConnection.getQueryTimeout() which > issues this INFORMATION_SCHEMA query. > > I think its reasonable for hibernate to issue such queries, but not sure that > it makes sense to always go back to the information schema metadata -- I mean > how frequently is this really changing? > > To see what kind of difference it made for me, I subclassed org.h2.Driver, > then overrode the connect method to return a cglib proxied JdbcConnection > that returned back the fixed value of 0 for getQueryTimeout (because I never > use query timeouts in my app). > > This reduced my total execution time by 24%, which is not too bad for > something simple like this. > > Not sure if this is "working as designed", but figured that it might be of > interest to the group. > > Steve > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/rz_OaH7KlxEJ. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
