Question about PreparedStatement cache

[sdne...@gmail.com]

Hi,

i use jooq on firebird database, and i try to optimize the global performance.

my datasource is a c3p0 connection pool with statement cache activated.

after a database monitoring i have seen that a i have a lot of prepared statement that like's that :

update "TEST" set "TEST"."A" = cast(? as varchar(3))

update "TEST" set "TEST"."A" = cast(? as varchar(4))

update "TEST" set "TEST"."A" = cast(? as varchar(5))

update "TEST" set "TEST"."A" = cast(? as varchar(6))

update "TEST" set "TEST"."A" = cast(? as varchar(7))

and the code in java like that

public void test(String a) {

 getDsl().update(TEST).set(TEST.A, a).execute();

}

main(){

 test("123");

test("1234");

....

}

so because the key for statement cache is the sql of the request and as the variable is cast with length, this behaviour generates a lot of prepared statement for the same query.

is there a way to tell to jooq to generate a sql like that update "TEST" set "TEST"."A" = ?

or maybe i don't understand something and other solution will be most valuable.

thanks in advance.

ps : my dsl config is quite simple

    org.jooq.impl.DefaultConfiguration res = new DefaultConfiguration();

    res.set(connectionProvider()); // default connection provider

    res.set(org.jooq.SQLDialect.FIREBIRD);

[Lukas Eder]

Hello,

Thanks for reporting. You're right, from a SQL parser perspective, this is far from optimal. Firebird is the only database that seems to "need" a concretely-sized varchar type when casting. Unfortunately, I don't remember the reasons for this logic, which was implemented as part of back in jOOQ 2.5:

https://github.com/jOOQ/jOOQ/issues/1727

The rationale behind bind variable casting in general can be seen here:

http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness/

is there a way to tell to jooq to generate a sql like that update "TEST" set "TEST"."A" = ?

That should be done indeed, as most databases are able to infer the bind variable types in such "obvious" situations. Unfortunately, they cannot infer the types in "edge cases", like SELECT ?, ? - which is the reason why jOOQ usually casts bind variables in some databases.

I'm very open to further investigate this, though. I have registered an issue for this:

https://github.com/jOOQ/jOOQ/issues/3398

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

This issue will be fixed in jOOQ 3.16 for SQLDialect.FIREBIRD_3_0: https://github.com/jOOQ/jOOQ/issues/3398 The FIREBIRD_2_5 implementation will remain intact.