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_000_000_000L)
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.htmlLike this:
Like Loading...
hi!
I am using code similar to yours, but I think you should put the StopWatch into a ThreadLocal because otherwise a query can call split on the watch of another query
Thanks for the feedback. You can govern the lifecycle of an ExecuteListener via an ExecuteListenerProvider. That way, you can create a new listener instance for each query execution.
I hope this helps
Ok ! thanks for advice :) I was using the DefaultExecuteListenerProvider, I will do my own then