Theo's Typesafe Cult•14mo ago

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?

2 Replies

Ramazan•14mo ago

That's... just how it works, unfortunately. I don't believe there are any plans to address this short term. Some threads to follow on this issue: https://github.com/prisma/prisma/discussions/12715 https://github.com/prisma/prisma/issues/12582 https://github.com/prisma/prisma/issues/15616

GitHub

Performance: How to make prisma perform a single SQL query with joi...

Hi there, When I make a query with the prisma client which includes other tables data, prisma seems to be chaining several SQL queries instead of making a single query to the database using joins. ...

GitHub

Network latency increases with joins · Issue #12582 · prisma/prisma

Bug description Per #5043 (comment), I am making this issue. I have found that joins have worse performance as the client <-> database latency increases. How to reproduce Check out the typesc...

GitHub

Performance and consistency issues for relational queries · Issue #...

Bug description When selecting related fields of one-to-many relations, the relations are selected by sequentially running one SELECT per related table. How to reproduce Set up tracing Run a simple...

Futures Trader•14mo ago

@MohlX Have not started with Prisma. I was excited about possibly using it until I saw this question asked. Considering I'll be having 3-4 join tables. This sounds like it'll play terrible with Prisma vs PG and raw SQL 😦

Gaming

Programming

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