SAPHANA开发者性能优化指南

需积分: 9 158 浏览量更新于2024-07-15 收藏 6.26MB PDF 举报

"SAPHANAPlatform2.0SPS05的HANA数据库开发性能指南，主要涵盖表类型选择、索引创建、分区、查询处理、SQL查询性能优化等内容，适用于HANA数据库的开发者。" HANA数据库是SAP公司的一款高性能内存数据库系统，特别适合实时分析和数据处理。这份《HANA性能开发指南》提供了有关如何优化HANA数据库性能的详细建议和最佳实践。 1. **模式设计** - 在设计HANA数据库时，正确的模式选择至关重要。这包括： - **选择适当的表类型**：HANA支持多种表类型，如 COLUMNSTORE 和 ROWSTORE。COLUMNSTORE 适用于分析查询，ROWSTORE 适用于事务处理。根据应用需求选择合适的类型可以提升性能。 - **创建索引**：索引能加速数据检索，但也会占用额外的存储空间并可能增加写操作的开销。主键和次级索引是常见的索引类型，而多列索引类型在特定场景下可以提供更好的查询性能。 - **分区策略**：对于大型表，分区可以提高查询性能。通过将大表分成更小、更易管理的部分，可以根据日期、地理位置或其他逻辑标准进行查询。 2. **查询处理** - 指南提供了查询处理的示例，包括如何利用delta和主表来处理增量数据，以及如何通过denormalization（反规范化）减少JOIN操作以提升性能。 3. **SQL查询性能** - SQL是与HANA交互的主要方式，理解其执行引擎的工作原理非常重要： - **SQL处理组件**：SQL查询的执行涉及多个组件，包括解析器、优化器和执行器。解析器将SQL语句转换成可执行的计划，优化器选择最佳执行路径，执行器则实际执行计划。 - **SQL优化器**：分为规则基础优化和成本基础优化。规则基础优化基于预定义的规则，而成本基础优化通过估算不同执行计划的成本来决定最优方案。 - **SQL查询优化步骤**：从查询解析到执行计划的生成，涉及到一系列步骤，包括语法检查、语义分析、优化决策和计划生成。 4. **分析工具** - HANA提供多种工具来帮助开发者分析和优化SQL查询： - **SQL计划缓存**：保存已优化的查询计划，用于重复查询的快速执行。 - **解释计划**：显示SQL查询的执行步骤和预计资源消耗，帮助识别性能瓶颈。 - **计划可视化器**：图形化展示查询执行计划，便于理解和调整。 - **SQL跟踪**：记录查询的详细执行信息，用于故障排查和性能调优。该指南旨在帮助HANA开发者深入了解系统内部工作原理，从而编写出更高效、更优化的SQL查询，提升整个数据库系统的性能。对于那些希望最大化利用HANA内存计算能力的开发人员来说，这份文档是一个宝贵的参考资料。

separate from those of the other partitions. As a result and depending on the actual value distribution and

partitioning criteria, the main memory consumption of a table might increase or decrease when it is changed

from a non-partitioned to a partitioned table. While this does not initially appear very intuitive, the root cause

for this lies in the dictionary compression that is applied.

For example:

● Increased memory consumption due to partitioning

A table has two attributes, MONTH and YEAR, and contains data for all 12 months and two distinct years

(2013 and 2014). When the table is partitioned by YEAR, the dictionary for the MONTH attribute needs to be

held in memory twice (both for 2013 and 2014), therefore increasing memory consumption.

● Decreased memory consumption due to partitioning

A table has two attributes, GENDER and FIRSTNAME, and stores data about German customers. When the

table is partitioned by GENDER, it is divided into two groups (female and male). In Germany, there is a

limited set of rst names for both females and males. As a result, the FIRSTNAME dictionaries are implicitly

partitioned as well into two almost distinct groups, both containing almost n/2 distinct values, compared

to the unpartitioned table with n distinct values. Therefore, to represent those values in the index vector,

only n-1 bits are required instead of n bits in the original table. As there is virtually no redundancy in the

dictionaries, memory consumption can be reduced by partitioning.

