Simulating Latency with SQL / JDBC – Java, SQL and jOOQ.

The answer is PostgreSQL and Hibernate particular, although to doesn’t should be. In addition to, it makes use of a saved operate to work across the limitations of a VOID operate in PostgreSQL, however that may be labored round otherwise as nicely, with out storing something auxiliary to the catalog.

To take away the Hibernate dependency, you may simply use the pg_sleep operate straight utilizing a NULL predicate, however don’t strive it like this!

choose 1
from t_book
-- Do not do that!
the place pg_sleep(1) is just not null;

It will sleep 1 second per row (!). As might be seen within the clarify plan. Let’s restrict to three rows to see:

clarify analyze
choose 1
from t_book
the place pg_sleep(1) is just not null
restrict 3;

And the result’s:

Restrict  (value=0.00..1.54 rows=3 width=4) (precise time=1002.142..3005.374 rows=3 loops=1)
   ->  Seq Scan on t_book  (value=0.00..2.05 rows=4 width=4) (precise time=1002.140..3005.366 rows=3 loops=1)
         Filter: (pg_sleep('1'::double precision) IS NOT NULL)
 Planning Time: 2.036 ms
 Execution Time: 3005.401 ms

As you may see, the entire question took about 3 seconds for 3 rows. The truth is, that is additionally what occurs in Gunnar’s instance from the tweet, besides that he was filtering by ID, which “helps” disguise this impact.

We will use what Oracle calls scalar subquery caching, the truth that a scalar subquery might be moderately anticipated to be side-effect free (regardless of the apparent side-effect of pg_sleep), that means that some RDBMS cache its consequence per question execution.

clarify analyze
choose 1
from t_book
the place (choose pg_sleep(1)) is just not null
restrict 3;

The result’s now:

Restrict  (value=0.01..1.54 rows=3 width=4) (precise time=1001.177..1001.178 rows=3 loops=1)
   InitPlan 1 (returns $0)
     ->  Outcome  (value=0.00..0.01 rows=1 width=4) (precise time=1001.148..1001.148 rows=1 loops=1)
   ->  Outcome  (value=0.00..2.04 rows=4 width=4) (precise time=1001.175..1001.176 rows=3 loops=1)
         One-Time Filter: ($0 IS NOT NULL)
         ->  Seq Scan on t_book  (value=0.00..2.04 rows=4 width=0) (precise time=0.020..0.021 rows=3 loops=1)
 Planning Time: 0.094 ms
 Execution Time: 1001.223 ms

We’re now getting the specified one-time filter. Nevertheless, I don’t actually like this hack, as a result of it relies on an optimisation, which is non-compulsory, not a proper assure. This can be ok for a fast simulation of latency, however don’t rely upon this sort of optimisation in manufacturing lightheartedly.

One other strategy that appears to ensure this behaviour can be to make use of a MATERIALIZED CTE:

clarify
with s (x) as materialized (choose pg_sleep(1))
choose *
from t_book
the place (choose x from s) is just not null;

I’m now once more utilizing a scalar subquery, as a result of I one way or the other have to entry the CTE, and I don’t wish to place it within the FROM clause, the place it will impression my projection.

The plan being:

Outcome  (value=0.03..2.07 rows=4 width=943) (precise time=1001.289..1001.292 rows=4 loops=1)
   One-Time Filter: ($1 IS NOT NULL)
   CTE s
     ->  Outcome  (...) (precise time=1001.262..1001.263 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  CTE Scan on s  (value=0.00..0.02 rows=1 width=4) (precise time=1001.267..1001.268 rows=1 loops=1)
   ->  Seq Scan on t_book  (value=0.03..2.07 rows=4 width=943) (precise time=0.015..0.016 rows=4 loops=1)
 Planning Time: 0.049 ms
 Execution Time: 1001.308 ms

Once more, containing a one-time filter, which is what we would like right here.

Utilizing a JDBC primarily based strategy

In case your utility is JDBC primarily based, you don’t should simulate the latency by tweaking the question. You possibly can merely proxy JDBC in a method or one other. Let’s take a look at this little program:

strive (Connection c1 = db.getConnection()) {

    // A Connection proxy that intercepts preparedStatement() calls
    Connection c2 = new DefaultConnection(c1) {
        @Override
        public PreparedStatement prepareStatement(String sql) 
        throws SQLException {
            sleep(1000L);
            return tremendous.prepareStatement(sql);
        }
    };

    very long time = System.nanoTime();
    String sql = "SELECT id FROM guide";

    // This name now has a 1 second "latency"
    strive (PreparedStatement s = c2.prepareStatement(sql);
        ResultSet rs = s.executeQuery()) {
        whereas (rs.subsequent())
            System.out.println(rs.getInt(1));
    }

    System.out.println("Time taken: " + 
       (System.nanoTime() - time) / 1_000_000L + "ms");
}

The place:

public static void sleep(very long time) {
    strive {
        Thread.sleep(time);
    }
    catch (InterruptedException e) {
        Thread.currentThread().interrupt();
    }
}

For simplicity causes, this makes use of jOOQ’s DefaultConnection which acts as a proxy, conveniently delegating all of the strategies to some delegate connection, permitting for overriding solely particular strategies. The output of this system is:

1
2
3
4
Time taken: 1021ms

This simulates the latency on the prepareStatement() occasion. Clearly, you’d be extracting the proxying into some utility so as to not litter your code. You may even proxy all your queries in improvement and allow the sleep name solely primarily based on a system property.

Alternatively, we might additionally simulate it on the executeQuery() occasion:

strive (Connection c = db.getConnection()) {
    very long time = System.nanoTime();

    // A PreparedStatement proxy intercepting executeQuery() calls
    strive (PreparedStatement s = new DefaultPreparedStatement(
        c.prepareStatement("SELECT id FROM t_book")
    ) {
        @Override
        public ResultSet executeQuery() throws SQLException {
            sleep(1000L);
            return tremendous.executeQuery();
        };
    };

        // This name now has a 1 second "latency"
        ResultSet rs = s.executeQuery()) {
        whereas (rs.subsequent())
            System.out.println(rs.getInt(1));
    }

    System.out.println("Time taken: " +
        (System.nanoTime() - time) / 1_000_000L + "ms");
}

That is now utilizing the jOOQ comfort class DefaultPreparedStatement. In the event you want these, simply add the jOOQ Open Supply Version dependency (there’s nothing RDBMS particular in these lessons), with any JDBC primarily based utility, together with Hibernate:

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
</dependency>

Alternatively, simply copy the sources of the lessons DefaultConnection or DefaultPreparedStatement in case you don’t want your complete dependency, otherwise you simply proxy the JDBC API your self.

A jOOQ primarily based answer

In the event you’re already utilizing jOOQ (and you have to be!), you are able to do this much more simply, by implementing an ExecuteListener. Our program would now appear like this:

strive (Connection c = db.getConnection()) {
    DSLContext ctx = DSL.utilizing(new DefaultConfiguration()
        .set(c)
        .set(new CallbackExecuteListener()
            .onExecuteStart(x -> sleep(1000L))
        )
    );

    very long time = System.nanoTime();
    System.out.println(ctx.fetch("SELECT id FROM t_book"));
    System.out.println("Time taken: " +
        (System.nanoTime() - time) / 1_000_000L + "ms");
}

Nonetheless the identical consequence:

+----+
|id  |
+----+
|1   |
|2   |
|3   |
|4   |
+----+
Time taken: 1025ms

The distinction is that with a single intercepting callback, we will now add this sleep to all forms of statements, together with ready statements, static statements, statements returning consequence units, or replace counts, or each.