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
Ramazan•2y 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
Futures Trader•2y 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 😦
Want results from more Discord servers?
Add your server