Hi, I will fix that.
In theory the query timeout could change whenever a statement is executed (you can set the query timeout using a statement). The JDBC implementation doesn't fully parse the SQL statement, so it doesn't try to detect the statement SET QUERY_TIMEOUT was executed. Maybe (not sure if that's possible) it could even change within a trigger, without explicitly using the word QUERY_TIMEOUT in a statement. That would be sneaky. So in theory the database currently does the right thing. However, I understand it's a performance problem now that Hibernate calls this method so often. It's unlikely we can convince the Hibernate team to not call it, because I guess they call it for some (more or less) good reason. Unless somebody has a better idea, I will change Statement.getQueryTimeout as follows: For performance reasons, only the first call to this method will query the database backend. If the query timeout was changed in another way than calling setQueryTimeout, this method will always return the initial value. How to implement this: the class JdbcConnection will have a new field "queryTimeoutCache", initially set to -1. This value is set when calling setQueryTimeout. When calling getQueryTimeout(), if the value is -1, then it behaves as it does now (asking the database). If not, it will return this value. Regards, Thomas On Saturday, August 18, 2012, Steve Ash 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]<javascript:;> > . > To unsubscribe from this group, send email to > [email protected] <javascript:;>. > 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.
