prisma: SQL Server: `The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.`

I believe this issue is due to the default_batch_size of 2099. I’m no expert in SQL Server internals, but according to this SO answer, the true limit should be 2098.

It’s possible to use the environment variable QUERY_BATCH_SIZE=2098 as a workaround.

Wanted to flag that this issue is also happening in groupBy.

// ✅ runs using the QUERY_BATCH_SIZE in .env
await prisma.orderlines.findMany({
	where: {
		order_number: { in: ordersList.map((order) => order.orderNumber) },
	},
});

// ❌ fails with 'too many params' error despite QUERY_BATCH_SIZE in .env
await prisma.orderlines.groupBy({
	by: ['order_number', 'box_id'],
	where: {
		order_number: { in: ordersList.map((order) => order.orderNumber) },
	},
});

I believe this issue is due to the default_batch_size of 2099. I’m no expert in SQL Server internals, but according to this SO answer, the true limit should be 2098.

It’s possible to use the environment variable QUERY_BATCH_SIZE=2098 as a workaround.

This worked for me, thank you @dmitri-gb.

Hi @janpio, I was able to reproduce this error in isolation. You can reproduce this yourself by cloning and running the project within the following repo:

https://github.com/JaapWeijland/prisma-mssql-issue

The case is a bit different than I initially explained, but I was able to reproduce the same error using a smaller conceptual domain nonetheless.

Happy to help. If there are any questions, please let me know.

Posting this without a reproducible example at hand to offer (apologies), but we did run into a situation today where keeping our batch size at 2050 works generally for most queries, but using include on a typical order headers/lines table that had a FK set up did not work and threw errors. We followed the advice to reduce our query size even lower, seems like 1000 finally allows the query to be run, however, curiously when the query runs on a quiet dev DB with no activity aside from my application making this one query against it -> it goes into status: stalled immediately and never recovers/hangs for what seems like forever? (I lost patience after 10 minutes so can’t actually confirm if it ever really will recover 🤷‍♂️)

Checking out the process log there are no other blocking queries or activities as I’m the only activity on the DB. For reference if it matters the header table has 12,533,371 rows, the lines has 46,731,122.

(Edit: I should also note we aren’t pulling back that many rows, just wanted to note the table size as maybe it’s trying to search in an inefficient way? The actual result set we’re pulling using a raw query is ~3,700 records as it’s filtered on a few different statuses/etc.)

Sadly it seems like the workaround is only a partial workaround 🥲 So it’s back to writing some ugly code to split things up and do it manually on our end😅

Hope we have made progress/are closer to a resolve on this one! 🙏🕯️

This is definitely a good step and I’m sure it’ll fix most cases! Very happy to see this getting some action.

I’ve seen other ORMs do this without parameterizing the “join” field (just a comma-separated list) but this leaves you open to sql injection.

There are two ways I could see to solve this:

1. Keep query batching but limit batch size to fix the parameter issue

you’d have a MAX_BATCH_SIZE constant instead of QUERY_BATCH_SIZE, default 2098 Then you would work out the number of additional parameters (in the example above, 1 for color) and subtract that from the max. For the query you would use: batchSize = MAX_BATCH_SIZE - numAdditionalParameters

2. Do away with query batching and use temp tables to temporarily store join fields - my personal dream

I would guess this is a much more performant option for large datasets and it’s injection safe Downsides are write operations and more complexity.

Say you have a prisma query:

prisma.person.findMany({
  select: {
    emailAddresses: true,
  },
});

you could aim for sql like this. Person query:

create table #Main(id int primary key, FirstName nvarchar(255), LastName nvarchar(255))

insert #Main
exec sp_executesql N'select PeopleID, FirstName, LastName from People'

create table #Join(id int)
create unique clustered index ix on #Join(id)
insert #Join(id)
select distinct id
from #Main

select * from #Main
drop table #Main

Email query:

select EMailAddress.* from EMailAddress join #Join on EMailAddress.PeopleID = #Join.id
drop table #Join

multi-column relations and temp table indexing are important considerations for this approach

Setting the batch size to 2098 will work in some cases, but does not solve the root problem. This is because it is possible to add more parameters to the query.

A fixed batch size is a fundamentally flawed approach, and the batch size should instead be dynamically determined based on the number of other parameters.

For example, if batch size is 2098 and you wanted to get all compatible red doors:

