Question about PreparedStatement cache

33 views
Skip to first unread message

sdne...@gmail.com

unread,
Jul 15, 2014, 2:06:54 AM7/15/14
to jooq...@googlegroups.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

unread,
Jul 15, 2014, 7:43:26 AM7/15/14
to jooq...@googlegroups.com
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:

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

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:

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

unread,
Nov 4, 2021, 8:55:06 AM11/4/21
to jOOQ User Group
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.
Reply all
Reply to author
Forward
0 new messages