3.4 Query Processing Examples

The examples below show how the dierent access paths and optimization techniques described above can

signicantly inuence query processing .

Exploiting Indexes

This example shows how a query with multiple predicates can potentially benet from the dierent indexes that

are available. The query used in the example is shown below, where the table FOO has a primary key for MANDT,

BELNR, and POSNR:

SELECT * FROM FOO

WHERE MANDT='999' and BELNR='xx2342'

No Indexes: Attribute Scans

A straightforward plan would be to scan both the attributes MANDT and BELNR to nd all matching rows and

then materialize the result set for those rows where both criteria have been fullled. Since the column store

uses dictionary compression, the system rst needs to look up the corresponding value IDs from the dictionary

during predicate evaluation (MANDT='999' and BELNR='xx2342'). It does this with a binary search operation

on the sorted dictionary for the main table, which means

log k, where k is the number of distinct values in the

main table. For the delta table, there are auxiliary structures that allow the value IDs to be retrieved with the

same degree of complexity from the unsorted delta dictionary. After that, the scan operation can be performed

to compare the value IDs. The scan operations are run sequentially so that if the rst scan already reduces the

result set signicantly, further scanning can be avoided and the values of the individual rows looked up instead.

PUBLIC

SAP HANA Performance Guide for Developers

Schema Design

This is also one of the reasons why query execution tries to start with the evaluation of the most selective

predicate rst (for example, it is more likely that BELNR will be evaluated before MANDT, depending on the

selectivity estimations).

Conceptually, the runtime for these scans is 2*n, where n is the number of values in the table. However, the

actual runtime depends on the number of distinct values in the corresponding column. For attributes with very

few distinct values (for example,

MANDT), it might be sucient to use a small number of bits to encode the

dictionary values (for example, 2 bits). Since the SAP HANA database scan operators use SIMD instructions

during processing, multiple-value comparisons can be done at the same time, depending on the number of bits

required for representing an entry. Therefore, a scan of n records with 2 bits per value is notably faster than a

scan of n records with 6 bits (an almost linear speedup).

In the last step of query processing, the result set needs to be materialized. Therefore, for each cell (that is,

each attribute in each row), the actual value needs to be retrieved from the dictionary in constant time.

Single-Column Indexes

To improve the query processing time, the system can use the single-column indexes that are created for each

column of the key. Instead of doing the column scan operations for MANDT and BELNR, the indexes can be used

to retrieve all matching records for the given predicates, reducing the evaluation costs from a scan to a

constant-time lookup operation for the column store. The other costs (combining the two result sets,

dictionary lookup, and result materialization) remain the same.

Concatenated Indexes

When a concatenated index is available, it is preferrable to use it for query processing. Instead of having to do

two individual index-backed search operations on MANDT and BELNR and combine the results afterwards (AND),

the query can be answered by a single index-access operation if a concatenated index on (MANDT, BELNR) is

available. In this particular example this is not the case because the primary key also contains the POSNR

predicate and therefore cannot be used directly. However, in this special case, the concatenated index of the

primary key can still be exploited. Since the query uses predicates that form a prex of the primary key, the

search can be regarded internally as semantically equivalent to SELECT * FROM FOO WHERE MANDT='999'

and BELNR='xx2342' and POSNR like '%'. Since the SAP HANA database engine internally applies a

similar rewrite (with a wildcard as the sux of the concatenated attributes), the concatenated index can still be

used to accelerate the query.

When this example is actually executed in the system, the concatenated index is exploited as described above.

Indexes Versus Partitioning

Both indexes and partitioning can be used to accelerate query processing by avoiding expensive scans. While

partitioning and partition pruning reduce the amount of data to be scanned, the creation of indexes provides

additional, alternate access paths at the cost of higher memory consumption and maintenance.

Partitioning

If partition pruning can be applied, this can have the following benets:

● Scan operations can be limited to a subset of the data, thereby reducing the costs of the scan.

● Partitioning a table into smaller chunks might enable the system to represent large query results in a more

ecient manner. For example, a result set of hundreds of thousands of records might not be represented

