Parameterization of query params (400+ items) results in exponential execution time (aka slow)

[iBotPeaches]

• PHP version 8.4
• PHP SQLSRV or PDO_SQLSRV version 5.12.0 (sqlsrv)
• Microsoft ODBC Driver version 18.18.5.1
• SQL Server version Azure SQL Database 12.0.2000 in Azure or MSSQL 2019 on-prem.
• Client operating system Alpine 3.20

---

Table schema

TBD

Problem description

For queries with any medium amount of parameters (400+), but still under the MSSQL limit of 2100 we find an exponential slowdown that makes system inoperable. I'm talking queries that take 80+ seconds.

It seems this has been reported before: #1501 #743 #189.

Expected behavior and actual behavior

I expect this query to take not long at all. To confirm this I installed dblib alongside odbc and joined a raw query via sqlsrv with my existing Laravel query (via sqlsrv). So I had 4 different connections to my table and ran the same query in random order over n over and summarized the average below.

Driver	Connection Time	Query Time	Total Time	Items
sqlsrv	393.23 ms	77,734.58 ms	78,129.85 ms	10,355
odbc	545.64 ms	626.28 ms	1,172.13 ms	10,355
dblib	407.70 ms	352.10 ms	760.76 ms	10,355
laravel	1,028.68 ms	79,671.05 ms	80,700.09 ms	10,355

Once I replicated without Laravel in the base execution using this pattern I felt validated.

$pdo = new PDO($dsn, $username, $password, $options);
$stmt = $pdo->prepare($query);
$stmt->execute($itemNumbers);
$result = $stmt->fetch();

Out of the box with no configuration odbc and dblib had no issue running my query at the expected speed.

Repro code or steps to reproduce

Reproducing this is obviously a challenge, but I will return with a Dockerized working replication step. I simply ran out of time, but I wanted to get this chunk of the report out while I work on replication steps.

[iBotPeaches]

=== Database Test Script - Multi-Driver Support ===
Date/Time: 2025-09-09 17:53:20
Testing all available drivers: sqlsrv, odbc, dblib, laravel

=== Testing sqlsrv Driver ===
✓ Successfully connected! (Time: 496.38ms)
Parameters: 838 item numbers
✓ Parameterized query executed successfully! (Time: 85,826.22ms)
Total matching items in items table: 10355
Total execution time: 86,323.36ms

=== Testing odbc Driver ===
✓ Successfully connected! (Time: 374.97ms)
Parameters: 838 item numbers
✓ Parameterized query executed successfully! (Time: 625.26ms)
Total matching items in items table: 10355
Total execution time: 1,000.44ms

=== Testing dblib Driver ===
✓ Successfully connected! (Time: 372.26ms)
Executing parameterized count query on items table...
Parameters: 838 item numbers
✓ Parameterized query executed successfully! (Time: 358.12ms)
Total matching items in item table: 10355
Total execution time: 731.43ms

=== Testing Laravel Raw Query ===
✓ Laravel bootstrapped successfully! (Time: 804.61ms)
Executing parameterized Laravel raw query on item table...
Parameters: 838 item numbers
✓ Parameterized Laravel raw query executed successfully! (Time: 79,535.69ms)
Total matching items in item table: 10355
Total execution time: 80,340.68ms

In this test I proved Laravel and the raw sqlsrv succumb to same issue (parameters). Still working on a reproducible sharable example as this is done in a private repo.

I logged the queries on the MSSQL side and I don't understand how my parameters are becoming nvarchar(4000)

nvarchar(4000),@p6 nvarchar(4000),@P7 nvarchar(4000),@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 

$stmt = $pdo->prepare($query);
$stmt->execute($items);

This led me to discovering this attribute (PDO::SQLSRV_ATTR_ENCODING) which I set to SYSTEM with something like this.

$mssqlOptions = extension_loaded('pdo_sqlsrv') ? array_filter([
    PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_SYSTEM
]) : [];

Now my query went from 82s to .9s. Now I still don't understand why dblib and odbc have no issue with this without this parameter, but I'll research that more. Leaving all of this in public for the next Laravel people who have poor performance come wandering.

[iBotPeaches]

Since I'm just responding to myself here building a trail of thoughts for years to come. The reason this occurs in systems presumably like Laravel is because it uses PDOStatement::bindValue(string|int $param, mixed $value, int $type = PDO::PARAM_STR): bool under the hood.

