Best Practice For Hana Performance Optimization (PART I):

Hi Everyone, I am sharing my experience of working on Oracle to Hana Migration Project.Below are the few points about the performance optimization of the sap hana code. We can achieve better performance by considering these points while writing SQLScript Procedure.

1.      Always select only the required column instead of selecting all the columns.


Example: Suppose their are three table TABLE_A ,TABLE_B and TABLE_C with the below structure.


TABLE_A Structure:


Name Age Emp_Id Department Salary


TABLE_B Structure:


Name Department Job_Grade Company_Name Company_Type

TABLE_C Structure:


Department Emp_Id Designation Job_Location


Now suppose in your procedure you have to select only the Name,Salary and Designation from these three table based on the join condition and use them to populate the data into some target table TABLE_T.

So,For the given Scenario you should not use the below SQL Statements if you are using this it will lead to performance degrade of the particular procedure.


                                              INS_1.PNG

If you are using query like above then you are selecting more column than required. So its always better to select only the required columns which will result in performance improvement of your SQL procedures.


                                                 ins_2.PNG

2.  Always try to use “NOT EXISTS” and “EXISTS” keyword in your procedure instead of  “NOT IN” and “IN” because using the      “NOT IN”  or “IN” inside the procedure will slow down the procedure performance.

    

     Example: I want to delete all the records from COMPONENT_A where ENTERPRISE ,SITE and PRODUCTION ORDER is not      in HEADER_A.

    

     Using the Below Delete statement will slow down the performance.

                               DE_1.png

    

     So ,Its always advisable to use the NOT EXISTS statements like below which will improve the performance.

                                  DE_2.png


3.     Always try to avoid using HDBSEQUENCE in your procedure Becuase it will slow down your procedure performance.

    

     Example:- Suppose I have SALES table with below structure.

        

Item Production_Order Sales_Name Sales_Organisation Status Scenario
A_1              0
B_2              0


Now i want to select all the item from the sales table and add the suffix to all the item of sales table and scenario is one of the sales table column which value is constant.


Solution:-     So first solution which will come to our mind is to create a hdbsequence  and concatenate that sequence to Item column of the SALES table.


Steps are given as:

I.     Create a HDBSEQUENCE.

          a.     Go to Project and follow the steps to created the sequence as below.

                   

                         /wp-content/uploads/2014/11/seq_2_595089.png          

II.     Now using the sequence created we can write the procedure for our scenario.Please see the below procedure using the sequence.


                SEQ_3.PNG


So, My observation was when i tried calling this procedure it took around 1 minute to execute. So i tried below approach.

If you have any column in your table which is constant through out you process then you should use row number function to achieve the same functionality. which will not affect the execution time at all. Like below.


                     SEQ_4.PNG


So,When i executed the above procedure it took only few seconds.

So if anyone have better idea of removing the sequence from hana procedure,Please share you thoughts.



 

4.     Always try to take filtered data for join operations.

Example:  In the below Hana Procedure I have used the table variable where we are storing the data from join of three table and their is calculation happening in the same join expression Because of which it takes more time to execute.


CREATE PROCEDURE TEST_PROC

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS

BEGIN

JN_DATA  =      SELECT   T1.RUNTIME

                                         T2.ITEM,

                                         T3.LOCATION

                            FROM   DETAILS T1,

                                         ROUTING T2,

                                         RESOURCES T3

                             WHERE T1.BOR= T2.BOR

                             AND      T1.LOCATION = T2.LOCATION

                             AND      T1.SCENARIO= T3.SCENARIO

                             AND      T2.ITEM = T3.NAME

                             AND     T1.BOR LIKE ‘%BOR_ALT%’

                             AND     T2.BOS NOT LIKE ‘%_TMP_%’

                             AND     T3.ITEM = ‘N’ OR ITEM IS NULL;

                        

                        

INSERT INTO TABLE_COMPONENTS (SELECT * FROM :JN_DATA);

                                

END;


In below procedure where i am taking the filtered data for join and it results in faster execution of the procedure.

CREATE PROCEDURE TEST_PROC1

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

BEGIN

EXP_DETAIL    = SELECT RUNTIME,

                                         LOCATION,

                                         SCENARIO,

                                          BOR

                           FROM     DETAILS

                           WHERE  BOR LIKE ‘%BOR_ALT%’;

EXP_ROUTING = SELECT   ITEM,

                                            LOCATION,

                                           BOR

                            FROM       ROUTING         

                            WHERE    BOS NOT LIKE ‘%_TMP_%’;