SAP HANA Performance Guide for Developers

Schema Design

PUBLIC 17

as a bit vector for a huge table with billions of records, but this might be feasible if the table were

partitioned into smaller chunks. Consequently, result set comparisons (AND/OR of several predicates) and

handling might be more ecient in the partitioned case.

Note that these benets heavily depend on having matching query predicates. For example, partitioning a table

by YEAR is not benecial for a query that does not include YEAR as a predicate. In this case, query processing

will actually be more expensive.

Indexes

Indexes can speed up predicate evaluation. The more selective a predicate is, the higher the gain.

Combining Partitioning and Indexes

For partitioning, the greatest potential for improvement is when the column is not very selective. For indexing, it

is when the column is selective. Combining these techniques on dierent columns can be very powerful.

However, it is not benecial to use them on the same column for the sole purpose of speeding up query

processing.

3.5 Delta Tables and Main Tables

Each column store table consists of two distinct parts, the main table and the delta table. While the main table

is read only, heavily compressed, and read optimized, the delta table is responsible for reecting changes made

by DML operations such as INSERT, UPDATE, and DELETE. Depending on a cost-based decision, the system

automatically merges the changes of the delta table into the main table (also known as delta merge) to improve

query processing times and reduce memory consumption, since the main table is much more compact.

The existence and size of the delta table might have a signicant impact on query processing times:

● When the delta table is not empty, the system needs to evaluate the predicates of a query on both the delta

and main tables, and combine the results logically afterwards.

● When a delta table is quite large, query processing times may be negatively aected, since the delta table

is not as read optimized as the main table.

Therefore, by merging the delta table into the main table to reduce main memory consumption, delta merges

might also have a positive impact on reducing query processing times. However, a delta merge also has an

associated cost, which is mostly linear to the size of the main table. A delta merge should therefore only be

performed after weighing the improvement in memory consumption and query processing times against this

cost. In the case of automatic merges, it has already been considered in the cost function.

Data Compression

After merging the contents of the delta table into the main table during the delta merge process, the system

might optionally run an additional data compression step to reduce the main memory footprint of the main

table part. This process is also known as optimize compression. Internally, the SAP HANA database system

contains multiple compression methods (run length encoding, sparse coding, default value, and so on). While

the most ecient compression mechanisms are automatically chosen by the system, the compression

mechanism that is applied might also aect the query processing times. It is normally not necessary to

PUBLIC

SAP HANA Performance Guide for Developers

Schema Design

manually alter the compression methods, or even uncompress the table, but this can be done when there is a

problem with data compression. Generally, however, you should contact SAP Support when there is a problem

with data compression.

3.6 Denormalization

Denormalization can be applied as an additional tuning mechanism to improve performance. The idea of

denormalization is to combine data that was previously kept in dierent tables into a single combined table to

avoid the overhead of join processing. In most cases, this introduces some redundancy into the underlying

dataset (for example, by repeating customer addresses in multiple orders instead of storing them in a separate

master data table), but potentially speeds up query processing.

In terms of relational theory, denormalization is a violation of good database design practices, since it

deliberately causes violations of normal forms, thereby increasing the risk of anomalies, redundancy, potential

data inconsistencies, and even data loss. Before considering this measure, we strongly recommend becoming

familiar with relational theory and normal forms. Denormalization should be considered as a last resort in

performance optimization. Any schema design should therefore start with a reasonably high normal form (3rd

normal form, BCNF, or even 4th normal form). If it is then impossible to achieve your performance goals, these

forms can be carefully relaxed.

Benets

Depending on the query workload and data model, join processing might be a signicant cost factor in an

application. Particularly if the data that needs to be retrieved by a query is distributed across multiple tables,

join processing might easily become predominant. By changing the underlying database schema and merging

the records of two or more tables (thereby adding all necessary attributes to a single large, combined table),

the costs of join processing can be avoided, which therefore improves performance. The actual gains that can

be achieved depend heavily on the query complexity and datasets. Even for simple joins, such as two tables

reecting the classical SAP header/line item schema (for example, STXH and STXL, or BKPF and BSEG), there

