MY EXPERIENCE OF ORACLE TO HANA MIGRATION PROJECT-PART III

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.

                                             

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.

                                                

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.

                              

    

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

                                 

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.

                   

                                   

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

               

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.

                    

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.

              

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

            

         

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..:)