EXP_RESOURCES= SELECT  NAME,

                                               RESOURCE,

                                              SCENARIO

                                              LOCATION

                                FROM         RESOURCES

                               WHERE     ITEM = ‘N’ OR ITEM IS NULL;

JOIN_DATA   = SELECT     T1.RUNTIME

                                          T2.ITEM,

                                          T3.LOCATION

                            FROM    :EXP_DETAIL T1,

                                         :EXP_ROUTING T2,

                                         :EXP_RESOURCES T3

                             WHERE  T1.BOR= T2.BOR

                             AND       T1.LOCATION = T2.LOCATION

                             AND       T1.SCENARIO= T3.SCENARIO

                             AND       T2.ITEM = T3.NAME;

                    

INSERT INTO TABLE_COMPONENTS (SELECT * FROM :JOIN_DATA);

                                

END;

5.     Creating a read and write procedure is always better in terms of performance.So always try to create a read and write procedure to get the better performance.

      

       Example: Just for the example i am showing the procedure which takes more time when we use to read and write in the same procedure.

CREATE PROCEDURE HISTORY_DATA

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS

BEGIN

DATA_1=(SELECT  SCENARIOID,

                             LINENUM,

                            SITE,

                            NAME

                FROM HISTORY);

********************************************************

Many other Transaction on data not shown here

********************************************************

  INSERT INTO SHIPMENT_HISTORY

   (

    SCENARIOID,

    LINENUM,

    SITE,

    NAME

    )(SELECT * FROM :DATA_1);

  DATA_2=(SELECT      SCENARIOID,

                                    SHIPPED,

                                   DATESHIPPED,

                                  SOURCE,

                                  CREATEDDATE

                 FROM HISTORY);

********************************************************

Many other Transaction on data not shown here

********************************************************

INSERT INTO SHIPMENT_HISTORY

   (

    SCENARIOID,

    SHIPPED,

    DATESHIPPED,

    SOURCE,

    CREATEDDATE

    )(SELECT * FROM :DATA_2);

END;

So,the above procedure takes around 1:36 Minutes time when we run it that’s the reason i have separated the procedure into read and write procedure.

READ PROCEDURE:  The read procedure in hana does not allow any DML statements inside the procedure.So we will just read the data from the target tables after all the transactions and pass that data to the output parameter of the procedure ,Output parameter of the procedure can be a scalar variable or table variable.

So below steps has to be followed to create the read and write procedure.


STEP I- First create the HDBTABLETYPE of the same column which you are passing to the output parameter. And to Create the HDBTABLE first we have to declare the artifacts of different datatypes which we can use to create the table type. As shown in the below screen shot.

               new_read.PNG

STEP II- Now create the table type using these artefacts like below.      

             READ_2.PNG


         

STEP III- Create a read procedure and pass the data to output variable of above table type.

    

CREATE PROCEDURE HISTORY_DATA_READ

                                                                           (OUT OUT_DATA_1 FULL_PATH_OF_HDBTYPE_HISTORY_1,

                                                                            OUT OUT_DATA_2 FULL_PATH_OF_HDBTYPE_HISTORY_2)

  LANGUAGE SQLSCRIPT

  READS SQL DATA

  SQL SECURITY INVOKER

  AS

BEGIN

********************************************************

Many other Transaction on data not shown here

********************************************************

–final data to be sent to out parameter

DATA_1=(SELECT  SCENARIOID,

                              LINENUM,

                              SITE,

                              NAME

                FROM HISTORY);

********************************************************

Many other Transaction on data not shown here

********************************************************

–final data to be sent to out parameter

  DATA_2=(SELECT    SCENARIOID,

                                   SHIPPED,

                                   DATESHIPPED,

                                  SOURCE,

                                  CREATEDDATE

                 FROM HISTORY);

END;

              

WRITE PROCEDURE:- Now read procedure is created so we will create one procedure which will call the read procedure and we will read the data into another variables which we will use to insert into target tables.

CREATE PROCEDURE HISTORY_DATA

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS

BEGIN

–call the read procedure to store the data into two table variables

***************************************************************************

CALL HISTORY_DATA_READ (DATA_1_IN,DATA_2_IN);

***************************************************************************

  INSERT INTO SHIPMENT_HISTORY

   (

    SCENARIOID,

    LINENUM,

    SITE,

    NAME

    )(SELECT * FROM :DATA_1_IN);

