[Bug]: Performance Issue with Excessive Queries in Form Response List

[mohaphez]

What happened?

Recently, while reviewing the system, I noticed a significant number of database queries being executed when loading the form response list. This seems to be caused by displaying collection values within the table, which can lead to performance issues, particularly when multiple fields use collection types. The attached screenshots illustrate the problem.

The current situation involves just 2 collection fields and 10 records, yet we're seeing an excessive number of queries being generated for this simple setup.

Upon investigating the code, I discovered that the method getCollectionsValuesForResponse() is responsible for retrieving the collection values, and it triggers a separate query for each record in a Select field. However, I am unsure how to optimize this process to reduce the number of queries. Are you have any idea ?

How to reproduce the bug

• Define a simple form and create 2-3 fields with collection types.
• Add multiple responses as entries.
• Use DB::listen to monitor the database queries when you open the "Entries Report" page.

Package Version

3.0.65

PHP Version

8.3.0

Laravel Version

11.0

Which operating systems does with happen with?

Linux

Notes

No response

[atmonshi]

ya,
since there is no relations between the values and the collections! and there is different types of collections, I am unsure how to optimize this process to reduce the number of queries too :)

[mohaphez]

Hi @atmonshi

Thanks for quick response !

Do you think it would help if we create a cache for each dataSource type and ID, even for a short duration like 30 seconds? This could reduce the number of queries when loading the table page by preventing repeated requests.

Right now, this is the only solution that comes to mind, but perhaps there's a better approach. "_"

[atmonshi]

this actually nice solution, not bad at all, we can also store it for longer time, since they wont change unles the collection data is changed, we can clear the cache there too.

if you want to start on a PR go for it, otherwise I prefer to wait for filament 4.

[mohaphez]

Hi @atmonshi

Now , we have it in #327 PR , please check it

Thanks in advance .

[atmonshi]

done with #327