The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

[Erdega]

Hi Prisma Team! My Prisma Client just crashed. This is the report:

Hints

The database is a Microsoft Azure Database (i.e. SQL-Server based)
The table Customers contains more than 80,000 records.
The table Devices contains more than 22,000 records.
When I try SQL: select * from Customers c join Devices d on d.CustomerID = c.ID where d.InstallDate >= convert(date, '2021-03-01')
I get 17 records in less than a second. Of course some Customers-data are redundant, when having multiple Devices with expected InstallDate.
DATABASE_URL="sqlserver://xxx.database.windows.net:1433;Initial Catalog=Xxx;Persist Security Info=false;User ID=xxx;Password=xxx;MultipleActiveResultSets=false;Encrypt=true;TrustServerCertificate=false;Connection Timeout=30;"

Versions

Name	Version
Node	v14.16.1
OS	windows
Prisma Client	2.21.2
Query Engine	query-engine e421996c87d5f3c8f7eeadd502d4ad402c89464d
Database	undefined

Query

query {
  findManyCustomers {
    ID
    Name
    Devices(where: {
      InstallDate: {
        gte: "X"
      }
    }) {
      Serial
      CustomerID
      InstallDate
    }
  }
}

Logs

ma:engine   flags: [
  prisma:engine     '--enable-experimental=microsoftSqlServer',
  prisma:engine     '--enable-raw-queries',
  prisma:engine     '--port',
  prisma:engine     '59497'
  prisma:engine   ]
  prisma:engine }  
  prisma:engine stdout  Starting a mssql pool with 9 connections.  
  prisma:engine stdout  Performing a TLS handshake  
  prisma:engine stdout  TLS handshake successful  
  prisma:engine stdout  Database change from 'Xxx' to 'master'  
  prisma:engine stdout  Changed database context to 'Xxx'.  
  prisma:engine stdout  SQL collation change from None to windows-1252/windows-1252  
  prisma:engine stdout  Microsoft SQL Server�� version 3490119692  
  prisma:engine stdout  Packet size change from '4096' to '4096'  
  prisma:engine stdout  Started http server on http://127.0.0.1:59497  
  prisma:engine Search for Query Engine in F:\git\TCS\hello-prisma\node_modules\.prisma\client  
  prisma:engine Client Version: 2.21.2  
  prisma:engine Engine Version: query-engine e421996c87d5f3c8f7eeadd502d4ad402c89464d  
  prisma:engine Active provider: sqlserver  
  prisma:engine stdout  The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.  +3s
  prisma:engine {
  prisma:engine   error: PrismaClientUnknownRequestError2 [PrismaClientUnknownRequestError]: Error occurred during query execution:
  prisma:engine   ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(TokenError { code: 8003, state: 1, class: 16, message: "The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.", server: "xxx-xxx", procedure: "", line: 1 }) })
  prisma:engine       at NodeEngine.graphQLToJSError (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:27491:14)
  prisma:engine       at NodeEngine.request (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:27387:24)
  prisma:engine       at processTicksAndRejections (internal/process/task_queues.js:93:5)
  prisma:engine       at cb (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:34750:26) {
  prisma:engine     clientVersion: '2.21.2'
  prisma:engine   }
  prisma:engine }  
  prisma:engine stdout  The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.  +2s
  prisma:engine {
  prisma:engine   error: PrismaClientUnknownRequestError2 [PrismaClientUnknownRequestError]: Error occurred during query execution:
  prisma:engine   ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(TokenError { code: 8003, state: 1, class: 16, message: "The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.", server: "xxx-xxx", procedure: "", line: 1 }) })
  prisma:engine       at NodeEngine.graphQLToJSError (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:27491:14)
  prisma:engine       at NodeEngine.request (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:27387:24)
  prisma:engine       at processTicksAndRejections (internal/process/task_queues.js:93:5)
  prisma:engine       at cb (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:34750:26) {
  prisma:engine     clientVersion: '2.21.2'
  prisma:engine   }
  prisma:engine }  
  prisma:engine stdout  The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.  +2s
  prisma:engine {
  prisma:engine   error: PrismaClientUnknownRequestError2 [PrismaClientUnknownRequestError]: Error occurred during query execution:
  prisma:engine   ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(TokenError { code: 8003, state: 1, class: 16, message: "The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.", server: "xxx-xxx", procedure: "", line: 1 }) })
  prisma:engine       at NodeEngine.graphQLToJSError (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:27491:14)
  prisma:engine       at NodeEngine.request (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:27387:24)
  prisma:engine       at processTicksAndRejections (internal/process/task_queues.js:93:5)
  prisma:engine       at cb (F:\git\TCS\hello-prisma\node_modules\@prisma\client\runtime\index.js:34750:26) {
  prisma:engine     clientVersion: '2.21.2'
  prisma:engine   }
  prisma:engine }  

