Simulating Latency With SQL/JDBC
A nifty trick for anyone working with databases.
Join the DZone community and get the full member experience.Join For Free
I’ve run across a fun little trick to simulate latency in your development environments when testing some SQL queries. Possible use-cases including to validate that backend latency won’t bring down your frontend, or that your UX is still bearable, etc.
The solution is PostgreSQL and Hibernate specific, though it doesn’t have to be. Besides, it uses a stored function to work around the limitations of a
VOID function in PostgreSQL, but that can be worked around differently as well, without storing anything auxiliary to the catalog.
To remove the Hibernate dependency, you can just use the
pg_sleep function directly using a
NULL predicate, but don’t try it like this!
This will sleep 1 second per row (!). As can be seen in the explain plan. Let’s limit to 3 rows to see:
And the result is:
As you can see, the whole query took about 3 seconds for 3 rows. In fact, this is also what happens in Gunnar’s example from the tweet, except that he was filtering by ID, which “helps” hide this effect.
We can use what Oracle calls scalar subquery caching, the fact that a scalar subquery can be reasonably expected to be side-effect free (despite the obvious side-effect of
pg_sleep), meaning that some RDBMS cache its result per query execution.
The result is now:
We’re now getting the desired one-time filter. However, I don’t really like this hack, because it depends on an optimisation, which is optional, not a formal guarantee. This may be good enough for a quick simulation of latency, but don’t depend on this kind of optimisation in production lightheartedly.
Another approach that seems to guarantee this behaviour would be to use a
I’m now again using a scalar subquery, because I somehow need to access the CTE, and I don’t want to place it in the
FROM clause, where it would impact my projection.
The plan being:
Again, containing a one-time filter, which is what we want here.
Using a JDBC-Based Approach
If your application is JDBC based, you don’t have to simulate the latency by tweaking the query. You can simply proxy JDBC in one way or another. Let’s look at this little program:
For simplicity reasons, this uses jOOQ’s
DefaultConnection which acts as a proxy, conveniently delegating all the methods to some delegate connection, allowing for overriding only specific methods. The output of the program is:
This simulates the latency on the
prepareStatement() event. Obviously, you’d be extracting the proxying into some utility in order not to clutter your code. You could even proxy all your queries in development and enable the sleep call only based on a system property.
Alternatively, we could also simulate it on the
This is now using the jOOQ convenience class
DefaultPreparedStatement. If you need these, just add the jOOQ Open Source Edition dependency (there’s nothing RDBMS specific in these classes), with any JDBC based application, including Hibernate:
Alternatively, just copy the sources of the classes
DefaultPreparedStatement if you don’t need the entire dependency, or you just proxy the JDBC API yourself.
A jOOQ-Based Solution
If you’re already using jOOQ (and you should be!), you can do this even more easily, by implementing an
ExecuteListener. Our program would now look like this:
Still the same result:
The difference is that with a single intercepting callback, we can now add this sleep to all types of statements, including prepared statements, static statements, statements returning result sets, or update counts, or both.
Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.