XSJS out of memory – maybe not
hi all, this is my first blog post thank you Lucas Oliveira for suggesting me to blog about my solution.
my issue was that I have a web service / API that I am offering as a service for consumers to request some data. Initially, there was a requirement that one of the end points in my RESTful service, I was supposed to expose an unfiltered request 😯 Yes that was my impression when I saw the requirement. I went back and forth with my client and I told them that most likely this type of request it will run out of memory / freeze / fail / etc.. this is not just in HANA but in any platform / technology… my suggestion was that there are probably other ways to accomplish this besides a web service call without filters.
anyways, they wanted to prove that this was still an option and they still wanted me to try to accomplish it. After several failed attempts, a couple nights ago, I sat down, looked at the data, and starred at my screen for a few seconds…
my XSJS service is calling a stored procedure which is calling a calculation view (CV). this CV was returning a very large data set which in HANA was not a problem. it was actually running within a 2-3 seconds for about 180k records. when I ran the stored procedure, it would also run without issues. The problem existed only when I tried to return that output via an output parameter of a table type from the stored procedure to a XSJS var (array) it literally blew up and all of the sudden we saw a service unavailable (http 503) response on the requesting browser. I started debugging and literally stepped thru the code and just saw it failed. While doing research, many people suggested we had a very small HANA appliance – in fact we do.. we have the smallest size appliance as this is a HANA Side car approach… anyways… long story short… while I was starring at the output of my stored procedure which looked like
id col1 col2 … spec value
1 x y a b
1 x y b c
1 x y c d
2 z j a d
2 z j b d
n val val val val
I noticed that a row was the same identifier column almost identical, except for spec/value columns… so I thought why not do a pivot of the data so that the output would be unique rows (based on the id) and the spec/value columns would be a concat of their values … using the STRING_AGG function in SQLScript, I was able to reduce the number of rows tremendously which resulted in the service being able to run without issues. My new output from my db stored procedure looks like
id col1 col2 … spec value
1 x y a,b,c b,c,d
2 z j a,b d,d,
Nice start! Would like to see more coding samples here though =)
Any chances you share a bit of code here (before/after samples)?
BTW: I think you refered to the wrong Lucas Oliveira ( my id is Lucas Oliveira)
Lucas de Oliveira
thanks for the correction... did I get the right person now? I will upload some code sample later today.
Yep. That's me 😉
While uploading a multipart file of 10MB i am getting 413 (Payload too large) with xsjs service.
Any solution to this ?
yes that seems to be a big file ... try a different file size (smaller) , otherwise, you will need to process this file from BODS (ETL tool)