CODEWITHSUNDEEP
javasqljooq
user3278460
user3278460

Reputation:

JOOQ slow code generation

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

Upvotes: 2

Views: 757

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

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

Upvotes: 1

Related Questions