My experience with Calc View doing transpose vs Stored Proc approach

[SergioG_TX]

This reason why I wanted to share this is due to my personal experience with trying to optimize some code that was already working, however, it was not running optimally to what my client and I wanted to use it for.

Initially one of my colleagues created a calculation view that would provide an output of 10 columns : material, location and 8 inventory buckets.

Then he built a transpose view to return 4 columns: material, location, inventoryCategory and value.  The reason for this approach was to meet a requirement of how the output needed to be presented from an API call.

the initial calculation view was running in approx. 300-400 ms in small HANA appliance. Once my colleague created a transpose view, the time of the transpose view went up to about 700-800 ms. Then when we called this view from a stored procedure our response time increased to > 1 second which was a non-acceptable response in our requirement. I put my thinking hat on and did a few analysis. I first ran the view for 1 record, then for multiple to compare the result in execution time. Then I analyzed it from with the plan visualizer. i wasn't able to tell much from looking at what i was seeing so i started digging further.

since my view (prior to transposing) was performing well to our requirement, I thought why not query that view and do the transpose myself in the stored procedure - Again, at first I thought that this is a big no as i would be using the Sql Engine and the Calc engine, however, I still went ahead and did the sqlScript code to do the transpose myself. I realized that SqlScript was performing faster that way. since i was only transposing 8 columns into rows i didn't think it would be a huge thing for me to do this in sql script

so here is a initial screenshot of the calc view. it is a very straight fwd calc view doing joins and projections.

then the transpose view is literally taking the calc view from above and selecting each of the inventory columns into rows

then from the stored proc, I was querying the transpose view which was a calc view with 2 required input parameters. additionally each input parameter takes multiple values.

After I went back to the stored proc and re-wrote the logic to take the first calc view and i would do the transpose in sqlscript, the stored proc looks like this

finally in lines 87 - 92 i am doing an inner join when there is an optional filter. even though, I am introducing an IF statement, running the stored procedure like this is much faster than running the transpose view from the stored procedure.

My learnings here are:

1) follow the best practices as much as you can - then look into best fit

2) think outside the box and see what other options can be done

3) be patient and (if you have time) try different things to possibly improve the execution times - your client will appreciate it

4) don't give up  - don't say I have tried everything if you have not

again, this is just from a recent personal experience and I just wanted to share these thoughts with others who may be also struggling to improve some execution times.

Please share if you have experience similar scenarios where you had to think outside from best practices and maybe have done a best fit scenario instead.

Good luck and happy programming.

Sergio