Drizzle Team•14mo ago

Slow queries with relationships

Hi all, I recently moved from querying 4 tables concurrently, to using drizzle relations and using db.query. I really like using the query, but I've seen a massive increase in query latency on my planetscale dashboard. Is this to be expected? Side note, while I haven't done much profiling, I haven't noticed much of a change in my server response time (I'm using this query inside a nextjs server component). Here is my new query:

    return db.query.usersTable.findFirst({
        where: (row, { eq, and }) => (
            and(
                username ? eq(row.username, username) : undefined,
                userId ? eq(row.userId, userId) : undefined
            )
        ),
        with: {
            links: {
                where: (links, { eq }) => (
                    activeOnly ? eq(linksTable.status, "active") : undefined
                )
            },
            tabs: {
                where: (tabs, { eq }) => (
                    activeOnly ? eq(linksTable.status, "active") : undefined
                ),
                orderBy: (tabs, { asc }) => [asc(tabs.position)],
            },

            ...(withCampaignData ? {
                usersToCampaigns: {
                    with: {
                        campaign: {
                            columns: {
                              ...
                            },
                            with: {
                                seller: {
                                    columns: {
                                        ...
                                    }
                                }

                            }
                        }
                    }
                }
            } : {})
        }
    })

    return db.query.usersTable.findFirst({
        where: (row, { eq, and }) => (
            and(
                username ? eq(row.username, username) : undefined,
                userId ? eq(row.userId, userId) : undefined
            )
        ),
        with: {
            links: {
                where: (links, { eq }) => (
                    activeOnly ? eq(linksTable.status, "active") : undefined
                )
            },
            tabs: {
                where: (tabs, { eq }) => (
                    activeOnly ? eq(linksTable.status, "active") : undefined
                ),
                orderBy: (tabs, { asc }) => [asc(tabs.position)],
            },

            ...(withCampaignData ? {
                usersToCampaigns: {
                    with: {
                        campaign: {
                            columns: {
                              ...
                            },
                            with: {
                                seller: {
                                    columns: {
                                        ...
                                    }
                                }

                            }
                        }
                    }
                }
            } : {})
        }
    })

1 Reply

fasmOP•14mo ago

Gaming

Programming

Slow queries with relationships