SystemOutofMemoryException while loading reading .sql file

[Soumya21]

Hi Team,

We are loading .sql file (around 20MB) data into blue prism and performing some replace function in next steps but while executing these steps we are getting systemoutofmemory exception

If anyone knows how to handle this, please help us to resolve this issue

Thanks,

Soumya

[harish.mogulluri]

HI Sowmya,

I probably create stored procedure and pass the variables from stored procedure instead of using SQL script.

If the SQL script is required for the use case, Identify on which step it is throwing error
Check  the task manager CPU and Memory utilization before and after loading SQL script  to understand whether the issue is due to logic around sql script

I would  try creating new OBject and load the sql script and manipulate  in the object itself before I pass the final data to the  process. You can  try creating code stage or use calculating stage and identify which  option works better with out memory out of exception

Also take a look in bp KB article around memory issue.


https://support.blueprism.com/en/support/solutions/articles/7000076839-how-can-i-avoid-out-of-memory-issues-
As mentioned in teh above article try to implement "GC.Collect() function." in the object code stage

[zdenek.kabatek]

Hi, Soumaya21,

what about taking completely different approach. If it is only about replace some text in SQL I would go the following route - open the sql script in notepad (from Blue Prism) and call replace window do your stuff, save the sql script and you are done. This way you avoid any Out of Memory issues. Well, as long as notepad can handle such a file ;).

Regards

Zdenek

[david.l.morris]

Maybe I'm just crazy, but a 20MB text file shouldn't cause an issue like this, at least not with a single copy of the data in it. Have you checked how much memory is being used in Blue Prism before and after reading that file in?

What I would guess your issue is has to do with when you pass that text data into an object which is then creating its own copy of that data, instantly doubling the amount of memory being used. Then, if that object's action has two separate text data items (one for input and one for output), then the data is being tripled in memory. Then if you output from the action into a new text data item, you are now at a quadrupled amount of memory than really needs to be consumed by this data.

Keep in mind that most versions of Blue Prism (excluding the most recent ones) run as 32-bit processes in Windows. And then beyond that, what I've found is that various versions of Blue Prism will crash when they reach the 800 MB or higher point. I'm not saying it will 100% crash at this point, but I've never successfully had more than 1 GB of RAM being used by Blue Prism without getting a crash, at least as far as when I was paying attention. So, your text file of 20 MB multiplied a few times across different text data items could result in a crash with as little as 40 copies or so. And this is assuming the rest of your automation isn't holding onto tons of other data. If you're using collections or other massive text data items, that would contribute to the problem.

I agree with Harish and Zdenek that you likely should redesign your solution a bit. What form that redesign takes will depend on the actual cause of your memory issues. It really shouldn't be hard to identify though. Step through the process/objects and watch your memory utilization in task manager and this will help you identify the biggest offenders of memory util in your automation.