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): https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-inline-values-in-sql/
To verify this, you could force jOOQ to use inline values using DSL.inline(), or StatementType.STATIC_STATEMENT (in Settings): https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-statement-type 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: https://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/ 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: https://www.jooq.org/benchmark 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. On Tue, Feb 23, 2021 at 3:14 AM Hong Nguyen Thanh <[email protected]> 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? > > On Friday, February 19, 2021 at 2:51:50 PM UTC+7 [email protected] wrote: > >> 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: >> https://use-the-index-luke.com >> >> I can highly recommend Markus's work. Use the "jooq" 10% discount code on >> his book if you want to purchase that: >> https://sql-performance-explained.com >> >> It's a high ROI investment for 95% of your SQL performance issues. >> >> I hope this helps, >> Lukas >> >> On Fri, Feb 19, 2021 at 8:39 AM Hong Nguyen Thanh <[email protected]> >> wrote: >> >>> 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 >>> >>> >>> >>> -- >>> 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 [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/124c1a3c-3594-4d9c-bf43-91e59e0bd4e8n%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/124c1a3c-3594-4d9c-bf43-91e59e0bd4e8n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/a01a70fc-9759-4b25-8cea-1734dacecb60n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/a01a70fc-9759-4b25-8cea-1734dacecb60n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5%2BYZsSaUvSV073qmA33KxvgcPq6GRvwmwT4y9G8R3sVw%40mail.gmail.com.
