Skip to content

Performance benchmark to show difference between regular execution and reusing PreparedStatements #1625

Closed
jjYBdx4IL opened this Issue Jul 25, 2012 · 10 comments

2 participants

@jjYBdx4IL

Have done some benchmarking and I'm now wondering is there is any prepareStatement equivalent in jOOQ? What I want to achieve: not re-creating the same SQL over and over again.

The benchmarks I did, seem to reveal that the SQL construction is crippling performance immensely. The sample query takes 1.7ms to execute, whereas the PreparedStatement equivalent takes only 0.09ms for execution. Before I start using the getSQL() method all over: is there any solution with jOOQ already available? Or is my benchmark measuring something totally different?

        int n = 10000;

        // no trick applied
        Map<Long, Record> tfmRecords;
        Long start1 = System.currentTimeMillis();
        for(int i=0; i<n; i++) {
            // 1.7ms (n=10.000)
            db.select().from(TFM)
                    .where(TFM.ID.equal(taId)
                      .and(TFM.EXT.isNull().or(TFM.EXT.notEqual(randLong.toString())))
                    ).execute();
        }
        Long end1 = System.currentTimeMillis();

        // only repeat execute() calls
        SelectConditionStep scs = db.select().from(TFM)
                .where(TFM.ID.equal(taId)
                    .and(TFM.EXT.isNull().or(TFM.EXT.notEqual(randLong.toString())))
                );
        Long start2 = System.currentTimeMillis();
        for(int i=0; i<n; i++) {
            // 1.4ms (n=10.000)
            scs.execute();
        }
        Long end2 = System.currentTimeMillis();

        // use the SQL directly via prepared stmts
        String sql = scs.getSQL(false);
        System.out.println(sql);
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setLong(1, taId);
        pst.setString(2, randLong.toString());
        Long start3 = System.currentTimeMillis();
        for(int i=0; i<n; i++) {
            // 0.09ms (n=10.000)
            pst.executeQuery();
        }
        Long end3 = System.currentTimeMillis();

        // use the SQL directly via static stmts
        sql = scs.getSQL(true);
        System.out.println(sql);
        Statement st = con.createStatement();
        Long start4 = System.currentTimeMillis();
        for(int i=0; i<n; i++) {
            // 0.6ms (n=10.000)
            st.executeQuery(sql);
        }
        Long end4 = System.currentTimeMillis();
@lukaseder
Java Object Oriented Querying member

Hi,

Thanks for your benchmark. This is probably related to #385. Currently, jOOQ doesn't keep any PreparedStatement objects open for multiple executions. This means, that every query re-execution will need at least one soft-parse, unless the underlying database is sophisticated enough, to recognise identical SQL strings prior to parsing. What would the following benchmark result in? It corresponds to what jOOQ does, internally:

    // use the SQL directly via prepared stmts (alternative)
    String sql = scs.getSQL(false);
    System.out.println(sql);
    Long start3 = System.currentTimeMillis();
    for(int i=0; i<n; i++) {
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setLong(1, taId);
        pst.setString(2, randLong.toString());
        // 0.09ms (n=10.000)
        pst.executeQuery();
        pst.close();
    }
    Long end3a = System.currentTimeMillis();

Nevertheless, I will re-use your benchmark for some YourKit profiling, to see if there is any obvious flaw. What database did you test this against?

@jjYBdx4IL

Unattended installation via https://gist.github.com/3003417

@lukaseder
Java Object Oriented Querying member

OK, I see. Well, a good part from the overhead comes from rendering/binding and the necessary traversal through the jOOQ object hierarchies. There's probably room for improvement along the lines of calculating hashCode() values, which I'm going to analyse with #1626.

I'll keep you posted with my profiling findings

@lukaseder lukaseder added a commit that referenced this issue Jul 27, 2012
@lukaseder lukaseder [#1625] Performance benchmark to show difference between regular
execution and reusing PreparedStatements
a120437
@lukaseder
Java Object Oriented Querying member

I can reproduce the problems indicated by your benchmark. The results I get against an H2 database can be seen here:

12:54:09,069  INFO - Benchmark start          : Total: 0.037ms
12:54:11,088  INFO - Full re-execution        : Total: 2.018s, +2.018s
12:54:11,962  INFO - Reuse select             : Total: 2.893s, +874.458ms
12:54:11,965  INFO - Reuse SQL String         : Total: 2.895s, +2.632ms

I will close this issue as fixed meaning the benchmark was added to the integration test suite.
I will reference this issue from other issues, as soon as I detect problems through profiling

@lukaseder lukaseder closed this Jul 27, 2012
@lukaseder
Java Object Oriented Querying member

An interesting finding from #1633 and #1634 is that non-negligible time is wasted in fetching records from the database. That fact is bypassed when using PreparedStatement directly

@jjYBdx4IL

I'm thinking about dynamically creating non-temporary stored procedures in Sybase ASE using the SQLs returned by the jOOQ getSQL() method, but I haven't tested the performance impact of that yet.

@jjYBdx4IL

The most simplistic solution would be, of course, to just combine a connection pool with a preparedstatement cache.

@lukaseder
Java Object Oriented Querying member

Sounds interesting! Unfortunately, #1507 has not yet been implemented (Sybase ASE stored procedure support). I'd be curious about any findings with your approach though. I haven't thought about such an approach yet.

About the PreparedStatement cache in a connection pool: You'd probably see this being managed externally to jOOQ right now? i.e. let jOOQ render the SQL once for an initial PreparedStatement creation, and then operate only on JDBC objects? I'm wondering if something like that could be integrated in jOOQ 3.0, with respect to the recent discussions on the user group about connection/data source/transaction lifecycle management. This was discussed in #385

@jjYBdx4IL

Issue #1507 wouldn't be a problem for the first solution because I wouldn't use jOOQ to call the stored procedures in that case: jOOQ would be used to create the SQL and that SQL could in turn be put into stored procedures automatically , which of course, could be done externally to jOOQ. As far as I understand it, PreparedStatements are basically precompiled temporary stored procedures (or so), and if one has no connection pool, why not create an adaptive "permanent" stored procedure pool? Having said that, I should add that I'm not a too great expert in that field, and I haven't tested my assumptions yet.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.