Client Snippet

import { PrismaClient } from "@prisma/client";
export const prisma = new PrismaClient();

async function main() {
  console.log(
    await prisma.customers.findMany({
      include: {
        Devices: { where: { InstallDate: { gte: new Date("2021-03-01") } } },
      },
    })
  );
}

main()
  .catch((e) => {
    console.error(e);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer"]
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

model Customers {
  ID      Int       @id @default(autoincrement())
  Name    String    @db.NVarChar(4000)
  Devices Devices[]
}

model Devices {
  Serial      String     @id @db.NVarChar(30)
  CustomerID  Int?
  InstallDate DateTime?  @db.DateTime
  Customer    Customers? @relation(fields: [CustomerID], references: [ID])
}

[pantharshit00]

Hey @Erdega

I tried this on my local machine and I was able to include more than 2100 items.

This is reproducible if you have more than 4000 users and include will try to use IN and it will overflow the server limit here.

[pimeys]

This should be "just" reducing the limit of splitting the queries (and later on just not triggering these queries).

[Erdega]

Hey @Erdega

I tried this on my local machine and I was able to include more than 2100 items.

This is reproducible if you have more than 4000 users and include will try to use IN and it will overflow the server limit here.

Hey @pantharshit00, thank you for the answer.

Correct: We have 80,000 customers and only some of them have one or more devices assigned to.

I used npm prisma-query-log to try to find out a bit more. Prisma seems to read first all customers (where 1=1) and then do a new select trying to get all related devices (where CustomerID in (* thousands of parameters *) ...)

I guess this seems to be bad practice according to performance. Reading 80,000 customers where only less than 20 have devices assigned to with the given date. I guess a join or sub select would be more efficient here.

Also in the full database schema we have a table already containing more then 100,000,000 records. Reading those first and later select related data even via split IN queries would be impossible.

[pimeys]

@Erdega we can and should do a quick fix for you outside of the sprint schedule (doing it hopefully today), but I expect it might be not that performant.

The client team focus is going to be in the query performance next and this is one of the issues we already know about and will be on the table quite soon.

And yes. Doing a join with that many records is not going to work out. I can make the client to not crash quite easily, but you need to wait a bit for the bigger refactorings to take place.

[Erdega]

@Erdega we can and should do a quick fix for you outside of the sprint schedule (doing it hopefully today), but I expect it might be not that performant.

The client team focus is going to be in the query performance next and this is one of the issues we already know about and will be on the table quite soon.

And yes. Doing a join with that many records is not going to work out. I can make the client to not crash quite easily, but you need to wait a bit for the bigger refactorings to take place.

@pimeys Prisma seems to become a very good product. It would help for now not to crash this way.

We are currently migrating away from pure SQL mixed with TypeORM (historically grown). I guess for now we can circumvent some performance issues with some workarounds and hope in long term Prisma will become better in performance.

[matthewmueller]

Definitely a problem, I just want to share a quick untested workaround for the time being while we figure out how to tackle this:

const customers = await prisma.customers.findMany({})
for (let customer of customers {
  const devices = await prisma.devices.findMany({
    where: { 
      installDate: { 
        gte: new Date("2021-03-01")
      },
      customerId: customer.Id 
    }
  })
  customers.devices.push(...devices)
}

This will be much slower though. Running these queries async with Promise.all could help speed things up. Definitely needs to be fix though.

[pimeys]

But also running them in parallel will eat up your valuable connections from the pool, blocking other users, so carefully...

[Erdega]

Definitely a problem, I just want to share a quick untested workaround for the time being while we figure out how to tackle this:

const customers = await prisma.customers.findMany({})
for (let customer of customers {
  const devices = await prisma.devices.findMany({
    where: { 
      installDate: { 
        gte: new Date("2021-03-01")
      },
      customerId: customer.Id 
    }
  })
  customers.devices.push(...devices)
}

This will be much slower though. Running these queries async with Promise.all could help speed things up. Definitely needs to be fix though.

Oops I guess my example source code was bad due to the lack of experience with Prisma. I only want to get the Customers having Devices with InstallDate >= 2021-03-01.

Maybe this would be the correct getMany call?

    await prisma.customers.findMany({
      select: {
        ID: true,
        Name: true,
        Devices: { where: { InstallDate: { gte: new Date("2021-03-01") } } },
      },
    });

but also lead to the "...maximum of 2100 parameters..." problem.

Or am I doing completely wrong in limiting the expected list of customers?

[Erdega]

Luckily I found #440 of prisma / prisma-client-js due which I was able to improve my getMany-call:

await prisma.customers.findMany({
        where: {
          Devices: { some: { InstallDate: { gte: new Date("2021-03-01") } } },
        },
        include: {
          Devices: true,
        },
      });

SQL:

SELECT [dbo].[Devices].[Serial], [dbo].[Devices].[CustomerID], [dbo].[Devices].[InstallDate] FROM [dbo].[Devices] WHERE [dbo].[Devices].[CustomerID] IN (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)

This seems to work fine for me for now.

But of course, the "...maximum of 2100 parameters..."-problem still remains, but does not influence my new getMany call right now.

This way I got the expected 10 customers with related 17 devices.

[pimeys]

Hey @Erdega I just remembered we have this test env var that might help you to get forward without us making any changes (until we fix these queries for good).

Could you try setting the env var QUERY_BATCH_SIZE=2000 so when you start your app, it can use the setting. You can iterate and try different values here, but this is an escape hatch in our tests to try out different batching modes.

It basically splits your select into smaller pieces and runs them in parallel. Not optimal and not the real solution I'm suggesting, but should get you forward without the client crashing.

[Erdega]

Hey @pimeys thank you so much. 2,000 works fine, I get all 80,000 customers and belonging devices.

I tried to increase the value. It works up to 2,097, but 2,098 and above gave the same error. Of course depending on the complexity of the queries.

So if I run into new problems like this I may reduce to 1,500 or even 1,000

Thank you

[thebiglabasky]

Until we go about looking at improving this globally (e.g. joins etc.), we should set a default for SQL Server to a batch number which would prevent from facing the error too quickly.

[Erdega]

Next to set a default value, I would recommend to mention this parameter somewhere in the documentation.

[thebiglabasky]

For sure!

[dpetrick]

Connectors with parameter limits will now default to working values with 2.29.

[jlkravitz]

@pimeys hi all, is there an update on this issue? I still get this error consistently on SQL Server, so just trying to troubleshoot.

[janpio]

The issue has been closed for over two years, so we were not aware that an update would be necessary. If you still have this problem, please create a new issue and provide the information the bug issue template is asking for. Thanks.

[Erdega]

Since I reported this issue I use the environment variable QUERY_BATCH_SIZE=1000. I never had the problem again, even if using IN statements with multiple 10K of e.g. IDs.

Info: But we are still using Prisma 3.15.x (yes we should update soon, I know)

[mpopolin]

@Erdega how do you set the QUERY_BATCH_SIZE var? Using .env ou on schema.prisma?

[Erdega]

@mpopolin I've set it in .env:

QUERY_BATCH_SIZE=1000