might be a notable performance boost. Measurements that were done with an example query that aggregated

1000 records after a join between BKPF and BSEG were up to a factor of 4 faster in a denormalized model.

Risks

The typical risks of denormalization revolve around accumulating redundant data, for example redundantly

keeping a customer address as part of a line item instead of storing it in a separate master data table. Special

care has to be taken, for example, to ensure that update operations touch all redundant copies of that data,

otherwise there might be inconsistencies (for example, dierent addresses for the same customer) or even

data loss (all orders of a customer are deleted and therefore the address is lost because it is not kept in a

separate table).

SAP HANA Performance Guide for Developers

Schema Design

PUBLIC 19

The obvious performance drawbacks with added redundancy are as follows:

● Increased memory consumption by keeping redundant data multiple times in a table, for example the

customer address k times in a denormalized model. This is also relevant for table maintenance operations

(LOAD from disk and DELTA MERGE) and the I/O footprint of a table (savepoints, merges, table loading,

and storage requirements).

● Additional update costs (needing to insert the customer address redundantly for each order that is added

to the system)

● Potentially, additional lookup costs (needing to query the customer address from another row of the table

to insert it redundantly with a new order)

There are also less obvious cases where the performance of a system or query can suer due to

denormalization. For example, consider a setup with two tables in a header and line item relationship. The

header table includes a ZIP code that is used to lter the data, before it is joined with the line item table and

certain values are aggregated for the corresponding line items (for example, price). No indexes are available.

The header table has 1 million entries and each header has a large number of line items (100). If both tables are

now merged through denormalization, the resulting table has the same number of entries as the old line item

table (therefore 100 million).

To now process the lter predicate on the ZIP code, the entire table must be scanned because there is no index

available. This means that 100 times more data needs to be scanned than before. Depending on the selectivity

of the ZIP code, this can easily result in a more expensive plan than when the header table (1 million records)

was simply scanned and the join with the line item table processed afterwards with a reduced set of records.

Obviously, this problem can be mitigated by creating additional indexes. However, this in turn can introduce

additional issues.

Note that while the simplied scenario above sounds trivial, similar eects have been observed with BW In-

Memory Optimised (IMO) InfoCube structures (which basically denormalize a snowake schema into a star

schema).

Column Store Specics

The dictionary compression used by the SAP HANA database column store helps to reduce the overhead of

storing redundant data. When the same value is stored multiple times (for example, the street name in a

redundant address), the corresponding literal is stored only once in the underlying dictionary. Therefore, the

added overhead is not the size of the literal, but just the size of the corresponding entry in the index vector (this

requires k bits, where k is ceil(log_2 x) for x entries in the dictionary). Therefore, the penalty for storing

redundant data is typically much lower than when denormalization is applied in a row store, where the data is

uncompressed.

When to Denormalize

It is important that you consult an expert rst. Denormalization should be applied carefully and only when there

is a clear benet for the query workload in terms of response times and throughput. Any denormalization

eorts should therefore be driven by performance analysis, which also takes into account the update workload

on the denormalized tables, as well as resource consumption (main memory overhead, additional I/O footprint,

additional CPU costs, also for background operations like delta merge, optimize compression, table loading

PUBLIC

SAP HANA Performance Guide for Developers

Schema Design

剩余275页未读，继续阅读

m0_54520691

粉丝: 0
资源: 2

SAPHANA开发者性能优化指南

SAP HANA_STUDIO 安装包下载指南

HANA数据库管理与优化：SQL语句及配置检查包

探索SAP_HANA_STUDIO_2.3.15_X64的特性与安装方法

SAP_HANA_Search_Developer_Guide_en.pdf

SAP_HANA_Developer_Guide_for_SAP_HANA_Studio_en.pdf

SAP_HANA_SQL_Reference_Guide_en.pdf

SAP_HANA_Client_Installation_Update_Guide_en.pdf

SAP_HANA_Administration_Guide_en.pdf

SAP_HANA_Developer_Guide_en

SAP_HANA_SQL_Script_Reference_en.pdf

最新资源