🐛 Out of memory error on pool candidates csv download

[brindasasi]

🐛 Bug

When trying to download 500 candidates CSV file , out of memory error occurs. This can be reproduced in Azure env. with large amount of data. Below is the complete error log.

outofmemoryError.txt

🦋 Expected Behaviour

Download should work for large amount of data without any issue

🕵️ Details

Add any additional details that could assist with troubleshooting/fixing the issue.

• Operating System: Name and version (if possible).
• Browser: Name and version (if possible).

📋 Steps to Reproduce

1. Generate around 600 pool candidates
2. Try to download by 500s

📸 Screenshot

Add a screenshot (if possible).

🙋‍♀️ Proposed Solution

(optional) Do you have a proposed solution?

✅ Acceptance Criteria

A set of assumptions which, when tested, verify that the bug was addressed.

• [ ] Poolcandidates query needs to be optimized
• [ ] ~Laravel queues needs to be implemented for high volume download~
• [ ] Bulk data download should work without any issues

[brindasasi]

related #8974

[brindasasi]

PoolCandidatesTable_SelectPoolCandidates triggers over 100 sql queries and finishes with [2024-01-25 20:14:54] local.ERROR: Maximum execution time of 30 seconds exceeded {"userId":"06be6b14-86ce-4585-b70f-36fa25fe7c94","exception":"[object] (Symfony\\Component\\ErrorHandler\\Error\\FatalError(code: 0): Maximum execution time of 30 seconds exceeded at /home/site/wwwroot/api/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php:1939)

for 10 candidates CSV download 😭