INSERT INTO SHIPMENT_HISTORY

   (

    SCENARIOID,

    SHIPPED,

    DATESHIPPED,

    SOURCE,

    CREATEDDATE

    )(SELECT * FROM :DATA_2_IN);

END;

So now after separating the procedures into read and write it took only 2.01 Seconds to execute.Conclusion is its always better to use read and write procedure.

So,these are the some points from my work experience on Oracle to Hana Migration Project.

Please share your thought about the post,Advise for further improvement is most welcome ..:)

Happy Reading..:)

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann

    Hey there.

    Sorry, if this seems like a rushed review, but this blog really needs another round of work.

    First off: why is this a blog at all? Why not a WIKI page or document with your collection of notes. In fact it doesn’t really seem to be much more than just that: notes to keep for the next time.

    See through The Difference between a Discussion, Blog Post, Document and Wiki and reconsider the format.

    I fail to find your opinion, your standpoint about anything in here. It’s just a list of self-proclaimed “best practices”.

    Also: where in the text can I find anything on how stuff works in Oracle differently from SAP HANA? The title is pure click-bait in SHOUTING upper case letters.

    Also it indicates that there are more posts like that, but no reference or links are provided.

    The text is not well structured, the formatting makes it hard to read and proof-reading surely would improve the quality as well.

    The worst to me anyhow is that none of the claims you make are actually backed by any evidence.

    Why was the version with the window-function faster than the sequence-version?

    Where did you find that the join-engine and the calc-engine where mixed and that exactly this led to worse performance?

    Rather than this loosely connected lists of statements I’d rather read about your personal experiences. Which development environment did you like more? Why? What where your prior experiences? What would you want to change in SAP HANA …

    There’s so much more interesting stuff you could post instead. Just let it out 🙂

    – Lars

    (0) 
    1. Pankaj Singh Post author

      Hi Lars,


      First of all thanks for your suggestion..:) .I will keep that in mind for my next posts.

      And yes this is my first project and I am working on oracle to hana migration project,I am just 11 month experienced.

      I like working on SAP HANA no doubt in that,i just wanted to highlight the point which everyone should take care while writing SQLSCRIPT in hana.

      1.      I used the sequence in a procedure and i could see the same procedure which runs in seconds takes minutes after sequence usages.

      2.     Same thing with read and write procedure, When i use the read and write procedure it improves the performance of the procedure but when i do all insert and update in the same procedure it takes more time.

               
           Please see the below screen shot of the same, due to some reason i wont be able to post the code here.


      Procedure Execution time when we do read and write in the same procedure.


      R_W_3.PNG

      Procedure Execution time when i read the data in one procedure and store it in table variable and then call the same procedure into another procedure to insert the data into target table.

      R_W_4.PNG

      Regards,

      Pankaj

      (0) 
      1. Lars Breddemann

        Hi Pankaj

        unfortunately you miss the key points.

        I don’t doubt that you like working with SAP HANA or that you experienced the performance differences as you described it.

        But things like system response times are not something superstitious, impenetrable. It can and have to be understood what made the response times.

        On another note, you can still edit this current blog post, in case you want to fix this. No need to wait for the next one. Luckily, the stone plates of SCN blogs are very soft and allow to correct things if required.

        – Lars

        (0) 
  2. VISHAL MAHAJAN

    Hi Pankaj,

    I have gone through the HANA SQL Reference SP08 (page 18) and found that the read-only procedure can only be called by other read-only procedures. Please find the below snapshot for your reference.

    If this is the case then kindly justify the fifth point of your “experience”, in which you have called a read-only procedure inside other procedure(which is not read-only) by using below statement :

    “CALL HISTORY_DATA_READ (DATA_1_IN,DATA_2_IN);”

    Read_Only_Procs.JPG

    Thanks and Regards

    Vishal Mahajan

    (0) 
    1. Lars Breddemann

      Hi Vishal,

      a slight over-interpretation of the documentation. Read-Only procedures can of course be called by other read-only and read-write procedures. The documentation just wanted to make the point that since the procedure described is a read-only procedure as well, it won’t break the optimization options that are available for side-effect free (read-only) procedures.

      – Lars

      (0) 
    2. Pankaj Singh Post author

      Hi Vishal,


      yes you can call the read only procedure inside another read or read-write procedure as Lars already provided the explanation about the document.



      Regards,

      Pankaj



      (0) 

Leave a Reply