System Out of Memory Exception - SQL Server Object - Get Collection Action

[maneesh.vemula1]

Hi Everyone - I am running the 'Get Collection' action from the Business Object 'BP-SQL Server' and getting the 'System Out of Memory' exception. The weird thing here is that I am not receiving this error while running from the Studio - I'm only getting this error when I am running the process through the control room. The total # of rows are 667288 in the collection. I checked the memory usage in the task manager - when run from the studio, the total memory is reaching around 600MB and it is able to retrieve the data without any issues but while running from the control room, it is going up to 1400MB before throwing the exception. Can someone advise on how to resolve this issue?

Thanks in advance!​​

------------------------------
Maneesh Vemula
------------------------------

[ManpreetKaur1]

Hi @maneesh.vemula1,

Did you try getting the data in chunks or batches rather than directly fetching the entire data set in one go?​

------------------------------
Manpreet Kaur
Manager
Deloitte
*If you find this post helpful mark it as Best Answer
------------------------------

[ewilson]

Hello @maneesh.vemula1,

@ManpreetKaur1's recommendation is the best approach. Break your query up into chunks (ex. rows 1-100000, rows 100001-200000, etc). The following tutorial discusses using the OFFSET and FETCH clauses in your query:

https://learnsql.com/cookbook/how-to-limit-rows-in-a-sql-server-result-set/​​

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[maneesh.vemula1]

Thanks Manpreet and Eric. I will try doing that. However, what do you think could be the reason for why its a problem from control room and not from the studio?

------------------------------
Maneesh Vemula
------------------------------