Recommendations for SQLScript in CDS Views and ABAP Programing

[qrestik_tech]

Recommendations for SQLScript

1. Keep SQL statement simple:
* Complex SQL hard for HANA SQL optimizer
* Break down complex SQL into multiple simpler statements using variables

2. Avoid dependency between SQL statements:
* Dependency depresses parallel execution of statements

3. Avoid mixture SQL and CE functions:
* Increase overhead of engine switching and limit the optimization
* Break into sub-procedures

4. Avoid imperative logic:
* Loops and cursor processing make parallelization difficult

5. Leverage HANA views:
* Break down complex requirements and model parts of task with views as much as possible
* Use strengths of different HANA engines to process views

6. Avoid dynamic SQL:
* Recompile for each procedure call
* Potential security problem

7. Understand the Costs of Statements:
* Employ the visualize plan facility to investigate the performance impact of different SQL queries

Best Practices for Using SQLScript

1. Reduce Complexity of SQL Statements:
* Variables in SQLScript enable you to arbitrarily break up a complex SQL statement into many simpler ones
* This makes a SQLScript procedure easier to comprehend. Simple example:

articles_per_category = SELECT category, COUNT (*) AS cnt FROM :articles GROUP BY category;
largest_categories = SELECT * FROM :articles_per_category WHERE cnt >= (SELECT MAX (cnt) FROM :articles_per_category);

* Writing this in a single SQL query would result in a complex statement using WITH
* The SQLScript compiler will combine these statements into a single query
* The resulting application program is easier to understand without sacrificing performance

2. Reduce Dependencies

* Massive parallelization of query execution is one of the most important methods of speeding up processing in HANA
* Parallelization occurs on different levels of granularity, e.g.
– Parallel execution of multiple users’ requests
– Single relational operators within a query are executed on multiple cores
– Execution of different statements of a single SQLScript in parallel, if the statements are independent of each other
* From the developer perspective, it is thus important to
– Avoid unnecessary dependencies between separate SQL statements
– Use declarative constructs where possible (avoid imperative features)

[Author]

Posts