I am new in supporting SQL views build on HANA XS on prem and came to a system behaviour that I cannot explain.
There is a SQL view that will select from several tables incl. JCDS. When the view is called too often, the system reaches the cpu limit. The only big table in the view is JCDS that has 4 Keys MANDT, OBJNR, STAT, CHGNR and ~3 billion entries. All other tables are significantly smaller. When analysing the view I see that data from JCDS is selected based on OBJNR and STAT.
As there is no MANDT maintained I assume that an index search is not used but a full table scan is performed. When executing the select for a single line from JCDS with the given filter criteria I get a response time of ~90ms (>81ms && <97ms). When also adding the field for MANDT I get a response time that is on average 76ms.
There seems to be no relevant performance improvement when maintaining the additonal key field.
Can this be explained as the whole table is already stored in memory and the binary search for the index does not have too much impact?
Are keys in HANA XS used in hierarchical manner? When I maintain only the first 3 of 4 key fields will this already limit down the results based on binary search? Or is an index search only performed when the keys are fully maintained?
Can you recommend me a documentation I can use to gather additional knowledge on developing views in HANA XS?
Select * from JCDS where OBJNR = $OBJNR and STAT = $STAT
==> Response Time avg 90ms
Select * from JCDS where MANDT = 100 and OBJNR = $OBJNR and STAT = $STAT
==> Response Time avg 76ms