Using SAP HANA to count my Elephants, Cows and Goats
We were working on SAP HANA for some project work, when one of our colleagues suddenly jumped in with a simple puzzle. This was a basic kind of math puzzle, which all of us might have solved in our math classes. But then, a technical minds are about finding something new out of everything. So we tried to use SAP HANA to count my number of elephants, cows and goats.
Here is what we found:
You have $100, with which you have to buy exact 100 toys.
There are three different choices for toys: Elephant Toy = $10, Cow Toy = $3, Goat Toy=$0.5.
So what are the possible options for buying 100 toys with just $100?
You don’t have to use any programming language, but some database technology (and that too, only SAP HANA) to achieve the result. 🙂
Reason (for such absurd challenge)
For such kinds of puzzle, anyone with technical background would prefer to use some programming language (C, C++, Java etc.), which provides support for loops and while statements. But then in past few years, with evolution of SAP HANA, we have learnt that providing a single platform for both application and database layers would work much faster than using two different technologies (a database technology to handle data, and some application platform running the business logic on top of database layer). If the whole business logic, the application code and the database could reside on a single huge chunk of RAM, having the fastest of the processors processing the entire business flow from top to bottom, that could create wonders out of such technology. And for doing this, we need to understand the ways in which we can harness this kind of technology to its full extent. This challenge may be one ay of doing that!!!
How it progressed
To proceed with, we started with the most basic programming constructs, running loops and using conditional statements in them. This would be a simple stored procedure developed in SQLScript. With help from various sources (CREATE PROCEDURE, Working with stored procedures), discussions (link 1, link 2) and with feedback from experts (Maxime Tremblay-Savard, Ravindra Channe, Lars Breddemann and many others) , we came out with the first working construct to find the answer. Here it goes:
–creating the table for storing logs
CREATE TABLE PRACTICE_TEST.MY_LOG
–procedure to make entries in log tables
CREATE PROCEDURE PRACTICE_TEST.PROC_MY_LOG
(IN v_in_msg VARCHAR (100) )
INSERT INTO PRACTICE_TEST.MY_LOG ( LOG_ENTRY) VALUES (:v_in_msg);
–dropping any other procedure with similar name (just in case)
DROP PROCEDURE PRACTICE_TEST.PROC_PUZZLE_OUTPUT;
–Creating the actual stored procedure to process the business logic
CREATE PROCEDURE PRACTICE_TEST.PROC_PUZZLE_OUTPUT
ELEPHANT_COUNT := 1;
CNTR := 0;
v_msg := ”;
While ELEPHANT_COUNT < 10
COW_COUNT := 1;
While COW_COUNT < 100
GOAT_COUNT := 1;
while GOAT_COUNT < 100
CNTR := CNTR + 1;
if MOD(:cntr, 1000) = 0 then
v_msg := ‘CNTR RUN COUNT = ‘||:CNTR;
if ((:ELEPHANT_COUNT + :COW_COUNT + :GOAT_COUNT) = 100) AND ((:ELEPHANT_COUNT * 10) + (:COW_COUNT * 3) + (:GOAT_COUNT * 0.5) = 100)
v_msg := ‘CNTR RUN COUNT = ‘||:CNTR || ‘Goat Loop : Elephant count = ‘||:ELEPHANT_COUNT||’ Cow count = ‘||:COW_COUNT||’Goat count = ‘||:GOAT_COUNT;
GOAT_COUNT := :GOAT_COUNT + 1;
COW_COUNT := :COW_COUNT + 1;
ELEPHANT_COUNT := :ELEPHANT_COUNT + 1;
–deleting the log entries for previous runs
truncate table PRACTICE_TEST.MY_LOG;
–running the procedure having business logic
–Checking the results in the log
SELECT * from PRACTICE_TEST.MY_LOG ASC;
-X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X–X- X- X-
Yes, this worked well. We found the correct answer to the puzzle. 5 Elephants, 1 Cow and 94 Goats would fetch us exact 100 toys in exact $100. But then with the discussions in the feedback, we realized that this was not the best method for carrying out this logic. Asby Lars, a better way would be to use the internal CE functions provided by SAP HANA.
CE (Calculation engine) Built-In functions are special function provided by SAP, that are optimized for performance with SAP HANA. If these functions are used instead of normal DB Constructs, the internal implementation would be in a much more optimized way, because their logic is directly implemented in the calculation engine. (For more details about CE Functions, see http://help.sap.com/hana/hana_dev_sqlscript_en.pdf )
We are still looking forward to create the same business logic using CE functions in SAP. If we could create the same business logic using appropriate CE Functions, and measure its performance against the traditional B Programming constructs, we could identify how much difference this creates.
We still believe this article as an open article, and conclusion can be made only after the same business logic is implemented using the CE functions. So until our team is working with that, we invite suggestion/feedback/inputs from everyone about this idea.