Tables: performance when selecting or using bulk actions on all records

[underdpt]

Hello,

First, thanks for this great package!

I've noticed that when you're working with lots of records and selecting all the records there might be some performance optimizations:

• When selecting all the records, all the keys are stored on the component and they're sent to the browser. I think this might not be necessary (only send the total number of records to display) and might be a security issue. On a 100.000 records table, this generates a response of about 640kb.

• The table uses this array of keys to perform the bulk actions. There's a limit on the sql servers on how many parameters can be used on a prepared statement (on mysql that's about 65.000, the error is 1390 Prepared statement contains too many placeholders), and this makes impossible to use the bulk actions if there are more records to process.

  This could be solved by storing the filtered query instead of all the keys, and then use that query to perform the bulk actions in a more performant way (using $query->delete() to bulk delete, using $query->cursor() on other actions to limit the memory usage, or allowing the action to get the query, that should be handy for exports).

What do you think?

[danharrin]

I understand your concerns, but I am unsure how this would work if you selected all records, and then wanted to deselect certain ones. Or entire pages. That's the point at which your concept kinda falls down there and makes our codebase quite unnecessarily complex.

[underdpt]

I did something like this with datatables time ago. It worked this way:

• A allSelected boolean variable which tells us if the user selected all the records
• A selected array, that behave this way
  • If allSelected === false then it holds the manually selected row ids
  • If allSelected === true then it holds the manually un-selected row ids

The query builder would work like this:

• If allSelected === false then it would run the query adding a whereIn('id', $selected)
• If allSelected === true then it would run the query with a whereNotIn('id', $selected)

The UI would also use the variables in the same way.

Al normally the user would select/unselect manually only a few rows, the performance would be better and it's very unlikely to reach the sql server bind variables limit.

It's true that this makes the codebase more complex, but I think the gain is worth it. The method described above might not be the optimal one (a little messy using the same variable for selected/unselected records, that can be worked on).

I'm not as familiar with filament 2 codebase as I was with version 1, but I could work on a PR if you might consider it.