Rather simple update does 6 queries

[JohnnyCrazy]

Hi,

I'm using the following query to update a OneToOne relation:

    await userRepository.update({
      where: { id: me.id },
      data: {
        credentials: {
          update: this.buildCredentials(tokenRes),
        },
      },
    });

with the following simplified schema:

model User {
  id            String      @id
  credentials   Credentials @relation(fields: [credentialsId], references: [id])
  credentialsId Int
}

model Credentials {
  id           Int      @default(autoincrement()) @id
  accessToken  String
  refreshToken String
  expires      DateTime
  user         User
}

Not counting SELECT 1, BEGIN and COMMIT, this creates a total of 6 queries. Log:

SELECT 1
BEGIN
SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1
SELECT "public"."User"."id", "public"."User"."credentialsId" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2
SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE (1=1 AND "public"."Credentials"."id" IN ($1)) OFFSET $2
SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE "public"."Credentials"."id" = $1
UPDATE "public"."Credentials" SET "expires" = $1, "refreshToken" = $2, "accessToken" = $3, "updatedAt" = $4 WHERE "public"."Credentials"."id" IN ($5)
SELECT "public"."User"."id", "public"."User"."country", "public"."User"."displayName", "public"."User"."email", "public"."User"."images", "public"."User"."product", "public"."User"."uri", "public"."User"."roles", "public"."User"."credentialsId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
COMMIT

Complete Query Log

2020-04-04T12:38:57.618Z engine stdout {
  timestamp: 'Apr 04 12:38:57.618',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT 1',
    item_type: 'query',
    params: '[]',
    duration_ms: 0
  }
}
prisma:query SELECT 1
2020-04-04T12:38:57.619Z engine stdout {
  timestamp: 'Apr 04 12:38:57.618',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: { query: 'BEGIN', item_type: 'query', params: '[]', duration_ms: 0 }
}
prisma:query BEGIN
2020-04-04T12:38:57.620Z engine stdout {
  timestamp: 'Apr 04 12:38:57.619',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1',
    item_type: 'query',
    params: '["beatportbot"]',
    duration_ms: 0
  }
}
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1
2020-04-04T12:38:57.620Z engine stdout {
  timestamp: 'Apr 04 12:38:57.619',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."User"."id", "public"."User"."credentialsId" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2',
    item_type: 'query',
    params: '["beatportbot",0]',
    duration_ms: 0
  }
}
prisma:query SELECT "public"."User"."id", "public"."User"."credentialsId" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2
2020-04-04T12:38:57.621Z engine stdout {
  timestamp: 'Apr 04 12:38:57.620',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE (1=1 AND "public"."Credentials"."id" IN ($1)) OFFSET $2',
    item_type: 'query',
    params: '[2,0]',
    duration_ms: 0
  }
}
prisma:query SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE (1=1 AND "public"."Credentials"."id" IN ($1)) OFFSET $2
2020-04-04T12:38:57.622Z engine stdout {
  timestamp: 'Apr 04 12:38:57.621',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE "public"."Credentials"."id" = $1',
    item_type: 'query',
    params: '[2]',
    duration_ms: 1
  }
}
prisma:query SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE "public"."Credentials"."id" = $1
2020-04-04T12:38:57.623Z engine stdout {
  timestamp: 'Apr 04 12:38:57.621',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'UPDATE "public"."Credentials" SET "expires" = $1, "refreshToken" = $2, "accessToken" = $3, "updatedAt" = $4 WHERE "public"."Credentials"."id" IN ($5)',
    item_type: 'query',
    params: '[2020-04-04 13:38:57.616 UTC,"XYXYX","XYXYX",2020-04-04 12:38:57.618513200 UTC,2]',
    duration_ms: 0
  }
}
prisma:query UPDATE "public"."Credentials" SET "expires" = $1, "refreshToken" = $2, "accessToken" = $3, "updatedAt" = $4 WHERE "public"."Credentials"."id" IN ($5)
2020-04-04T12:38:57.624Z engine stdout {
  timestamp: 'Apr 04 12:38:57.624',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."User"."id", "public"."User"."country", "public"."User"."displayName", "public"."User"."email", "public"."User"."images", "public"."User"."product", "public"."User"."uri", "public"."User"."roles", "public"."User"."credentialsId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3',
    item_type: 'query',
    params: '["beatportbot",1,0]',
    duration_ms: 2
  }
}
prisma:query SELECT "public"."User"."id", "public"."User"."country", "public"."User"."displayName", "public"."User"."email", "public"."User"."images", "public"."User"."product", "public"."User"."uri", "public"."User"."roles", "public"."User"."credentialsId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
2020-04-04T12:38:57.627Z engine stdout {
  timestamp: 'Apr 04 12:38:57.627',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: { query: 'COMMIT', item_type: 'query', params: '[]', duration_ms: 2 }
}
prisma:query COMMIT

There also seems to be 2 duplicated queries with an unnecessary OFFSET attached. Before opening a bug, I wonder if someone has an explanation for this, since I'm not a SQL Expert 😛

[nikolasburk]

Hey @JohnnyCrazy 👋 thanks a lot for raising this! It seems to me like this indeed can (and should) be optimized. For the Beta, we mainly focused on stability of Prisma Client, but we certainly have lots of performance improvements on our list that we'll talk through in the upcoming weeks and months.

Would be great if you could create an issue for this to make sure it's persisted 🙂

[nikolasburk]

Hey @JohnnyCrazy, just a quick reminder that it would be really helpful if you could open this as an issue as well so that we can tackle it from an engineering end! 🙂 (otherwise it's not officially part of our engineering process to look into this, since only "issues" but not "questions" are considered in the engineering planning)

[JohnnyCrazy]

Will do today, sorry for the delay 👍

[JohnnyCrazy]

Opened #2157