SQLite Performance

[Kevin Jones]

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

[Lukas Eder]

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

--
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/CAKT%3DYsMqavAf%3D9OTU-YcNQ9hOVxQKrAU2U3oNZxw4BJ0JR594A%40mail.gmail.com.

[Kevin Jones]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6US2TXCKTfpxSVqmfbGenEsSW2QBRft0RRnbVsPdk6sg%40mail.gmail.com.

--

Kevin Jones

KnowledgeSpike

[Lukas Eder]

So, try replacing .eq(1) by .eq(inline(1))  on the original query

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAKT%3DYsPk7%2BJdPWL5QG2ZuVQdbYsVqS4CnGOB4F8X-pryBYWZtw%40mail.gmail.com.

[Kevin Jones]

Bingo!!

Thank you :)

So should all those 'hard-coded' values use 'inline' ?

Kevin

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5K%2BxRmVG05dzjWGcvKruFe2HppM2fo%3D%2BLEKZmthSzCMQ%40mail.gmail.com.

--

Kevin Jones

KnowledgeSpike

[Lukas Eder]

Kevin,

I never give any advice using universal quantifiers, such as "always" or "all" or "never", etc.

Cheers,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAKT%3DYsOA3b1ZCQDxO0rQx6ot2gtHCcf17oQcEwxo_nemqL5pFw%40mail.gmail.com.