Good practices

Question 1

Best pratices

Answer 1

1. Avoid using Att and Analy views.

2. Avoid mixing CDS views CV.

Question 2

Unfolding can damage Performance sometimes.

Answer 2

Certain time Query unfolding can impact performance.
Such cases we need to add Hint.
Semantics-> Properties->Advanced-> Hint.
For e.g Compilation time can be increased.

Question 3

SQL Analyser

Answer 3

You can check if CV is unfolded , if col view is called that means it is not unfolded.

Question 4

Not Unfolded

Answer 4

M_FEATURE_USAGE.

Question 5

JOIN Cardinalities

Answer 5

1. If tables are used , then we can use Propose .

2. Ensure Correct Ness.

Question 6

Perquisite of Join Pruning

Answer 6

1. No fields from the to be pruned table.
2. Outer , Ref or text
3. Join Cardanility is either :1 for to be pruned or only measures with aggregation or no measure at all.

Question 7

Refrential Join allows Join prunning if

Answer 7

1. No fields from the to be pruned table.
2. Integrity is placed on not to be pruned.
3. Cardanilty on to be pruned is :1

Question 8

Filter Pushdown

Answer 8

Ignore multiple output for filer can be set to have filter pushdown.

Question 9

Joins Recommendations

Answer 9

1. Maintain cardinality of the Join.
2. Left Outer N:1 or 1:1 and Right outer 1:N, 1:1
3. Reduce number of Join fields .
4. Avoid Joining on Calculated fields.
5. Check Dynamic or Optimise Join can be used.

Question 10

List of values

Answer 10

1. faster.
2. Consistent across views.
3. Support for analytical priv.

Question 11

Column Prunning.

Answer 11

Add only column that are required in the output.

Question 12

Filtering and Column Calculations.

Answer 12

1. Verify filters are push down.

2, Try to avoid filters on Calculated Columns.

Question 13

Optimal Aggregations.

Answer 13

1. Try to aggregate as early as possible.
2. do not switch measures with attributes.
3. For Star Join use Start join model only , so not mix with sequence of joins.

Question 14

Cache

Answer 14

1. Can be used for view where there is no Analytical Priv.
2. Analytics privileges can be assigned on top most view in the stack.
3. Cache can be done at the Column level , in the cachc add only those columns that are requried .
4. Cache size can futher reduced with the help of filters.
5. Retention period of cache in MIN,
6. It is currently not possible to define the cache invalidation if data is chaneged.

Question 15

Cache prequsites.

Answer 15

1. enable cache.
2. CV can be unfolded.( Explain plan in SQL Analyser).
3. No Granulity tracking calculations.

Question 16

Using the Cache

Answer 16

1. DB hint in the top CV.
2. Hint is added to query.
3. Configuration parameter is set.

Question 17

For Parallezation:Partition Local Execution Flag

Answer 17

1. Only project or Aggregation is allowed .
2. Source col is defined for Partiniong value.
3. To end , Union is used and property ON.
4. Always starts with a node that includes Table only.( No Table function)

Question 18

Parallelization

Answer 18

1. No Parallezation Across views.
2. Only 1 parallezation block per view per stack.
3. Multiple start nodes can be there but partition should be defined.

Question 19

UNION Pruning

Answer 19

1. Explicit - Adding Constant value.

2. Implicit - Using the Pruning Config table

Question 20

Performance Analysis Mode

Answer 20

1. Need not to activate or build the CV.
2. Design time warnings and other Information.
3. Can be switched on all the tabs except Semantics.

Question 21

Performance Analysis Mode examples

Answer 21

1. Type of tables used( Col or Row)
2. Join details(Type, Cardanility and no of rec in Join.
3. Partition Details( Tables are partinioned , how they are defined, and number of rec in partitions).
4. Warnings

Question 22

Performance Analysis Mode warnings

Answer 22

1. large table warnings.
2. missing cardinalities
3. Join based on cal view.
4. restricted col based on Calculated col.
5. Filters are Pushed down.

Question 23

Performance Analysis Mode does not support Join nodes which consist of multiple Join definitions.
T/F

Answer 23

True.

Question 24

Debug Query Mode

Answer 24

Check SQL of each step how it is generated, unfiltered i.e all the col from the view.

Can be used for Union Pruning is happening.
Icon is like a bug.

During the Debug Query Mode, cv is only in Read Only , you can not edit it.

The SQL generated , can be executed with the icon near by.

Pruned col are greyed out .

You need to build the CV , only active version /build version you will see Debug query mode.

Question 25

SQL Analyzer.

Answer 25

SQL performs.

1. Total Execution time .
2. How long each step took.
3. Bottleneck steps.
4. How many rec each step process.
5. Sequence
6. Operator.
7. Parallezation.
8. processing engine,
9. Unfolding.

Question 26

SQL analyzer can be launched

Answer 26

1. Database explorer.
2. Right click and analyze SQL.
   Can be used where ever we have SQL for e,g proc and functions.

Question 27

Table partitioning advantages on Col table,

Answer 27

1. Load balancing in distributed system,
2. Parallezation using multiple threads.
3. Pruning-Only hits required partitions, hence improving performance.
4. 2 Billion row limit for each table , Partitions too have
   2 billion row limit , but you can define 16 k partitions.
5. Improves Delta merge.

Question 28

Table partitions

Answer 28

1. Range.( may or may not have primary key) you define the range.
2. Hash may or may not have primary key), SAP hana
   computes HASH values.
   3.Round robin( table must not have Primary keys, new rows are assigned on rotation basis.)

Question 29

Table partitions- How to do it.

Answer 29

SQL statement.
CDS defination.

Partition is transparent to SQL statement.

Question 30

Table partitions-Features

Answer 30

1. Repartition
2. merge
3. add/delete new partitions.
4. Move partitions to other host.

Question 31

Multi level partitions.

Answer 31

Hierarchy of partions.

Question 32

Partitions on ROW store table

Answer 32

No

Question 33

Warm Data

Answer 33

1. Native Storage extension(NSE) ( latest uses Disk)
2. Extension Node( Fastest , need more hardware).
3. Dynamic tiering,( slowest , uses Multi store tables).