The following optimization all apply to statements in SQLScript. The optimizations presented here cover how dataflow exploits parallelism in the SAP HANA Database: (7 + 2) = 9 bullets
- Reduce complexity of SQL statements: Break up a complex SQL statements into simpler ones.
- Identify common sub-expressions: if you split a complex query into logical sub queries it can help the optimizer to identify common sub expressions and to derive more efficient execution plans.
- Multi-Level-Aggregations:In the special case of multi-level aggregations, SQLScriipt can exploit results at a finer grouping for computing coarser aggregations and return the different granularities of groups in distinct table variables.
- Understand the cost of statements: Explain plan facility to investigate the performance impact of different SQL queries.
- Exploit underlying Engine: SQLScript can exploit the specific capabilities of the OLAP- and Join-Engine by relying on views modeled appropriately
- Reduce dependencies: As SQLScript is translated into a dataflow graph, and independent paths in this graph can be executed in parallel, reducing dependencies enables better parallelism, thus better performance.
- Avoid mixing Calculation Engine plan operators and SQL Queries: Mixing calculation engine plan operators and SQL may lead to missed opportunities to apply optimizations as calculation engine plan operators and SQL statements are optimized independently.
- Avoid using Cursors: Check if use of cursors can be replaced by ( a flow of ) SQL statements for better opportunities for optimization and exploiting parallel execution
- Avoid using Dynamic SQL; Executing dynamic SQL is slow because compile time checks and query optimization must be done for every invocation of the procedure. Another related problem is security because constructing SQL statements without proper checks of the variables used may harm security.
No comments:
Post a Comment