mohlx Posts - Answer Overflow

mohlx

•Created by mohlx on 5/5/2023 in #questions

Why is the first query on each request so slow?

query: 356.14ms
query2: 6.166ms
query: 51.136ms
query2: 4.249ms
query: 47.58ms
query2: 2.664ms
query: 47.062ms
query2: 2.383ms
query: 51.787ms
query2: 3.194ms

query: 356.14ms
query2: 6.166ms
query: 51.136ms
query2: 4.249ms
query: 47.58ms
query2: 2.664ms
query: 47.062ms
query2: 2.383ms
query: 51.787ms
query2: 3.194ms

On each request in the Nextjs App Router is the first query extremly slow. I connect to the database as follows:

import { Pool } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";
import { env } from "~/env.mjs";

const pool = new Pool({
  connectionString: env.DATABASE_URL,
});

export const db = drizzle(pool);

import { Pool } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";
import { env } from "~/env.mjs";

const pool = new Pool({
  connectionString: env.DATABASE_URL,
});

export const db = drizzle(pool);

I also tried this in a simple nodejs project without next and there it's works. Does anyone have any idea why this is so?

3 replies

TTCTheo's Typesafe Cult

•Created by mohlx on 4/28/2023 in #questions

Why prisma do 2 seperate Queries instead of 1 left join?

const sessionByRefreshToken = await prisma.session.findUnique({
    where: {
      refreshToken: refreshToken.token,
    },
    include: {
      User: true,
    },
});

const sessionByRefreshToken = await prisma.session.findUnique({
    where: {
      refreshToken: refreshToken.token,
    },
    include: {
      User: true,
    },
});

Why does this query execute 2 seperate Queries like this:

prisma:query SELECT "public"."Session"."id", "public"."Session"."userId", "public"."Session"."refreshToken", "public"."Session"."accessToken", "public"."Session"."expiresAt", "public"."Session"."ip", "public"."Session"."userAgent", "public"."Session"."createdAt", "public"."Session"."updatedAt" FROM "public"."Session" WHERE ("public"."Session"."refreshToken" = $1 AND 1=1) LIMIT $2 OFFSET $3
prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."password", "public"."User"."clubId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" IN ($1) OFFSET $2

prisma:query SELECT "public"."Session"."id", "public"."Session"."userId", "public"."Session"."refreshToken", "public"."Session"."accessToken", "public"."Session"."expiresAt", "public"."Session"."ip", "public"."Session"."userAgent", "public"."Session"."createdAt", "public"."Session"."updatedAt" FROM "public"."Session" WHERE ("public"."Session"."refreshToken" = $1 AND 1=1) LIMIT $2 OFFSET $3
prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."password", "public"."User"."clubId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" IN ($1) OFFSET $2

I believe this should, to optimize performance, be send as a single query with a join. I wrote a raw sql query that does the same thing:

 const sessionByRefreshTokenRaw = await prisma.$queryRaw(
    Prisma.sql`SELECT * FROM "Session" LEFT JOIN "User" ON "Session"."userId" = "User"."id" WHERE "Session"."refreshToken" = ${refreshToken.token}`
  );

 const sessionByRefreshTokenRaw = await prisma.$queryRaw(
    Prisma.sql`SELECT * FROM "Session" LEFT JOIN "User" ON "Session"."userId" = "User"."id" WHERE "Session"."refreshToken" = ${refreshToken.token}`
  );

On avarage the query that prisma generated is 7ms, and my query is 2ms. Why does prisma not generate a sinqle query utilising a join?

4 replies

TTCTheo's Typesafe Cult

•Created by mohlx on 4/23/2023 in #questions

TRPC context in RSC

I wanted to use trpc in React server components. I found the approach with the createCaller() method. But there is one problem, for the auth I have to set the headers (cookies), but in the app router the cookies() and the headers() function is only read-only. Is there any good solution for this problem?

2 replies

Gaming

Programming