Scribd
Upload
26 views

Development and Design of Query

1. When designing queries, only include necessary information objects and use filters to reduce the dataset and improve performance. 2. Tuning query performance involves using the proper decimal precisions, activating cache modes, disabling parallel processing when not needed, and using logical or physical partitioning. 3. Consult SAP notes for troubleshooting memory issues commonly seen with queries.

Uploaded by

m
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
26 views

Development and Design of Query

1. When designing queries, only include necessary information objects and use filters to reduce the dataset and improve performance. 2. Tuning query performance involves using the proper decimal precisions, activating cache modes, disabling parallel processing when not needed, and using logical or physical partitioning. 3. Consult SAP notes for troubleshooting memory issues commonly seen with queries.

Uploaded by

m
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Development and design of query

1. only include info objects in query which are necessary.

2.Use Composite provider to build a query.

3.Use filter whenever is possible to reduce the dataset into query. (Add input-parameters in
HANA views)

In filter use Not in i.e., Exclude some data point from a data set instead of IN. It improves
performance.

 4. Minimum use of Free Characteristics:  It reduces the memory load and improve the
performance.

use structures selections inside that and enable the setting “Selection of Structure Elements”
the performance improves Significantly.

5. Push the calculation to the transformation layer for complex calculations

6. Avoid Aggregate unnecessary fields, be specific to the key figures required

7.If required Use logical partitioning – semantic partitioning,

8. If required use physical partitioning

9. Use OLAP cache

10 Always “Suppress Result Row” to get optimize performance.

 11. Choosing the proper decimal precisions for key figure calculation: 

 Use higher precisions while creating info object using in Info Object maintenance
transaction (RSD1) for key figure, and in the query monitor for all key figures of a
query.
 By default, “short decfloat” with Dec floating point with 16 places selected.

Use “Long decfloat” with floating number with 32 places. This will reduce memory load.

 
Tuning and changing the setting and properties:

1. Use Program RSR_CACHE_RSRV_CHECK_ENTRIES to remove the unused Cache entries.

2. Using the Selection of Structure Elements activated.


a. Recommended setting in general scenario
b. By default, all queries use Mode “3.”
c. Mode “6” is specifically to deal with exception aggregation in SAP HANA

3. Deactivate the parallel processing for an individual query which do not have sub query
or formula variable with replacement path which execute another subquery. We will
gate fast response time.
a. Composite Provider is fully processed in SAP HANA

b. Different Cache Modes: Local and Database • Cache Mode I: Cache is inactive 
New data is read during every single navigation step • Cache Mode 0: Local
cache  The database cache is deactivated. In this case, the system works
exclusively with the local cache. • Cache Mode D: Cache in database  The cache
data is persistent in database tables. In this mode, no data is displaced, and the
memory size is unlimited. The database instance for the table carries the load.
More space is required, but this method also saves time. • Cache Mode P: Every
provider as set in the InfoProvider definition  This mode is only available for
queries on MultiProviders and CompositeProviders. It makes it possible to cache
the data from the contained InfoProviders in accordance with the cache made
set in the definition of each individual InfoProvider. 27 Cache Usage Mode • The
cache usage mode is an expert extension to the cache mode. It defines in which
situation cache entries can be used for answering a query, and in which cases
data needs to be read anew. • “S” Always display data from query start (Stable
Data) • “C” Always display Current Data • “” Data from query start if possible,
otherwise current data (default behavior)

c.

d. By using the data integrity profile, the system automatically uses correct settings
for other individual technical query settings. This prevents contradictory settings
of the expert settings, which might not produce the required query behavior

e. No Parallel Processing • Parallel processing can be deactivated for an individual


query. This can be beneficial because the query then uses fewer system
resources if you use non-parallel processing. • In the case of queries with very
fast response times, the effort required for parallel processing can be greater
than the achievable time gain

4. ADSO partitioning (physical and logical partitions)


5. Add input-parameters in HANA views.

6.  High Cardinality flag will lead to enable the Info Object.


7.
Useful SAP Notes

 1879725   BW Query related Memory Dumps like


TSV_TNEW_OCCURS_NO_ROLL_MEMORY
 1681396   Query Performance
 1967896   Basic recipe for troubleshooting memory problems with Bex queries
 1968598   Mass data in Bex queries: Options and Workarounds

You might also like