Static Queries

[Robert DiFalco]

I'm really enjoying using JOOQ but I have a question about static queries that I use over and over again.

I'm not sure why I need to parse these every time they are executed because they never change. I was thinking that in my DAO constructor I would just create them once with #getSQL and then use ResultQuery with saved the string. Of course doing that I lose all the type bindings. 

I thought that I should be able to save ResultQuery objects instead and reuse them but I think (and I'm not sure why) that they are tied to the connection. Or am I missing something? Is there a way to cache the type safe queries without any database connection context? 

Thanks!

[Lukas Eder]

Hello,

There's a short section on optimisation in the manual:

http://www.jooq.org/doc/latest/manual/sql-execution/performance-considerations

Essentially it reads: Don't :)

(unless you measured stuff and *know* you have a bottleneck).

If you still want to generate SQL strings only once, we generally suggest you extract the string and its bind values and execute them via:

- DSLContext.fetch() or DSLContext.execute()

- JDBC directly

- Some other tool

You can extract the SQL and bind values using:

- Query.getSQL() (http://www.jooq.org/javadoc/latest/org/jooq/Query.html#getSQL--)

- Query.getBindValues() (http://www.jooq.org/javadoc/latest/org/jooq/Query.html#getBindValues--)

As of jOOQ 3.x, Query objects aren't thread safe and must not be used by several threads at the same time.

Hope this helps,
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.

[Robert DiFalco]

Thanks, yeah I read that. What I'm doing now seems to work pretty well. I just save the #getSQL and then when I use I do a sqlContext.resultQuery(sql, [values,...]);

Are you thinking (a) this isn't a good idea or (b) that I am not saving any cycles? The main reason I'm doing it is that I have several unit tests that (unfortunately) require some timing to check timeouts and such. The first few will usually fail because the first time I construct the query its spends a bit of time creating that query. So whichever test ran first would fail. Extracting the string as above seemed to fix the warm-up issue. 

R.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/7jqbm98_Ga8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2015-06-01 18:37 GMT+02:00 Robert DiFalco <robert....@gmail.com>:

Thanks, yeah I read that. What I'm doing now seems to work pretty well. I just save the #getSQL and then when I use I do a sqlContext.resultQuery(sql, [values,...]);

Are you thinking (a) this isn't a good idea or (b) that I am not saving any cycles? The main reason I'm doing it is that I have several unit tests that (unfortunately) require some timing to check timeouts and such. The first few will usually fail because the first time I construct the query its spends a bit of time creating that query. So whichever test ran first would fail. Extracting the string as above seemed to fix the warm-up issue. 

Aha, I see. Well, the first query that uses generated meta data will suffer from the initialisation overhead for all the metadata. Unless you're doing some magic tricks with classloaders, jOOQ's metadata blocks the classloader completely in order to be fully class-loaded and intialised in one go. If that's bothering you in your tests, you might want to bootstrap 1-2 queries in your tests' @BeforeClass methods.

An even cleaner solution would be to bootstrap each individual test before doing the benchmarks. I suspect that JMH provides tools for such bootstrapping, in case you're using JMH

I wouldn't proceed with storing the SQL strings for all of your application, because you probably won't save too many cycles, but you might have a bit of overhead in your work. Also, as with any cache, you'll risk regressions because of stale caches - i.e. there might be a case where you pre-generate SQL that isn't really static.

Of course, if you feel more at ease this way, and you've already done the work, that'll be fine, too.

[Robert DiFalco]

Yes, exactly that is the problem. Yeah it was just like three lines of code to move the sqlContext.select to the constructor save it as getSQL in the DAO singleton ctor and then just use resultQuery instead in the actual code. Writing warm-up code was actually more tedious but I'll see if I can do it in a more logical way. These aren't benchmarked unit tests so I'm not using JMH. And I didn't want to have to pre-run queries for every single unit test in this Test class (there is a lot of coverage). It's just some IPC system tests where I had to check out timeouts, waits, errors, and I want to keep the "expect" timeout as short as possible so they will run fast. 

Thanks for the input. 

--

[Lukas Eder]

2015-06-01 19:06 GMT+02:00 Robert DiFalco <robert....@gmail.com>:

Yes, exactly that is the problem. Yeah it was just like three lines of code to move the sqlContext.select to the constructor save it as getSQL in the DAO singleton ctor and then just use resultQuery instead in the actual code. Writing warm-up code was actually more tedious but I'll see if I can do it in a more logical way. These aren't benchmarked unit tests so I'm not using JMH. And I didn't want to have to pre-run queries for every single unit test in this Test class (there is a lot of coverage). It's just some IPC system tests where I had to check out timeouts, waits, errors, and I want to keep the "expect" timeout as short as possible so they will run fast. 

Thanks for the input. 

OK, I guess you've found the optimal solution, then :)

Cheers,

Lukas 

[Robert DiFalco]

While I have you, is there any simple way to speed up the code generation phase in the maven plugin? mvn test can take a while doing the generation each time. I only have one schema, does adding:

<outputSchemaToDefault>true</outputSchemaToDefault>

Save me any time? Anything else I can look at? I'm not generating beans or anything like that. 

--

[Lukas Eder]

I don't think that the code generation itself is the bottleneck. Between subsequent generations, only deltas are re-generated (class additions / removals / changes) to allow for incremental compilation of generated code.

Probably, the queries against the dictionary views / information_schema might not be optimal. Did you measure things at your side? Is there anything particular that you found to be slow? Are you generating code against a local database on your computer, or a remote one - possibly cloud hosted one?

One way to speed up code generation would be to use jOOQ's XMLDatabase, or implement your own org.jooq.util.Database from jOOQ meta, that is optimised for your specific schema. Let me know if that sounds like an option to you, and I'll explain more

2015-06-01 22:38 GMT+02:00 Robert DiFalco <robert....@gmail.com>:

While I have you, is there any simple way to speed up the code generation phase in the maven plugin? mvn test can take a while doing the generation each time. I only have one schema, does adding:

<outputSchemaToDefault>true</outputSchemaToDefault>

Save me any time? Anything else I can look at? I'm not generating beans or anything like that. 

No, that shouldn't have any impact on the code generation speed. It is just used to omit the schema in generated output, which is useful in single-schema / multi-tenant environements.

[Robert DiFalco]

Interesting, I'll look into those classes to see what they do.

One thing that occurs to me is that it would be nice to be able to customize the code that decides if java files need to be changed/created. For example, I use Flyway so I have a hash of my latest schema changes. It would be nice to be able to do a quick lookup on the last hash Jooq generated for (and the Jooq version) to see if anything needs to be done. I'm fine with regenerating if generator configuration options change. That could make things very fast. Yes, it is a local database. 

[Lukas Eder]

Hmm, interesting. I would have thought this is documented in the manual, but it appears it isn't. You can use <schemaVersionProvider/> for this:

<!--

A custom version number that, if available, will be used to assess whether the above

<inputSchema/> will need to be regenerated.

There are three operation modes for this element:

- The value is a class that can be found on the classpath and that implements

org.jooq.util.SchemaVersionProvider. Such classes must provide a default constructor

- The value is a SELECT statement that returns one record with one column. The

SELECT statement may contain a named variable called :schema_name

- The value is a constant, such as a Maven property

Schema versions will be generated into the javax.annotation.Generated annotation on

generated artefacts.

-->

<element name="schemaVersionProvider" type="string" default="" minOccurs="0" maxOccurs="1"/>

(from http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd)

The code generator won't regenerate the schema if it has the same version as provided by the SchemaVersionProvider... One use-case is precisely to specify a query that reads Flyway's version table. For more info, see this feature request here:

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

E.g.

 <schemaVersionProvider>SELECT :schema_name || '_' || MAX("version") FROM "schema_version"</schemaVersionProvider>

I've added an issue to document this in the manual:

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

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.

[Lukas Eder]

(I'm starting to wonder if there's any convenience that we haven't implemented in the code generator yet ;) )

[Robert DiFalco]

Hmmm...I added that property to the database section and every time I run "mvn test" I get this output (and it regenerates).

[INFO] Generating schemata      : Total: 1

[INFO] No previous version available for schema public. Regenerating.

[INFO] Generating schema        : Public.java

I'm using flyway and my pom.xml looks like this:

<plugin>
    <!-- Specify the maven code generator plugin -->
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.6.1</version>

    <!-- The plugin should hook into the generate goal -->
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <!-- Specify the plugin configuration.
         The configuration format is the same as for the standalone code generator -->
    <configuration>
        <!-- JDBC connection parameters -->
        <jdbc>
            <driver>org.postgresql.Driver</driver>
            <url>${database.jdbcUrl}</url>
            <user>${database.username}</user>
            <password>${database.password}</password>
        </jdbc>

        <!-- Generator parameters -->
        <generator>
            <name>org.jooq.util.JavaGenerator</name>
            <database>
                <name>org.jooq.util.postgres.PostgresDatabase</name>
                <includes>.*</includes>
                <inputSchema>public</inputSchema>
                <!--<outputSchemaToDefault>true</outputSchemaToDefault>-->
                <schemaVersionProvider>SELECT :schema_name || '_' || MAX("version") FROM "schema_version"</schemaVersionProvider>                        </database>
            <target>
                <packageName>com.waiapp.server.jooq</packageName>
                <directory>src/main/java</directory>
            </target>
            <generate>
                <deprecated>false</deprecated>
            </generate>
        </generator>
    </configuration>
</plugin>

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/7jqbm98_Ga8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Huh, what does the generated code of your schema look like?

[Robert DiFalco]

This?

@Generated(
   value = {
      "http://www.jooq.org",
      "jOOQ version:3.6.1",
      "schema version:public_2.2"
   },
   date = "2015-06-02T14:47:42.719Z",
   comments = "This class is generated by jOOQ"
)

[Lukas Eder]

Looks good. I suspect the annotation has been newly formatted in a recent release, leading to a regression here... I'll look into this, soon.

[Robert DiFalco]

No worries, let me know if you need anything else.

[Lukas Eder]

Indeed a regression. Good thing we discovered it, and thanks for reporting!

I've registered an issue for this:

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