Hi,
In my project we use H2 table functions to read data from other databases.
I found out that H2 calls our table functions more often than expected,
which can lead to serious performance problems if the execution of the SQL
from the other database is quite expensive.
The sample code below creates a table function, and creates a wrapping
view for it.
During creation of the view, H2 calls the table function 3 times, and when
selecting from the view again 4 times.
I also tried to create the table function with the DETERMINISTIC flag but
that made things even worse (6 call when creating the view, 7 when
selecting from it)
Is it really required that H2 call the function more than once for each
operation?
Is it possible to configure this some how, or is there a bug in my code?
Code:
public class Test {
public static void main(String[] args) throws SQLException {
new Test().run();
}
private void run() throws SQLException {
Connection connection = connect();
Statement st = connection.createStatement();
st.execute("create alias MYTF for \"Test.getResultSet\"");
System.out.println("creating view...");
st.execute("create view MVIEW as select * from MYTF()");
System.out.println("selecting from view...");
st.executeQuery("select * from MVIEW");
st.execute("create alias MYTF2 deterministic for
\"Test.getResultSet\"");
System.out.println("creating view (deterministic table
function)...");
st.execute("create view MVIEW2 as select * from MYTF2()");
System.out.println("selecting from view (deterministic table
function)...");
st.executeQuery("select * from MVIEW2");
}
public Connection connect() throws SQLException {
String connectString = "jdbc:h2:mem:" + "H2" +
";DB_CLOSE_DELAY=-1;QUERY_CACHE_SIZE=0";
try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException ex) {
throw new SQLException(ex.getMessage(), ex);
}
Connection connection = DriverManager.getConnection
(connectString);
connection.setTransactionIsolation(Connection.
TRANSACTION_READ_UNCOMMITTED);
return connection;
}
public static ResultSet getResultSet() throws SQLException {
System.out.println("call to getResultSet()");
return new Test().connect().createStatement().executeQuery("select
1 as x");
}
}
Output:
creating view...
call to getResultSet()
call to getResultSet()
call to getResultSet()
selecting from view...
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
creating view (deterministic table function)...
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
selecting from view (deterministic table function)...
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
call to getResultSet()
Kind regards and thanks in advance
Christoff Schmitz
F I N A R I S
Financial Software Partner GmbH
Sömmerringstrasse 23
60322 Frankfurt am Main
Fon: +49 (0)69 / 254 98 - 24
Mobile: +49 (0)176 / 206 34 186
Fax: +49 (0)69 / 254 98 - 50
eMail: mailto:christoff.schm...@finaris.de
www: http://www.finaris.de und http://www.rapidrep.com
================================================================================================================
Disclaimer
The information contained in this e - mail and any attachments ( together
the "message") is intended for the addressee only and
may contain confidential and/or privileged information. If you have
received the message by mistake please delete it and notify
the sender and do not copy or distribute it or disclose its contents to
anyone.
FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322
Frankfurt/Main, Germany
Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf.
Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl
================================================================================================================
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
