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:

The Puzzle

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?

The Challenge

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

(LOG_ENTRY varchar(100));

–procedure to make entries in log tables

CREATE PROCEDURE PRACTICE_TEST.PROC_MY_LOG

(IN v_in_msg    VARCHAR  (100)   )

LANGUAGE SQLSCRIPT

AS

BEGIN

   INSERT INTO PRACTICE_TEST.MY_LOG ( LOG_ENTRY) VALUES (:v_in_msg);

END;

–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

LANGUAGE SQLSCRIPT

AS

  ELEPHANT_COUNT  integer;

  COW_COUNT integer;

  GOAT_COUNT integer;

  ELEPHANT integer;

  CNTR integer;

  v_msg varchar(100);

Begin

  ELEPHANT_COUNT := 1; 

  CNTR := 0;

  v_msg := ”;

  While ELEPHANT_COUNT < 10

    DO

    COW_COUNT := 1;

      While COW_COUNT < 100

        DO

          GOAT_COUNT := 1;

            while GOAT_COUNT < 100

              Do

                CNTR := CNTR + 1;

                  if MOD(:cntr, 1000) = 0 then

                                  v_msg := ‘CNTR RUN COUNT = ‘||:CNTR;

                                  call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

                  end if;

                  if ((:ELEPHANT_COUNT + :COW_COUNT + :GOAT_COUNT) = 100) AND ((:ELEPHANT_COUNT * 10) + (:COW_COUNT * 3)  + (:GOAT_COUNT * 0.5) = 100)

                  THEN

                         v_msg := ‘CNTR RUN COUNT = ‘||:CNTR || ‘Goat Loop : Elephant count = ‘||:ELEPHANT_COUNT||’ Cow count = ‘||:COW_COUNT||’Goat count = ‘||:GOAT_COUNT;

                         call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

                  end if;

                  GOAT_COUNT := :GOAT_COUNT + 1;

          END While;

          COW_COUNT := :COW_COUNT + 1;

      End While;                 
  ELEPHANT_COUNT := :ELEPHANT_COUNT + 1; 

  end while;

End;

–deleting the log entries for previous runs

truncate table PRACTICE_TEST.MY_LOG;

–running the procedure having business logic

call PRACTICE_TEST.PROC_PUZZLE_OUTPUT;

–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. As suggested by 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.

Conclusion:

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.