Handling large volume of data in HANA

[former_member198758]

Handling large volume of data in HANA

 

Data volume is increasing rapidly in today’s world so handing large volume of data with performance is bit difficult so here we will discuss how we will handle large volume of data in HANA.

We will distribute the data in chunk like instead of handling (DDL/DML) on 3 years of data at a time we can do same thing in chunk by using below concept. We can also use temp table to push all data including all calculation in that temp table so that whenever end user want to see data simply data will pass from temp table which is holding resultant data after all calculations.

Use the below code to handle large no of data in HANA using HANA stored procedure and Intermediate table for better performance.

CREATE PROCEDURE "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_II" ()

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

BEGIN

/*****************************

Write your procedure logic

We can handle large data of sets in chunk for better performance.

*****************************/

DECLARE var_commit  VARCHAR(100) := 'COMMIT';

DECLARE var_rollback VARCHAR(100) := 'ROLLBACK' ;

DECLARE start_date1 nvarchar(8) := to_dats(add_days(to_dats(now()),-600));

DECLARE end_date1 nvarchar(8) := to_dats(add_days(to_dats(now()),-450));

DECLARE start_date2 nvarchar(8) := to_dats(add_days(to_dats(now()),-449));

DECLARE end_date2 nvarchar(8) := to_dats(add_days(to_dats(now()),-350));

DECLARE start_date3 nvarchar(8) := to_dats(add_days(to_dats(now()),-349));

DECLARE end_date3 nvarchar(8) := to_dats(add_days(to_dats(now()),-250));

DECLARE start_date4 nvarchar(8) := to_dats(add_days(to_dats(now()),-249));

DECLARE end_date4 nvarchar(8) := to_dats(add_days(to_dats(now()),-150));

DECLARE start_date5 nvarchar(8) := to_dats(add_days(to_dats(now()),-149));

DECLARE end_date5 nvarchar(8) := to_dats(add_days(to_dats(now()),-51));

DECLARE start_date6 nvarchar(8) := to_dats(add_days(to_dats(now()),-50));

 

DECLARE status_code integer;

DECLARE s_msg_str NVARCHAR(256);

SRC_SYS := 'NA';

CALL "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_SUB_PROC" (

'ABC_SUMMARY_II_NA1' ,

:SRC_SYS , start_date1 , end_date1 , :status_code );

 

CALL "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_SUB_PROC" (

'ABC_SUMMARY_II_NA2' ,

:SRC_SYS, start_date2 , end_date2 , :status_code );

 

CALL "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_SUB_PROC" (

'ABC_SUMMARY_II_NA3' ,

:SRC_SYS, start_date3 , end_date3 , :status_code );

 

CALL "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_SUB_PROC" (

'ABC_SUMMARY_II_NA4' ,

:SRC_SYS, start_date4 , end_date4 , :status_code );

 

CALL "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_SUB_PROC" (

'ABC_SUMMARY_II_NA5' ,

:SRC_SYS, start_date5 , end_date5 , :status_code );

 

CALL "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_SUB_PROC" (

'ABC_SUMMARY_II_NA6' ,

:SRC_SYS, start_date6 , '20991231' , :status_code );

WE can follow same approach for other region like APAC, Europe, LA & NA .

 

SUB Procedure

CREATE PROCEDURE "MY_CUSTOM"."DEMO.MY_CUSTOM::ABC_SUMMARY_SUB_PROC" (

IN ROUT_NAME NVARCHAR(40),

IN SRC_SYSTEM varchar(2), --NVARCHAR(10),

IN BEG_DATE NVARCHAR(8),

IN END_DATE NVARCHAR(8),

OUT STATUS_CODE integer)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

--DEFAULT SCHEMA <default_schema_name>

AS

BEGIN

/*****************************

Write your procedure logic

*****************************/

DECLARE var_commit  VARCHAR(100) := 'COMMIT';

DECLARE var_rollback VARCHAR(100) := 'ROLLBACK' ;

DECLARE s_msg_str NVARCHAR(256);

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

exec (:var_rollback);

s_msg_str := LEFT('Error Code '||::SQL_ERROR_CODE||::SQL_ERROR_MESSAGE,256);

insert into "MY_CUSTOM"."MY_CUSTOM_UPD_LOGGING" values(:ROUT_NAME, v_JOB_TS, CURRENT_UTCTIMESTAMP, 'ErrorG', s_msg_str);

Status_Code := 1;

END;

-- Truncate the Temp table

TRUNCATE TABLE "MY_CUSTOM"."ABC_SUMMARY_II_TMP";

COMMIT;

-- Update message after temp truncate

INSERT INTO "MY_CUSTOM"."MY_CUSTOM_UPD_LOGGING" values(:ROUT_NAME, v_JOB_TS,CURRENT_UTCTIMESTAMP, 'MSG', 'TMP Truncate Completed');

COMMIT;

 

-- Select and insert values into the temp table for the provided selection

INSERT INTO "MY_CUSTOM"."ABC_SUMMARY_II_TMP"

("SRC_SYS",

"MANDT",

"VBELN",

"POSNR",

"SUMM_GROUPING_DERIV",

"SUMM_GROUPING_DESC_DERIV"

)

(SELECT

"SRC_SYS",

"MANDT",

"VBELN",

"POSNR",

"SUMM_GROUPING_DERIV",

"SUMM_GROUPING_DESC_DERIV"

 

FROM "_SYS_BIC"."DEMO.MY_ABC/CV_MY_ABC_MY_GLOBAL_ABC" (

PLACEHOLDER."$$TGTDLVEDDT$$"=> :end_date, -- End Date

PLACEHOLDER."$$TGTDLVSTDT$$"=> :beg_date) --Start Date

WHERE "SRC_SYS" = :SRC_SYSTEM

--WHERE "SRC_SYS" IN (:SRC_SYSTEM) --"TGT_DATE" <= '2016-12-31'

GROUP BY "SRC_SYS",

"MANDT",

"VBELN",

"POSNR",

"SUMM_GROUPING_DERIV",

"SUMM_GROUPING_DESC_DERIV"

)

ORDER BY 1, 2, 3;

COMMIT;

 

INSERT INTO "MY_CUSTOM"."ABC_SUMMARY_II"

("SRC_SYS",

"MANDT",

"VBELN",

"POSNR",

"SUMM_GROUPING_DERIV",

"SUMM_GROUPING_DESC_DERIV"

)

(SELECT

"SRC_SYS",

"MANDT",

"VBELN",

"POSNR",

"SUMM_GROUPING_DERIV",

"SUMM_GROUPING_DESC_DERIV"

 

FROM "MY_CUSTOM"."ABC_SUMMARY_II_TMP");

COMMIT;

 

TRUNCATE TABLE "MY_CUSTOM"."ABC_SUMMARY_II_TMP";

END;

 

This approach will  dramatically increase the performance of the report.