- Just now, we implemented a nice little feature in jOOQ’s code generator: https://github.com/jOOQ/jOOQ/issues/4974
It detects whenever the jOOQ code generator runs a slow query to reverse engineer schema meta information. Why?
In our development and integration test environment, we don’t have huge schemas with all the different performance edge cases put in place. For instance, we don’t have 5000 Oracle synonyms. Or 10000 procedures with 500 parameters each. We do cover some common edge cases, but not in all databases.
Users on the other hand tend to accept the status quo after a while. The code generator is slow-ish? Sure, because we have a huge schema. This lazy acceptance is an impediment to our product quality. We had rather our users report all sorts of issues they encounter, so we want to encourage them.
And we did
In the upcoming jOOQ version 3.8 (and patch releases for 3.5.5, 3.6.5, and 3.7.3), we’ve added a nice little ExecuteListener to jOOQ-meta, which looks roughly like this:
class PerformanceListener
extends DefaultExecuteListener {
StopWatch watch;
class SQLPerformanceWarning
extends Exception {}
@Override
public void executeStart(ExecuteContext ctx) {
super.executeStart(ctx);
watch = new StopWatch();
}
@Override
public void executeEnd(ExecuteContext ctx) {
super.executeEnd(ctx);
if (watch.split() > 5 * 1000 * 1000 * 1000)
log.warn(
"Slow SQL",
"jOOQ Meta executed a slow query"
+ "\n\n"
+ "Please report this bug here: "
+ "https://github.com/jOOQ/jOOQ/issues/new\n\n"
+ formatted(ctx.query()),
new SQLPerformanceWarning());
}
}It’s very simple. Every time we start executing a query, a “stop watch” is started. Every time we end execution, we check if the watch elapsed more than 5 seconds. If so, we log a warning, a link to our issue tracker, a formatted version of the SQL query, and a stack trace to help find the exact spot where the slow statement was executed.
Let’s run this
The reason why we did this is because we’ve seen ourselves that the PostgreSQL code generator runs a slow query to fetch all the stored procedures (and to generate overload indexes). The produced error message is:
[WARNING] Slow SQL : jOOQ Meta executed a slow query (slower than 5 seconds)
Please report this bug here: https://github.com/jOOQ/jOOQ/issues/new
select
"r1"."routine_schema",
"r1"."routine_name",
"r1"."specific_name",
case when exists (
select 1 as "one"
from "information_schema"."parameters"
where (
"information_schema"."parameters"."specific_schema" = "r1"."specific_schema"
and "information_schema"."parameters"."specific_name" = "r1"."specific_name"
and upper("information_schema"."parameters"."parameter_mode") 'IN'
)
) then 'void'
else "r1"."data_type"
end as "data_type",
"r1"."character_maximum_length",
"r1"."numeric_precision",
"r1"."numeric_scale",
"r1"."type_udt_schema",
"r1"."type_udt_name",
case when exists (
select 1 as "one"
from "information_schema"."routines" as "r2"
where (
"r2"."routine_schema" in (
'public', 'multi_schema', 'pg_catalog'
)
and "r2"."routine_schema" = "r1"."routine_schema"
and "r2"."routine_name" = "r1"."routine_name"
and "r2"."specific_name" "r1"."specific_name"
)
) then (
select count(*)
from "information_schema"."routines" as "r2"
where (
"r2"."routine_schema" in (
'public', 'multi_schema', 'pg_catalog'
)
and "r2"."routine_schema" = "r1"."routine_schema"
and "r2"."routine_name" = "r1"."routine_name"
and "r2"."specific_name" <= "r1"."specific_name"
)
) end as "overload",
"pg_catalog"."pg_proc"."proisagg"
from "information_schema"."routines" as "r1"
join "pg_catalog"."pg_namespace"
on "pg_catalog"."pg_namespace"."nspname" = "r1"."specific_schema"
join "pg_catalog"."pg_proc"
on (
"pg_catalog"."pg_proc"."pronamespace" = "pg_catalog"."pg_namespace".oid
and (("pg_catalog"."pg_proc"."proname" || '_') || cast("pg_catalog"."pg_proc".oid as varchar)) = "r1"."specific_name"
)
where (
"r1"."routine_schema" in (
'public', 'multi_schema', 'pg_catalog'
)
and not("pg_catalog"."pg_proc"."proretset")
)
order by
"r1"."routine_schema" asc,
"r1"."routine_name" asc,
"overload" asc
org.jooq.util.AbstractDatabase$1$SQLPerformanceWarning
at org.jooq.util.AbstractDatabase$1.executeEnd(AbstractDatabase.java:230)
at org.jooq.impl.ExecuteListeners.executeEnd(ExecuteListeners.java:163)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:269)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:346)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:308)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2703)
at org.jooq.util.postgres.PostgresDatabase.getRoutines0(PostgresDatabase.java:707)
at org.jooq.util.AbstractDatabase.getRoutines(AbstractDatabase.java:1131)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:417)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:314)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:279)
at org.jooq.util.GenerationTool.run(GenerationTool.java:490)
at org.jooq.util.GenerationTool.generate(GenerationTool.java:193)
at org.jooq.util.maven.Plugin.execute(Plugin.java:131)
...We can now proceed with fixing the query, easily.
You can do the same!
The implementation of the ExecuteListener was straight forward. You can do the same, very easily. Just hook a simple execute listener into your jOOQ Configuration, measuring execution speeds and logging warnings after a threshold, done.
Happy debugging!
Further reading
Coincidentally, a very similar approach has been documented by the engineering team at Square – The Query Sniper: https://corner.squareup.com/2016/01/query-sniper.html
| Reference: | How to Detect Slow Queries with jOOQ from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
