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.
