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,

…

etc

the XS engine was actually performing faster when I read the output of the stored procedure like this.. I was even able to iterate thru the output in a for loop and do a spec.split(‘,’) = JavaScript function to return that output as an array from the output of the stored procedure. it was truly not a though solution, I just needed to know how to manipulate the data… I know since most of the logic should be push down to the db as close as possible to take advantage of the db computation power, this was a huge thing for me.  I hope other people who have struggle with this, may find it beneficial. This may not be the ultimate solution as the data set may be huge later but for now ( foreseeable future) it will be ok for us.