This makes sense since it can't trust in userland a reference for like bindParam to stay between preparation and execution so it uses value. The downside of value is the datatype/length of those isn't supported as a parameter because you have the value. Telling the length/datatype of a literal would make no sense and create a conflict of truth.

So when our string parameters come in to the driver they run through pdo_sqlsrv until they hit this line

column_size = param->max_value_len;

So our value which in the case of my sample query is a UUID is now a nvarchar(4000) or varchar(8000) depending on the constant - SQLSRV_ATTR_ENCODING

Now now our query with 400 parameters becomes either

(@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 

80s 🐢

or

(@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000),@P8 varchar(8000),@P9 varchar(8000),@P10 varchar(8000),@P11 varchar(8000),@P12 varchar(8000),@P13 varchar(8000),@P14 varchar(8000),@P15

1.1s 🔥

We know the difference column types between our column and the parameter are off with the nvarchar becoming the type of all our parameters. This causes all sort of issues with index scans and just all in all inefficient as proven by the nearly 72.7 times slower query.

With the conversation to a type closer to our schema its faster (1.1s) but not as fast as a raw query (212ms). So still need to research more - I imagine having parameterized parameters at max length isn't helping.

Research:

1. Why do dblib and odbc and mysql not suffer from this?
2. Is MySQL binary protocol the reason it can just read the size/type of a parameter to define on the fly types/memory and not suffer from this?
3. Why does sqlsrv pick the largest data size of a type?

[prennings]

I also ran into this problem last week and also could not figure out why this is happening and for the same order why query's for MySQL are fast.

It's not only slow when using allot of parameters, the varchar(8000) and nvarchar(4000) are there cause the driver itself does not know the field length of an table column and such to make sure it works it sets it to 8000 / 4000 (The encoding). Be aware of changing the encoding of you are only reading it should be fine but if you are also writing characters may be brake cause it is no longer on UTF-8 (thats why the VARCHAR(8000) instead of the NVARCHAR(4000) is happening).

We could get SQL query's to run for up to 8 seconds wheren paramaterized with PDO, when using hard coded values inside the query it went back to 0.052s (what I would expect :-)). The reason is that with parameters and PDO it sets a variable with VARCHAR(8000) and SQL Server does not use a index on the field provided with @p1 (Cause the field itself is 2 characters long, as such VARCHAR(2) would do) and SQL server turns to a table scan instead of index seek.

The work arround we use (with laravel and such) is or go to raw SQL and to tthe binding inside the query itself and lose Eloquent within the process.

Or if you do not need relations (In case of laravel):

$results = Model::fromQuery(
  Model::limit(10)
    ->where('field', 'value')
    ->toRawSql()
);

Cause we are not sending it with parameters but "hardcode" the values inside the query, indexes are used and @p1 etc. are not defined (cause no parameters are used).

[aduhaime-arm]

We are running in the same issue here with laravel 12, php 8.4 and sqlsrv.
In our case, with a simple query like this:

DB::table('MyTable')
->select(
    'MyTable.ID',
)
->whereIn('MyTable.Number', $chunk->pluck('referenceNumber'))
->get();

It create a sql statement where every parameter is a nvarchar(4000).
The number column however is a varcahr(50)
This means we can't use the index on the Number column, resulting in very poor performance.

We tried using PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_SYSTEM,
It does reduce the query from a couple hundred seconds to near instant.
However, when using that constant, randomly, we get the error PDOException: SQLSTATE[HY090]: [unixODBC][Driver Manager] Invalid string or buffer length , so we cannot rely on this.

[iBotPeaches]

@aduhaime-arm - We got that Invalid string or buffer length as well and it was because of nvarchar(max) fields included in the query causing some weird buffer expanse. We had to basically alias those as like MAX(columnA) as columnA to workaround it.

Since for us reverting the encoding fix wasn't an option - as we are talking about 60+ second queries without it. Hope that helps.

[aduhaime-arm]

For us, we did not have any nvarchar(max) column in the query.

Looking at this repo wiki Recommendations - encoding

I investigated our docker image codepage. Turns out it was posix. Once I've changed it to the same as our sql server (en_US.UTF-8), the SQLSRV_ENC_CHAR option worked correctly without any invalid buffer length error.