jOOQ doesn't implement indexing. I really suggest learning about those first. You'll see immediately why jOOQ can't offer help with indexing your data here (other than creating the CREATE INDEX statements, of course, but it doesn't matter if you create them via jOOQ or directly in SQL).
If you're not using connection pools (you should, but read about how they work, first, e.g. HikariCP), then connection pool misconfigurations can't be the problem. On Tue, Feb 27, 2024 at 12:06 AM Joshua Villano <jos...@inspiretek.io> wrote: > Thanks Lukas, I'll take closer look at it. I know about it but not > familiar with implementing it. > > I meant is there an implementation of indexing in jooq or just study about > it on our cloud servers? If jooq then do you know where to find a resource > for it? As well as connection pools? > > But no worries im actively searching for them, thanks for you help! > > Thanks! > > On Monday, February 26, 2024 at 6:57:16 PM UTC+8 lukas...@gmail.com wrote: > >> Josh, >> >> I mean indexes on your database columns. Do you have an index on relevant >> columns of your table? If you don't know about indexing in SQL, I highly >> recommend this book (use the "jooq" discount code for 10% off, though it's >> only 10 EUR for the PDF version): >> https://sql-performance-explained.com >> >> >> On Mon, Feb 26, 2024 at 6:25 AM Joshua Villano <jos...@inspiretek.io> >> wrote: >> >>> Thank you for your response Lukas. >>> >>> Firstly, the ms was a guess as i didnt run an analyze on the manual >>> query, but youre right it only runs on about 10ms. >>> >>> Secondly, we use JooqRecords and use Relations between jooq pojos, not >>> sure if that matters. >>> >>> We also quite use the date manipulation (below) alot, does that matter >>> and any way/ recommendations? >>> >>> Lastly, seems like we dont use indexing yet, do you have any guide or >>> point me to the right direction on using indexes on jooq? >>> >>> dsl.select(year(dateField), >>> month(dateField), >>> count(Biometric.ID)) >>> .from(Biometric.TABLE) >>> .where(Biometric.TYPE.eq(BiometricType.CHECKIN_SCORE) >>> .and(dateField.isNotNull())) >>> .and(Biometric.START_LOCAL.between(start, end)) >>> .groupBy(year(dateField), month(dateField)) >>> .fetchMap( >>> row -> { >>> int year = row.get(year(dateField)); >>> int month = row.get(month(dateField)); >>> return LocalDate.of(year, month, 1); >>> }, >>> row -> >>> >>> Thanks >>> Josh >>> >>> On Friday, February 23, 2024 at 5:53:28 PM UTC+8 lukas...@gmail.com >>> wrote: >>> >>>> Hi Josh, >>>> >>>> I'm not aware of any such significant performance penalties within jOOQ >>>> itself (although, in case you're using R2DBC, there may still be issues >>>> that I'm unaware of). There is also little risk of running into a >>>> concurrency issue within jOOQ. The only place I recall where there's some >>>> sort of locking is the reflection cache, but it's unlikely this will be the >>>> problem in your case. >>>> >>>> Other than that, I'd look into: >>>> >>>> Problems related to concurrency / load: >>>> >>>> - Connection pooling. Does it have the right size (both too small and >>>> too large are problematic) >>>> - Is anyone locking the table / rows, etc. or is there any other source >>>> of non-blocking contention, e.g. undo/redo log contention, cursor cache >>>> contention, etc. >>>> - Does your server have too little RAM, etc. >>>> >>>> Problems not strictly related to concurrency: >>>> >>>> - Are your statistics off (people always run SQL queries manually and >>>> see how that's much faster, but with manual SQL queries, you're typically >>>> not using bind values, so with bind values, you might run into weird edge >>>> cases if your statistics are off. An extreme example is here: >>>> https://blog.jooq.org/why-you-should-design-your-database-to-optimise-for-statistics/ >>>> ) >>>> - Are you missing an index (specifically one that might not work with >>>> bind values, in case you're relying on a function based index) >>>> >>>> In any case, your query taking ~100ms by "default" is already a hint >>>> that something's off. Unless you're doing sophisticated reporting, most >>>> queries should take less than 10ms per execution, so any load related >>>> performance issue might also be addressed by improving individual execution >>>> performance, as these queries tend to keep resources busy for way too long. >>>> >>>> Depending on your RDBMS, you may be able to analyse this directly in >>>> the database itself. E.g. Oracle Enterprise Manager can greatly help >>>> troubleshoot such problems for Oracle or MySQL databases. >>>> >>>> I hope this helps, >>>> Lukas >>>> >>>> On Fri, Feb 23, 2024 at 10:36 AM Joshua Villano <jos...@inspiretek.io> >>>> wrote: >>>> >>>>> Hi all, >>>>> >>>>> First let me excuse myself if this is not the correct place to ask >>>>> this, and can you kindly point me in the right direction if ever, thanks! >>>>> >>>>> Recently we've been noticing degrading performance whenever we use >>>>> .select on a single table in about 8-10 times concurrently. These range >>>>> from simple column, to column average and complex selects. Difference of >>>>> 500ms when ran solo vs 4s when other selects are ran. On sql queries they >>>>> run <100ms. The timer i use are just java System.currentTimeMillis(); >>>>> before and after the .select. >>>>> >>>>> Not sure where to start looking so let me know if/what info you need. >>>>> >>>>> jooq 3.18.4 >>>>> >>>>> Thank you! >>>>> Josh >>>>> >>>>> -- >>>>> 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/6aea62c1-1fb2-4395-820b-566aee25985bn%40googlegroups.com >>>>> <https://groups.google.com/d/msgid/jooq-user/6aea62c1-1fb2-4395-820b-566aee25985bn%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 jooq-user+...@googlegroups.com. >>> >> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/8e106a20-e813-4e34-8db7-2240f889da22n%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/8e106a20-e813-4e34-8db7-2240f889da22n%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 jooq-user+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/49f9a0e2-e6a5-4723-b567-5413da128da8n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/49f9a0e2-e6a5-4723-b567-5413da128da8n%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 jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6V6S7Wr%3D_r7kCOv8KMeW5H3Zt2ot4ipPtr60XM3h6s%2Bw%40mail.gmail.com.
