JOOQ - selectCount performance

282 views
Skip to first unread message

Hong Nguyen Thanh

unread,
Feb 19, 2021, 2:39:21 AM2/19/21
to jOOQ User Group
Hi ,
I'm facing performance issue with  select count(*) with some conditions  on a table of more than 1 billion records.  My code looks like this : 
 ResultQuery<?> query =  dslContext.selectCount().from(Tables.TEST).where(conditions)
 Integer  retVal =  dslContext.fetchOne(query).into(Integer.class);

The conditions list  contains 4 conditions : date range and simple comparison condition on  3 other fields. 
if date range = 1 month and the total hit records count about 80000 records, it will take about 1 minutes - 2 minutes to get result. 
If date range = 1 month,  it will take about 5 mins - 10 mins
Do you have any idea on how to improve performance in this case.  Appreciate if anyone can help. Thanks
 


Lukas Eder

unread,
Feb 19, 2021, 2:51:50 AM2/19/21
to jOOQ User Group
Hi Hong,

You'll want the right indexes on your table, likely on that date column, possible on others too, or composite indexes. Obviously, you shouldn't lightheartedly add indexes all over the place. An index is a tradeoff between faster reads vs slower writes. But if you're querying for date ranges a lot, then that seems like an obvious choice.

This is an excellent page to learn about proper indexing by Markus Winand, for quick reference:

I can highly recommend Markus's work. Use the "jooq" 10% discount code on his book if you want to purchase that:

It's a high ROI investment for 95% of your SQL performance issues.

I hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/124c1a3c-3594-4d9c-bf43-91e59e0bd4e8n%40googlegroups.com.

Hong Nguyen Thanh

unread,
Feb 22, 2021, 9:14:36 PM2/22/21
to jOOQ User Group
Hi  Lukas
The SQL generated by JOOQ takes almost no time  when I run from a database client such as MS SQL Server Management Studio.  Do you think it 's still the problem of  incorrect indexing? 

Rob Sargent

unread,
Feb 22, 2021, 9:32:25 PM2/22/21
to jooq...@googlegroups.com


On 2/22/21 7:14 PM, Hong Nguyen Thanh wrote:
> Hi  Lukas
> The SQL generated by JOOQ takes almost no time  when I run from a
> database client such as MS SQL Server Management Studio.  Do you think
> it 's still the problem of  incorrect indexing?
>
If the /exact/ same sql is fast in tsql and slow in your app then
there's something fishy in you app setup.

Perhaps show the timing for both tsql and your app for one specific query.
Describe your complete production stack and hardware.

Lukas Eder

unread,
Feb 23, 2021, 2:34:20 AM2/23/21
to jOOQ User Group
jOOQ uses bind variables by default, but logs a query with inlined values, which you probably ran in your editor. With bind variables, there's a tendency for optimisers to make estimates based on average cardinalities, which can be terribly off if your data is skewed. This tends not to happen with inline values. I've explained this more in detail here (blog is Oracle specific, but applies to SQL Server just the same):

To verify this, you could force jOOQ to use inline values using DSL.inline(), or StatementType.STATIC_STATEMENT (in Settings):

In some cases, wrong bind variable types may lead to implicit type conversions, which again lead to indexes not being applicable. Here's an Oracle DATE vs TIMESTAMP example that explains the problem:

Again, this doesn't happen with inline values, only with bind variables. The solution is not to inline the values, but to understand why it happened with the bind variable. To verify this, check your execution plans using a query like this one from here: https://stackoverflow.com/a/7359705/521799

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

In addition to that, if you're running a test with jOOQ and then immediately run a test with a SQL editor using *the same query*, then chances are, the latter may profit from hot caches throughout the execution pipeline, e.g. "buffer caches", or whatever SQL Server calls them, execution plan caches, etc. To verify this, you could try to flush all your caches between executions, or run both executions many times using a benchmark technique like the one documented here:

If your query wasn't a COUNT(*) query, then an additional difference would be that jOOQ fetches all the rows by default, whereas SQL editors fetch only the first few rows by default, but that's irrelevant with COUNT(*).

These are the main reasons why a jOOQ execution could be slower than an execution in your SQL editor, the first two being that you didn't actually compare the exact same thing.

Reply all
Reply to author
Forward
0 new messages