SAP HANA is the fastest-growing product in SAP’s 43-year old history. It is an in-memory database engine that can analyze large data sets in real-time. Companies leverage SAP HANA to streamline and automate the data management process. This blog highlights the optimization techniques in SAP HANA and covers the following:
You can improve the performance of any SAP HANA system by ensuring the following points:
The SAP HANA optimization is necessary to avoid termination of the task or process when it breaches the memory threshold (set for each process). It also improves resource utilization, such as memory and CPU time required.
Based on our experience, the following are the best practices for tuning the stored procedures:
While these above-mentioned optimization techniques can help you with SAP HANA optimization, here are a few things that you must avoid in the process:
For a large number of stored procedures that are already present in a different environment, like SQLServer or Oracle, instead of migrating the stored procedures to SAP HANA, leverage the power of HANA by creating a linked server connection between the existing system and SAP HANA. This will reduce the overall effort and cost of development.
Benefits of using a linked server:
Here are a few examples of OpenQuery to fetch the data from HANA:
Select * From OpenQuery(ALEX_HANA, 'Select Distinct "MGMT_REGION", "MYCOMPANY", "CUSTOMER_NAME", "SHIP_TO","KPI_REGION" From "_SYS_BIC"."prd.stg2/CV_INSTALLED" Where "MYCOMPANY" <> '''' Order by MGMT_REGION, MYCOMPANY, SHIP_TO').
Passing Dynamic values using an open query:
DECLARE @TableHANA VARCHAR(256) ,@TableSDA VARCHAR(256) ,@PrimaryKey VARCHAR(MAX) ,@GroupByPrimaryKey VARCHAR(MAX) ,@COLUMNS NVARCHAR(MAX) ,@COL_LEN INT; SET @COLUMNS = ',['; SELECT @COLUMNS = @COLUMNS + ISNULL((NAME + '],['),'') FROM SYS.columns WHERE object_id IN (SELECT OBJECT_ID FROM SYS.VIEWS WHERE NAME = 'X_BSD_25' ) --<====== CHANGE TABLE NAME HERE AND NAME <> 'iKEY_ID'; PRINT @COLUMNS; SET @COL_LEN = LEN(@COLUMNS); SET @COLUMNS = LEFT(@COLUMNS,@COL_LEN - 2 ) PRINT @COLUMNS; IF OBJECT_ID(N'tempdb..#TMPHANATABLE', N'U') IS NOT NULL DROP TABLE #TMPHANATABLE SELECT * INTO #TMPHANATABLE FROM OPENQUERY(ALEX_HANA,'SELECT * FROM "ALEX_CUSTOM"."ZT_X_BSD_25"'); SET @TableHANA = '#TMPHANATABLE' SET @TableSDA = '[dbo].[X_BSD_25]' SET @PrimaryKey = ' ,[iKEY_ID] AS PrimaryKey ' --Paste Field Names and create a single composite Key if more than one primary key fields exist in the field names below PRINT @PrimaryKey PRINT @Columns PRINT @GroupByPrimaryKey EXEC('SELECT PRIMARYKEY, COUNT(*) FROM (SELECT MIN(TableName) AS TableName, PrimaryKey' + @Columns + ' FROM ( SELECT ''' + @TableHANA + ''' AS TableName' + @PrimaryKey + @Columns + ' FROM ' + @TableHANA + ' A ' + 'UNION ALL SELECT ''' + @TableSDA + ''' AS TableName' + @PrimaryKey + @Columns + ' FROM ' + @TableSDA + ' B ' + ') N GROUP BY PrimaryKey' + @Columns + ' HAVING COUNT(*) = 1 ) XYZ GROUP BY PrimaryKey HAVING COUNT(*) > 1 ' );
We hope you liked our first-hand account of SAP HANA optimization techniques in this blog. You can use these techniques to reduce the memory and CPU utilization of your business to get more work done without increasing the size of the system.