HANA XSA DESIGN Principle to optimize the database performance –

1.If you partition a table by a sensible key (date, or an attribute like active/inactive).

partitioning strategy: On a single node appliance 3-5 partitions is normally a good number for optimal insert performance. On a scale-out appliance, you can multiply this by the number of active nodes over which you distribute the tables.

ROUNDROBIN PARTITIONS is preferable
*————————————————————————————————————————————————–

DROP TABLE “SCHEMA”.”TABLE”;

CREATE COLUMN TABLE “SCHEMA”.”TABLE” (

“TIME” TIME,

“FIELD1” CHAR(1),

“FIELD2” CHAR(16),

“FIELD3” CHAR(4),

“MEASURE1” INT,

“MEASURE2” DECIMAL(7,4))

PARTITION BY ROUNDROBIN PARTITIONS 15;

*————————————————————————————————————————————————–

2.HANA keeps a compressed “main” store of column tables and an uncompressed “delta” store, for new items. Periodically, a process called mergedog combines the two in a DELTA MERGE process.
Sometimes however your tables may not be configured to automerge (you can issue ALTER TABLE table name ENABLE AUTOMERGE), and occasionally merged fails and you have to restart HANA. This can cause the delta store to become very large, and because it is uncompressed, this is a big issue if you don’t have much RAM.

**————————————————————————————————————————————————–

ALTER TABLE “SCHEMA”.”TABLE” DISABLE AUTOMERGE;

IMPORT FROM ‘/sapmnt/log/test.ctl’ WITH THREADS 120 BATCH 200000;

MERGE DELTA OF “SCHEMA”.”TABLE”;

ALTER TABLE “SCHEMA”.”TABLE ENABLE AUTOMERGE;

**———————————————————————————————————————————————–

3. Implement Smart Merge :

For example, if an application starts loading relatively large data volumes, a delta merge during the load may have a negative impact both on the load performance and on other system users. Therefore, the application can disable the auto merge for those tables being loaded and send a “hint” to the database to do a merge once the load has completed.
*—————————————————————————————————————————–
Smart merge is active if the smart_merge_enabled parameter in the mergedog section of the indexserver.ini file is set to yes.

Before Initial Data load large dataset table Procedure executed.

PROCEDURE Disable_AutoMerge( IN table_name)

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN

ALTER TABLE “SCHEMA”.”TABLE” DISABLE AUTOMERGE;
ALTER TABLE “SCHEMA”.”TABLE” DISABLE SMART_MERGE;

END

After initial load :

PROCEDURE Enable_AutoMerge( IN table_name)

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN

ALTER TABLE “SCHEMA”.”TABLE” DISABLE SMART_MERGE;
ALTER TABLE “SCHEMA”.”TABLE” ENABLE AUTOMERGE;

END

*———————————————————————————————————————————

4. Monitoring Delta Merge History :
Information about all delta merge operations since the last system start are logged in the monitoring view M_DELTA_MERGE_STATISTICS.

Procedure created to check Delta merge history to check the delta table details –

PROCEDURE Disable_AutoMerge( OUT Table (table name varchar(100),…))

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
SELECT * FROM M_DELTA_MERGE_STATISTICS WHERE table_name = ‘<your_table>’ AND motivation = ‘SMART’;
SELECT * FROM M_DELTA_MERGE_STATISTICS WHERE type = ‘HINT’ AND table_name = ‘<your_table>’;
SELECT * FROM _SYS_STATISTICS.HOST_DELTA_MERGE_STATISTICS WHERE table_name = ‘<your_table>’;

END

3. Keep tables as narrow as possible. Although Recommended we can go till average 80 column. we try to keep it in under 40-50 column if possible.
4. On a single node appliance 3-5 partitions is normally a good number for optimal insert performance.
5. Do not use the HANA database for referential integrity if you can, it is expensive.
6. Avoid using VARCHARs if you don’t need them. use CHAR , and INTEGER fields are even better. varchar do not compress
7. PARTITION BY ROUNDROBIN PARTITIONS 12 (node 3 each node 4 partitions).

8. Health check of the calculation view –

Execute out of the box procedure to check the health of a calculation view –

CALL CHECK_ANALYTICAL_MODEL('','SCHEMA1','CV_DEMO_TEST',?);

Get the list of available health check history  –

CALL CHECK_ANALYTICAL_MODEL('LIST','','',?);