So, try replacing .eq(1) by .eq(inline(1)) on the original query
On Fri, Sep 13, 2024 at 9:30 AM Kevin Jones <ke...@knowledgespike.com>
wrote:
> Thanks Lucas.
>
> so I've narrowed this down.
>
> My original CTE looks like this:
>
> fun createTemporaryInnings(matchType: String, fieldingCteName: String):
> SelectHavingStep<Record2<Int, Int>> {
> val selInningsNumber = select(
> field("matchId"),
> field("teamId"),
> field("opponentsId"),
> field("playerId"),
> ).from(fieldingCteName)
> .where(field("inningsNumber", Int::class.java).eq(1))
> .asTable("tid")
>
> return select(
> field("playerId", Int::class.java),
> count().`as`("innings"),
> ).from(INNINGS)
> .join(MATCHES).on(MATCHES.ID.eq(INNINGS.MATCHID))
> .and(MATCHES.MATCHTYPE.eq(matchType))
> .join(selInningsNumber)
> .on(
> field("innings.matchId").eq(field("tid.matchId"))
> .and(field("innings.teamId").eq(field("tid.TeamId")))
> )
> .where(MATCHES.MATCHTYPE.eq(matchType))
> .groupBy(field("tid.playerId"))
> }
>
> and the generated SQL looks like this (which is what I expect)
>
> select playerId, count(*) as innings
> from Innings
> join Matches on (Matches.Id = Innings.MatchId and
> Matches.MatchType = 't')
> join (select matchId, teamId, opponentsId, playerId
> from tmp_fielding
> where inningsNumber = 1) as tid
> on (innings.matchId = tid.matchId and
> innings.teamId = tid.TeamId)
> where Matches.MatchType = 't'
> group by tid.playerId
>
>
> So I have a nested select. Running this in the IDE takes about 1.5 seconds
> but from my code takes about 11 seconds.
>
> If I replace the JOOQ nested query with SQL
>
> fun createTemporaryInnings(matchType: String, fieldingCteName: String):
> SelectHavingStep<Record2<Int, Int>> {
> val selInningsNumber = select(
> field("matchId"),
> field("teamId"),
> field("opponentsId"),
> field("playerId"),
> ).from(fieldingCteName)
> .where(field("inningsNumber", Int::class.java).eq(1))
> .asTable("tid")
>
> return select(
> field("playerId", Int::class.java),
> count().`as`("innings"),
> ).from(INNINGS)
> .join(MATCHES).on(MATCHES.ID.eq(INNINGS.MATCHID))
> .join(
> "(select matchId, teamId, opponentsId, playerId\n" +
> " from tmp_fielding\n"
> +
> " where inningsNumber
> = 1) as tid\n"
> )
> .on(
> field("innings.matchId").eq(field("tid.matchId"))
> .and(field("innings.teamId").eq(field("tid.TeamId")))
> )
> .where(MATCHES.MATCHTYPE.eq(matchType))
> .groupBy(field("tid.playerId"))
>
> }
>
>
> Then the generated SQL is the same but the code now only takes 1.5 seconds
> to execute,
>
> Kevin
>
>
>
>
>
> On Thu, Sep 12, 2024 at 7:30 PM Lukas Eder <lukas.e...@gmail.com> wrote:
>
>> Hi Kevin,
>>
>> The main differences are:
>>
>> - jOOQ uses bind values by default, not inline values. There might be
>> edge cases where this bothers optimisers
>> - jOOQ fetches all results in memory eagerly by default. JDBC (and JDBC
>> based UIs) are lazy, fetching only a few records
>>
>> Other than that, I'd have to see example code.
>>
>> I hope this helps
>> Lukas
>>
>> On Thu, Sep 12, 2024 at 6:37 PM Kevin Jones <ke...@knowledgespike.com>
>> wrote:
>>
>>> I'm not sure if this is a stupid question.
>>>
>>> I have an application that uses JOOQ. It's a desktop app written in
>>> Kotlin using SQLite as the database with a lot of fairly complex queries.
>>> Within the app one of the queries takes about 11 seconds to run. If I take
>>> the generated SQL for that query and run it from an IDE against the same
>>> SQLite database it takes ~1.2 seconds to run. (The IDE is Jetbrains
>>> DataGrip and it's using JDBC under the covers)
>>>
>>> I'm using the same version of the JDBC driver in both cases, I'm not
>>> using a connection pool in the Kotlin app
>>>
>>> This started out because the JOOQ I had written was taking too long, so
>>> I grabbed the SQL and managed to find the issue. I could fix the issue in
>>> one of two ways, the fixes are equivalent and both reduce the query time to
>>> ~1.2 seconds.
>>>
>>> If I apply either of those two fixes to the JOOQ code then the fix
>>> appears in the generated SQL *but* the query still takes about 11 seconds.
>>>
>>> Does JOOQ pass the generated SQL straight to the DB or is something else
>>> going on?
>>>
>>> --
>>> Kevin Jones
>>> KnowledgeSpike
>>>
>>> --
>>> 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/CAKT%3DYsMqavAf%3D9OTU-YcNQ9hOVxQKrAU2U3oNZxw4BJ0JR594A%40mail.gmail.com
>>> <https://groups.google.com/d/msgid/jooq-user/CAKT%3DYsMqavAf%3D9OTU-YcNQ9hOVxQKrAU2U3oNZxw4BJ0JR594A%40mail.gmail.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/CAB4ELO6US2TXCKTfpxSVqmfbGenEsSW2QBRft0RRnbVsPdk6sg%40mail.gmail.com
>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO6US2TXCKTfpxSVqmfbGenEsSW2QBRft0RRnbVsPdk6sg%40mail.gmail.com?utm_medium=email&utm_source=footer>
>> .
>>
>
>
> --
> Kevin Jones
> KnowledgeSpike
>
> --
> 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/CAKT%3DYsPk7%2BJdPWL5QG2ZuVQdbYsVqS4CnGOB4F8X-pryBYWZtw%40mail.gmail.com
> <https://groups.google.com/d/msgid/jooq-user/CAKT%3DYsPk7%2BJdPWL5QG2ZuVQdbYsVqS4CnGOB4F8X-pryBYWZtw%40mail.gmail.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/CAB4ELO5K%2BxRmVG05dzjWGcvKruFe2HppM2fo%3D%2BLEKZmthSzCMQ%40mail.gmail.com.
