Friday, 30 March 2018

Technical details about data aging Part II

To understand the data aging in detail we will go a little bit deeper in this part of the blog series.

As example we will use table CDPOS. Data aging is in use and aging runs already were executed. As result we get this data distribution.

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio
SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

So if we select the record count via SE16 we will get the following result:

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

Here we can see the partitioning attributes and the record count of each partition

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> all partitions are loaded partially like mostly in business time
=> we also see the partitiong as configured in the ABAP backend

SQL Test


Now we will check different statements (also with count clause):

1) select * from CDHDR;
2) select * from CDHDR where MANDANT='100';
3) select * from CDHDR with RANGE_RESTRICTION ('CURRENT')
4) select * from CDHDR where MANDANT='100' with RANGE_RESTRICTION ('2016-11-01');
5) select * from CDHDR where MANDANT='100' with RANGE_RESTRICTION ('0001-01-01');
6) select * from CDHDR where MANDANT='100' with RANGE_RESTRICTION ('CURRENT');​
7) select * from CDHDR with RANGE_RESTRICTION ('0001-01-01');

Test results


Row Count
SQL1 88.138 
SQL2 52.226
SQL3  36.884 
SQL4  51.281 
SQL5  52.226 
SQL6  972 
SQL7  88.138 

=> interesting is, that you can achive the same results with and without the range partitioning – which should not work if we can believe the SAP notes

DBACockpit => Diagnostics => SQL Editor

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> here you can also save the execution plan and import it into HANA Studio for a detailed analyses

Details – Plan viz


SQL1

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> we see a search without filters on all partitions
=> the exec plan is identically to SQL7 => no dynamic search is used here
=> time spend for all parts are pretty low, because some of them are loaded into memory and the row count is also pretty low

SQL4

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> now we see a dynamic search in cause of using the range restriction
=> but this time not on partition 2 (00010101 – 20160101)

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> closer look into the dynamic search
=> we see the filter on MANDANT on main and delta store of the partition 4

Same query with unloaded partitions besides current (part id 1)


SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> in the overview we see that the expensive part is on a new operator ‘Delta Log Replay’

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> the select on the current partition is still fast
=> but on the unloaded partitions there must be executed a delta log replay on first access/load

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> the expensive ones are partition 3 and 4
=> so if you have big partitions which are not accessed frequently you can run into performance issues on first access
=> 3,3ms (loaded partitions) vs. 292,5ms (first access on historical partitions) = factor 97 slower

SQL6

SAP HANA Tutorials and Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Guides, SAP S/4HANA, SAP HANA, SAP HANA Studio

=> on the current partition we see also the dynamic search with the select on both parts (main/delta) – this time with some results on the delta store