JOOQ slow code generation

2
votes

Are there any parameters, which can turn on/off execution of next query during jooq code generation? 

SELECT "SYS"."ALL_OBJECTS"."OWNER",
     "SYS"."ALL_OBJECTS"."OBJECT_NAME",
     "SYS"."ALL_OBJECTS"."OBJECT_ID",
     "SYS"."ALL_PROCEDURES"."AGGREGATE"
FROM "SYS"."ALL_OBJECTS"
     LEFT OUTER JOIN "SYS"."ALL_PROCEDURES"
        ON (    "SYS"."ALL_OBJECTS"."OWNER" =
                   "SYS"."ALL_PROCEDURES"."OWNER"
            AND "SYS"."ALL_OBJECTS"."OBJECT_NAME" =
                   "SYS"."ALL_PROCEDURES"."OBJECT_NAME")
WHERE (    UPPER ("SYS"."ALL_OBJECTS"."OWNER") IN ( 'MYSCHEMA')
      AND "SYS"."ALL_OBJECTS"."OBJECT_TYPE" IN ( 'FUNCTION', 'PROCEDURE'))
ORDER BY "SYS"."ALL_OBJECTS"."OWNER" ASC,
     "SYS"."ALL_OBJECTS"."OBJECT_NAME" ASC,
     "SYS"."ALL_OBJECTS"."OBJECT_ID" ASC

On database with large number of schemas and objects it tooks about one hour to be executed

1
javasqljooq
Hmm, OK, that UPPER(OWNER) expression in there probably prevents using a useful index on the column... We'll look into it. What Oracle database version are you using, and what jOOQ version? For this particular query, you could turn off the <includeRoutines/> and <includePackages/> flags in the <database/> configuration: jooq.org/doc/latest/manual/code-generation/codegen-advanced/… - Lukas Eder
Oracle 11.2.0.4, JOOQ 3.7.2. Seems that flags, you mentioned, are for JOOQ 3.9? - user3278460
Hmm, the flags were added in jOOQ 3.8, but the documentation currently doesn't reflect that. A workaround would be to override the JavaGenerator and its generatePackages() and generateRoutines() methods to be empty. I'll provide an answer with this information, soon - Lukas Eder
One more question (if you're allowed to publish this info): What does this query yield for you (otherwise, a PM would do as well)? SELECT object_type, count(*) FROM all_objects GROUP BY object_type ORDER BY object_type; - Lukas Eder

1 Answers

1
votes

One major issue with the query run by jOOQ is the UPPER(OWNER) expression. This was introduced with jOOQ 2.4 (#1418) to prevent misconfigurations where users accidentally use lower case schema names. The feature was based on an erroneous assumption that case-sensitive users are impossible. They are certainly possible (even if rare), so #1418 was wrong. I've created two issues for this problem:

  • #5989: Fix the performance issue by avoiding functions on the OWNER column
  • #5990: Re-enact case-sensitive schema names

In the meantime, you have some possible workarounds:

Pre jOOQ 3.8

You can always override the JavaGenerator from jooq-codegen and re-implement some methods including generatePackages() and generateRoutines() to be empty. This way, the relevant code will not be executed at all.

Of course, this means you won't get any generated packages and routines.

Post jOOQ 3.8

There is a new configuration option where you can do the same as above configuratively:

<configuration>
  <generator>
    <database>
      <includeRoutines>false</includeRoutines>
      <includePackages>false</includePackages>
      ...

See also: https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-include-object-types