uhp | Universal Honey Pot | Frontend Framework library

There are many things that are wrong here:

• Using SELECT DISTINCT with JOIN indicates that you should have used a Semi Join instead.
• The ON tl.owner_id = ?1 is done for filtering, not for the projection, hence you are better off doing an EXISTS query.
• Assuming why the query runs slow instead of profiling it. The reason why it runs faster in the DB tool is that DB tools usually truncate the result set while Spring Data consumes the entire result set. Or, if you run EXPLAIN, the output might come from the Optimizer without even running the query.

So, here's what you can do:

1. Use Semi Joins instead of Joins for filtering.
2. Use Blaze Persistence to write better entity queries dynamically.
3. Configure Statement Caching at the JDBC Driver level.
4. Use the slow query log to log the execution plan when the query is slower than N seconds.