Good practices Flashcards
Best pratices
- Avoid using Att and Analy views.
2. Avoid mixing CDS views CV.
Unfolding can damage Performance sometimes.
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.
SQL Analyser
You can check if CV is unfolded , if col view is called that means it is not unfolded.
Not Unfolded
M_FEATURE_USAGE.
JOIN Cardinalities
- If tables are used , then we can use Propose .
2. Ensure Correct Ness.
Perquisite of Join Pruning
- No fields from the to be pruned table.
- Outer , Ref or text
- Join Cardanility is either :1 for to be pruned or only measures with aggregation or no measure at all.
Refrential Join allows Join prunning if
- No fields from the to be pruned table.
- Integrity is placed on not to be pruned.
- Cardanilty on to be pruned is :1
Filter Pushdown
Ignore multiple output for filer can be set to have filter pushdown.
Joins Recommendations
- Maintain cardinality of the Join.
- Left Outer N:1 or 1:1 and Right outer 1:N, 1:1
- Reduce number of Join fields .
- Avoid Joining on Calculated fields.
- Check Dynamic or Optimise Join can be used.
List of values
- faster.
- Consistent across views.
- Support for analytical priv.
Column Prunning.
Add only column that are required in the output.
Filtering and Column Calculations.
- Verify filters are push down.
2, Try to avoid filters on Calculated Columns.
Optimal Aggregations.
- Try to aggregate as early as possible.
- do not switch measures with attributes.
- For Star Join use Start join model only , so not mix with sequence of joins.
Cache
- Can be used for view where there is no Analytical Priv.
- Analytics privileges can be assigned on top most view in the stack.
- Cache can be done at the Column level , in the cachc add only those columns that are requried .
- Cache size can futher reduced with the help of filters.
- Retention period of cache in MIN,
- It is currently not possible to define the cache invalidation if data is chaneged.
Cache prequsites.
- enable cache.
- CV can be unfolded.( Explain plan in SQL Analyser).
- No Granulity tracking calculations.
Using the Cache
- DB hint in the top CV.
- Hint is added to query.
- Configuration parameter is set.
For Parallezation:Partition Local Execution Flag
- Only project or Aggregation is allowed .
- Source col is defined for Partiniong value.
- To end , Union is used and property ON.
- Always starts with a node that includes Table only.( No Table function)
Parallelization
- No Parallezation Across views.
- Only 1 parallezation block per view per stack.
- Multiple start nodes can be there but partition should be defined.
UNION Pruning
- Explicit - Adding Constant value.
2. Implicit - Using the Pruning Config table
Performance Analysis Mode
- Need not to activate or build the CV.
- Design time warnings and other Information.
- Can be switched on all the tabs except Semantics.
Performance Analysis Mode examples
- Type of tables used( Col or Row)
- Join details(Type, Cardanility and no of rec in Join.
- Partition Details( Tables are partinioned , how they are defined, and number of rec in partitions).
- Warnings
Performance Analysis Mode warnings
- large table warnings.
- missing cardinalities
- Join based on cal view.
- restricted col based on Calculated col.
- Filters are Pushed down.
Performance Analysis Mode does not support Join nodes which consist of multiple Join definitions.
T/F
True.
Debug Query Mode
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.
SQL Analyzer.
SQL performs.
- Total Execution time .
- How long each step took.
- Bottleneck steps.
- How many rec each step process.
- Sequence
- Operator.
- Parallezation.
- processing engine,
- Unfolding.
SQL analyzer can be launched
- Database explorer.
- Right click and analyze SQL.
Can be used where ever we have SQL for e,g proc and functions.
Table partitioning advantages on Col table,
- Load balancing in distributed system,
- Parallezation using multiple threads.
- Pruning-Only hits required partitions, hence improving performance.
- 2 Billion row limit for each table , Partitions too have
2 billion row limit , but you can define 16 k partitions. - Improves Delta merge.
Table partitions
- Range.( may or may not have primary key) you define the range.
- 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.)
Table partitions- How to do it.
SQL statement.
CDS defination.
Partition is transparent to SQL statement.
Table partitions-Features
- Repartition
- merge
- add/delete new partitions.
- Move partitions to other host.
Multi level partitions.
Hierarchy of partions.
Partitions on ROW store table
No
Warm Data
- Native Storage extension(NSE) ( latest uses Disk)
- Extension Node( Fastest , need more hardware).
- Dynamic tiering,( slowest , uses Multi store tables).
