Hi,
The function is called multiple times, that's true. In most cases it is
called to get the list of columns, not to get the data. You can detect this
within the method call, as documented in
http://h2database.com/html/features.html#user_defined_functions "Using a
Function as a Table".
Example:
public static ResultSet getResultSet(Connection conn)
throws SQLException {
String url = conn.getMetaData().getURL();
if (url.equals("jdbc:columnlist:connection")) {
System.out.println("get the list of columns");
SimpleResultSet rs = new SimpleResultSet();
rs.addColumn("id", Types.INTEGER, 10, 0);
return rs;
}
System.out.println("get the data");
new Exception().printStackTrace(System.out);
SimpleResultSet rs = new SimpleResultSet();
rs.addColumn("id", Types.INTEGER, 10, 0);
rs.addRow(1);
rs.addRow(2);
rs.addRow(3);
return rs;
}
I think this should solve the problem. Having a cache within the database
should not be needed in this case.
By the way: deterministic table functions are called twice to get the data,
because the database tries to optimize the statement (pre-calculate
constants) - in this case the result is actually worse than if you declare
it non-deterministic.
Regards,
Thomas
On Sun, Oct 20, 2013 at 6:27 PM, Thomas Mueller <
[email protected]> wrote:
> Hi,
>
> I will check if it is possible to call the function less often. If that's
> possible, then no cache would be needed.
>
> Regards,
> Thomas
>
>
>
> On Fri, Oct 18, 2013 at 2:06 PM, Thomas Mueller <
> [email protected]> wrote:
>
>> Hi,
>>
>> I'm sorry but I will revert the change. I will soon try to release a new
>> version so such a change is a bit dangerous. Also I wonder whether it is
>> really better (faster) to have a cache in the database engine. Do we have a
>> benchmark? Possibly the equality test (Arrays.deepEquals) might not always
>> be correct; I'm not sure if this is a problem or not (maybe not).
>>
>> Regards,
>> Thomas
>>
>> The reverse patch is:
>> http://h2database.com/p.html#12a8e15f4abbd270c6861bc31b2ccc88
>>
>> Index: src/main/org/h2/engine/FunctionAlias.java
>> ===================================================================
>> --- src/main/org/h2/engine/FunctionAlias.java (revision 5243)
>> +++ src/main/org/h2/engine/FunctionAlias.java (working copy)
>> @@ -279,7 +279,7 @@
>> * Each method must have a different number of parameters however.
>> * This helper class represents one such method.
>> */
>> - public class JavaMethod implements Comparable<JavaMethod> {
>> + public static class JavaMethod implements Comparable<JavaMethod> {
>> private final int id;
>> private final Method method;
>> private final int dataType;
>> @@ -287,11 +287,6 @@
>> private boolean varArgs;
>> private Class<?> varArgClass;
>> private int paramCount;
>> - /**
>> - * Cache the value of the last call if the function is
>> deterministic.
>> - */
>> - private Object[] previousParams;
>> - private Value previousReturnValue;
>>
>> JavaMethod(Method method, int id) {
>> this.method = method;
>> @@ -403,11 +398,6 @@
>> params[p] = o;
>> }
>> }
>> - if (deterministic) {
>> - if (previousParams != null &&
>> Arrays.deepEquals(previousParams, params)) {
>> - return previousReturnValue;
>> - }
>> - }
>> boolean old = session.getAutoCommit();
>> Value identity = session.getLastScopeIdentity();
>> boolean defaultConnection =
>> session.getDatabase().getSettings().defaultConnection;
>> @@ -419,6 +409,9 @@
>>
>> Driver.setDefaultConnection(session.createConnection(columnList));
>> }
>> returnValue = method.invoke(null, params);
>> + if (returnValue == null) {
>> + return ValueNull.INSTANCE;
>> + }
>> } catch (InvocationTargetException e) {
>> StatementBuilder buff = new
>> StatementBuilder(method.getName());
>> buff.append('(');
>> @@ -431,21 +424,11 @@
>> } catch (Exception e) {
>> throw DbException.convert(e);
>> }
>> - Value ret;
>> - if (returnValue == null) {
>> - ret = ValueNull.INSTANCE;
>> - } else {
>> - if
>> (Value.class.isAssignableFrom(method.getReturnType())) {
>> - ret = (Value) returnValue;
>> - } else {
>> - ret = DataType.convertToValue(session,
>> returnValue, dataType);
>> - }
>> - }
>> - if (deterministic) {
>> - previousParams = params;
>> - previousReturnValue = ret;
>> + if
>> (Value.class.isAssignableFrom(method.getReturnType())) {
>> + return (Value) returnValue;
>> }
>> - return ret;
>> + Value ret = DataType.convertToValue(session,
>> returnValue, dataType);
>> + return ret.convertTo(dataType);
>> } finally {
>> session.setLastScopeIdentity(identity);
>> session.setAutoCommit(old);
>>
>>
>>
>> On Fri, Oct 18, 2013 at 10:27 AM, Noel Grandin <[email protected]>wrote:
>>
>>>
>>> On 2013-10-17 18:10, [email protected] wrote:
>>>
>>>
>>> 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.
>>>
>>>
>>> Thanks for the test-case, this is fixed in SVN now.
>>> Note that we only implement a single-value cache, so it's not going to
>>> eliminate all unnecessary calls, but it should catch most of them.
>>>
>>> --
>>> 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 [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at http://groups.google.com/group/h2-database.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>
>>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
