Cloudflare Developers•3w ago

yes i did this query

 const [total_records, users] = await Promise.all([
    prisma.user.count({
      where: whereClause
    }),
    prisma.user.findMany({
      take: limit || undefined,
      skip: page && limit ? (page - 1) * limit : undefined,
      where: whereClause,
      orderBy: sort ? getOrderBy(sort) : undefined,
      select: {
        id: true,
        first_name: true,
        last_name: true,
        role: {
          select: { name: true }
        },
        email: true,
        transportist: { select: { phone: true, status: true } },
        cargo_giver: { select: { phone: true, status: true } },
        created_at: true
      }
    })
  ])

 const [total_records, users] = await Promise.all([
    prisma.user.count({
      where: whereClause
    }),
    prisma.user.findMany({
      take: limit || undefined,
      skip: page && limit ? (page - 1) * limit : undefined,
      where: whereClause,
      orderBy: sort ? getOrderBy(sort) : undefined,
      select: {
        id: true,
        first_name: true,
        last_name: true,
        role: {
          select: { name: true }
        },
        email: true,
        transportist: { select: { phone: true, status: true } },
        cargo_giver: { select: { phone: true, status: true } },
        created_at: true
      }
    })
  ])

with prism findmany and selects it takes me 5 seconds and I changed it to using $queryRaw and it takes me 2 seconds which seems like quite a difference to me

6 Replies

James•3w ago

I would bet the query produced from findMany and count are going to be different than the one you're doing with queryRaw - unless you're doing the exact same one? Prisma often does multiple queries when you do findMany and other operations too, which you're likely avoiding with a raw query It does this to improve DX with it's nice to use wrappers and APIs, but there's always a tradeoff with speed vs raw performance with an ORM https://orm.drizzle.team/ is about as close as you'll get to raw queries with some of the creature comforts of an ORM, but not quite as ergonomic as something like Prisma

lucaspOP•3w ago

 const countQuery = `
    SELECT COUNT(*) as total_records
    FROM user u
    ${whereClause}
  `

  const usersQuery = `
    SELECT 
      u.id, 
      u.first_name, 
      u.last_name, 
      u.email, 
      u.created_at,
      r.name as role_name,
      t.phone as transportist_phone,
      t.status as transportist_status,
      cg.phone as cargo_giver_phone,
      cg.status as cargo_giver_status
    FROM user u
    LEFT JOIN role r ON u.role_id = r.id
    LEFT JOIN transportist t ON u.id = t.user_id
    LEFT JOIN cargo_giver cg ON u.id = cg.user_id
    ${whereClause}
    ${orderByClause}
    ${limitClause}
    ${offsetClause}
  `

  const [countResult, usersResult] = await Promise.all([
    prisma.$queryRaw<{ total_records: number }[]>`${Prisma.raw(countQuery)}`,
    prisma.$queryRaw<[]>`${Prisma.raw(usersQuery)}`
  ])

 const countQuery = `
    SELECT COUNT(*) as total_records
    FROM user u
    ${whereClause}
  `

  const usersQuery = `
    SELECT 
      u.id, 
      u.first_name, 
      u.last_name, 
      u.email, 
      u.created_at,
      r.name as role_name,
      t.phone as transportist_phone,
      t.status as transportist_status,
      cg.phone as cargo_giver_phone,
      cg.status as cargo_giver_status
    FROM user u
    LEFT JOIN role r ON u.role_id = r.id
    LEFT JOIN transportist t ON u.id = t.user_id
    LEFT JOIN cargo_giver cg ON u.id = cg.user_id
    ${whereClause}
    ${orderByClause}
    ${limitClause}
    ${offsetClause}
  `

  const [countResult, usersResult] = await Promise.all([
    prisma.$queryRaw<{ total_records: number }[]>`${Prisma.raw(countQuery)}`,
    prisma.$queryRaw<[]>`${Prisma.raw(usersQuery)}`
  ])

James•3w ago

Is that the exact query that findMany is doing?

lucaspOP•3w ago

yep do you recomend to use drizzle instead of prisma? is more faster?

James•3w ago

You could try it. You'll always going to add some overhead with any ORM

lucaspOP•3w ago

Yes I know, but I wouldn't think it would be that much, it's double and it seems too much for my taste.

Gaming

Programming

yes i did this query