Prisma•2w ago

Prisma performance questions

Let's say I have this schema:

model Tenant {
  id String @id @default(cuid())
  name String

  customers Customer[]
  users User[]
  salesOrders SalesOrder[]
}

model Customer {
  id String @id @default(cuid())
  name String
  tenantId String
  tenant Tenant @relation(fields: [tenantId], references: [id])

  salesOrders SalesOrder[]
}

model User {
  id String @id @default(cuid())
  name String
  tenantId String
  tenant Tenant @relation(fields: [tenantId], references: [id])

  salesOrders SalesOrder[]
}

model SalesOrder {
  id String @id @default(cuid())
  customerId String
  customer Customer @relation(fields: [customerId], references: [id])
  userId String
  user User @relation(fields: [userId], references: [id])

  tenantId String
  tenant Tenant @relation(fields: [tenantId], references: [id])
}

model Tenant {
  id String @id @default(cuid())
  name String

  customers Customer[]
  users User[]
  salesOrders SalesOrder[]
}

model Customer {
  id String @id @default(cuid())
  name String
  tenantId String
  tenant Tenant @relation(fields: [tenantId], references: [id])

  salesOrders SalesOrder[]
}

model User {
  id String @id @default(cuid())
  name String
  tenantId String
  tenant Tenant @relation(fields: [tenantId], references: [id])

  salesOrders SalesOrder[]
}

model SalesOrder {
  id String @id @default(cuid())
  customerId String
  customer Customer @relation(fields: [customerId], references: [id])
  userId String
  user User @relation(fields: [userId], references: [id])

  tenantId String
  tenant Tenant @relation(fields: [tenantId], references: [id])
}

I want to create a query that gets all the sales orders for a given tenant.

const salesOrders = await prisma.salesOrder.findMany({
  include: {
    customer: true,
    user: true,
  },
  where: {
    tenantId: tenant.id,
  },
});

const salesOrders = await prisma.salesOrder.findMany({
  include: {
    customer: true,
    user: true,
  },
  where: {
    tenantId: tenant.id,
  },
});

For context, this is a multi-tenant application sharing the same database. Looking at some slowness, I assume this would be better, correct?

const salesOrders = await prisma.salesOrder.findMany({
  include: {
    customer: true,
    user: true,
  },
  where: {
    tenantId: tenant.id,
    customer: {
      tenantId: tenant.id,
    },
    user: {
      tenantId: tenant.id,
    },
  },
});

const salesOrders = await prisma.salesOrder.findMany({
  include: {
    customer: true,
    user: true,
  },
  where: {
    tenantId: tenant.id,
    customer: {
      tenantId: tenant.id,
    },
    user: {
      tenantId: tenant.id,
    },
  },
});

Another thing we don't have is indexes on the tenantId fields. I assume this would also help, correct? What other things could we do to improve the performance of this query?

6 Replies

Prisma AI Help•2w ago

You opted for speed from this bot. Let's conjure up an answer right now! You're on the right track with your performance considerations. Here are targeted recommendations based on your scenario and the knowledge sources: 1. Indexes on tenantId fields:
Yes, adding indexes to the tenantId fields in your models will significantly improve query performance, especially for multi-tenant filtering. Indexes allow the database to quickly locate relevant rows without scanning the entire table. Prisma supports defining indexes in your schema, e.g.:

model SalesOrder {
  // ...
  tenantId String
  @@index([tenantId])
}

model SalesOrder {
  // ...
  tenantId String
  @@index([tenantId])
}

This applies to Customer, User, and SalesOrder models. For more on indexes and their impact, see Improving Query Performance with Indexes using Prisma. 2. Query structure:
Adding additional tenant checks in the where clause (e.g., customer: { tenantId: tenant.id }) can help ensure data isolation, but if your data model is consistent and all related records always have the same tenantId, this may be redundant and could slightly increase query complexity. The main performance gain comes from proper indexing and not over-fetching data. 3. Use JOINs (Prisma 5.7+):
If you are using PostgreSQL or CockroachDB and Prisma 5.7+ with the relationJoins preview feature enabled, Prisma can now resolve relations using SQL JOINs, reducing the number of round-trips and improving performance for queries like yours. See Prisma 5.7.0 release notes. 4. Avoid over-fetching:
Only include related data you actually need. If you don’t need all fields from customer or user, use select to fetch only required fields. 5. Monitor and optimize:
Use Prisma Optimize to analyze query performance and get recommendations. Also, consider logging query durations and using tools like OpenTelemetry for deeper insights Perf ormance Benchmarks: Improve the performance of your Prisma ORM queries. Summary:
- Add indexes on tenantId fields. - Use JOINs if available. - Avoid redundant filters if your data is consistent. - Fetch only needed fields. - Monitor and analyze with Prisma Optimize. These steps should help you address slowness and optimize your multi-tenant queries.

leofcOP•2w ago

Could you please clarify item 2?

Prisma AI Help•2w ago

🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

Nurul•2w ago

I would definitely recommend you to add indexes. You can also give Prisma Optimize a try: https://www.prisma.io/optimize It should help you in getting recommendations on how to improve the queries

leofcOP•2w ago

Thanks!

Nurul•7d ago

No worries

Gaming

Programming

Prisma performance questions

Did you find this page helpful?