Filament•10mo ago

BulkAction too many records, can we chunk these?

I'm running into an issue with a bulk table action. The table has approximately 2500 records but SQL Server has a parameter limit of 2100. Therefore, if I try to run a bulk action on the entire table I get the following error:

SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.

Is it possible to chunk these before being passed into the action method?

->action(function (array $data, Collection $accounts) {
    ray($data, $accounts);
})

->action(function (array $data, Collection $accounts) {
    ray($data, $accounts);
})

This is what I mean by the parameter limit:

select *
from [crm].[accounts]
where [crm].[accounts].[id] in (
    31LC01, ABEG01, ABGE01, ABOG01, ABWA01, ACEWAN, ADAD01, ADAD02, ADAG01, ADAP01, ADAS01, ADEM001, 
    ADEM002, ADMI01, ADMM01, ADMM02, ADVM01, AGCA02, AGINN01, AGRC02, AGRE01, AGRILAB, AGRISAC, AGRM01, 
    AGRM02, AGRO01, AGWO01, AING01, AIRL01, AJ001, etc...
) and ([active] = 1)
order by [name] asc

select *
from [crm].[accounts]
where [crm].[accounts].[id] in (
    31LC01, ABEG01, ABGE01, ABOG01, ABWA01, ACEWAN, ADAD01, ADAD02, ADAG01, ADAP01, ADAS01, ADEM001, 
    ADEM002, ADMI01, ADMM01, ADMM02, ADVM01, AGCA02, AGINN01, AGRC02, AGRE01, AGRILAB, AGRISAC, AGRM01, 
    AGRM02, AGRO01, AGWO01, AING01, AIRL01, AJ001, etc...
) and ([active] = 1)
order by [name] asc

Solution:

This turned out to be an issue only when using string keys rather than integer keys. Laravel apparently does not parameterize integer keys anymore as demonstrated here: https://github.com/laravel/framework/pull/26434 This specific query is to a view rather than a table referencing our ERP database and so I don't really have control over the schema. In turn, I've managed to work around this issue by implementing an ETL pipeline that runs every minute to use SQL Server's MERGE statement (https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN) to sync the view into a real table which is using auto-incrementing surrogate keys....

GitHub

[5.7] Improve eager loading performance on MySQL by staudenmeir · P...

There is an old bug in the PDO implementation for MySQL that slows down larges WHERE IN queries. When the query has a few thousand bindings, the time execution time of fetchAll() increases non-line...

MERGE (Transact-SQL) - SQL Server

MERGE (Transact-SQL)

Jump to solution

1 Reply

Solution

morty•10mo ago

GitHub

[5.7] Improve eager loading performance on MySQL by staudenmeir · P...

MERGE (Transact-SQL) - SQL Server

MERGE (Transact-SQL)

Gaming

Programming

BulkAction too many records, can we chunk these?