prisma.findUnique({
    where: {
        productId,
    },
    select: {
        compatibleDoors: true,
        where: {
            color: 'red'
        }
    },
})

For your ‘compatible doors’ batched query, you’ll end up with sql something like this:

exec sp_executesql 
N'select * from Doors where color = @P1 and id in(@P2, ... @P2199)'
, N'@P1 varchar(255) ... @P2199 int'
, @P1='red', ... @P2199=12345

Here you have sp_executesql with 2101 parameters:

• 1 sql string
• 1 parameter list
• 1 color
• 2098 ids

As a workaround I’ve set QUERY_BATCH_SIZE=2000 in my .env

Any progress with this one? I have the exact same problem. Tried to workaround it by fetching the relevant IDs of a single table, and manually chunking queries to the relating tables. Seems kinda anti pattern… any suggestions perhaps?

AFAIK the issue is not unique to MSSQL, although the parameter number message error probably is. Prisma is not translating the query code into a SQL JOIN query, but it is making multiple cascading queries and finally joining results in code within the driver: one can easily see this by activating query logging in the client: new PrismaClient({ log: ['query'] })

This behavior is far from optimal in any situation where the database is non trivial:

• missing indexes
• too much network traffic
• too many parameters error
• api timeouts

It usually ends up in unexpected errors when the system is live and the data grow over a certain point: I’ve seen this happening in both MySql and MSSql (not because other databases are immune, rather because these are the ones I routinely use).

While I firmly believe that Prisma is generally superior, in this one aspect it is still trailing way behind Sequelize whose join queries are excellent.

The workaround I usually adopt for the typical application where you have a list of rows and then you do CRUD operations on the single record, is to create views for the lists, add them as entities to schema and perform the findMany operation on them. Pros:

• the views underlying queries are natively written in sql, they can be optimized and use the appropriate indexes
• the queries will return only the needed fields
• findMany instructions become extremely simple to write and test

Con:

• new fake entities are added to the schema and manual work isrequired; this is true both for schema first and code first approaches
• there is no easy way to automatically apply these kind of migrations to the database

I believe this is a good compromise, but I’d really like to know when the Prisma team plans to fix this problem.

Amazing @JaapWeijland, I can reproduce with this:

gitpod /workspace/prisma-mssql-issue (master) $ npx ts-node src/index.ts
Deleting all...
Seeding database...
Deleting relations...
/workspace/prisma-mssql-issue/node_modules/@prisma/client/runtime/library.js:171
`)}function rd(e){return Array.isArray(e)}var Hi=(e,t)=>([r,...n])=>{l...turn t.map(El);default:return t}}var Ml=R(_r());var Tl=["datasources","errorFormat","log","__internal","rejectOnNotFound"],vl=["pretty","colorless","minimal"],Pl=["info","query","warn","error"],ud={datasources:(e,t)=>{if(!!e){if(typeof e!="object"||Array.isArray(e))throw new z(`Invalid value ${JSON.stringify(e)} for "datasources" provided to PrismaClient constructor`);for(let[r,n]of Object.entries(e)){if(!t.includes(r)){let i=At(r,t)||`Available datasources: ${t.join(", ")}`;throw new z(`Unknown datasource ${r} provided to PrismaClient constructor.${i}`)}if(typeof n!="object"||Array.isArray(n))throw new z(`Invalid value ${JSON.stringify(e)} for datasource "${r}" provided to PrismaClient constructor.
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              ^
PrismaClientUnknownRequestError: 
Invalid `prisma.door.update()` invocation in
/workspace/prisma-mssql-issue/src/index.ts:44:21

  41 const deleteRelations = async () => {
  42   console.log("Deleting relations...");
  43 
→ 44   await prisma.door.update(
Error occurred during query execution:
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: "487897f96434", procedure: "", line: 1 }), transient: false })
    at Pn.handleRequestError (/workspace/prisma-mssql-issue/node_modules/@prisma/client/runtime/library.js:171:7103)
    at Pn.handleAndLogRequestError (/workspace/prisma-mssql-issue/node_modules/@prisma/client/runtime/library.js:171:6358)
    at Pn.request (/workspace/prisma-mssql-issue/node_modules/@prisma/client/runtime/library.js:171:6237) {
  clientVersion: '4.14.1'
}