Performance Tuning in SAP HANA

Ratings:
(4.3)
Views:1997
Banner-Img
  • Share this blog:

Performance Tuning

To monitor the performance of views, we can use two tools

1) Explain the plan

2) To administration control.

Explain plan

It is a table to maintain details of views that are used to evaluate the execution plan that the SAP HANA DB follows to excursive the SQL statements. The result of the evaluation is stored in the EXPLAIN – PLAN -  table view for later user examination

The SQL statement must be a data manipulation statement. Thus a schema definition language stunt can not be used with the EXPLAIN PLAN command

We can obtain a SQL plan from EXPLAIN – PLAN – TABLE view. All users shy the view. Here is an example of reading an SQL plan from the view.

Screenshot_85

Inclined to build a profession as SAP HANA Developer? Then here is the blog post on, explore "SAP HANA Training"

Example of SQL plan Explanation

DELETE FROM Explain – plan – table WHERE statement name = ‘TPc – H Q10 Explain Plan set statement – NAME = ‘TPC – H Q10’ for

SELECT TOP 20

C- Cust keys

C- name

Sum (1 – enfolded price * (1-1___ discount) AS revenues

C- acct bal,

N – Name,

C – address,

C – phone,

C – connect

FROM

Customer,

Orders,

Line item,

Nation

WHERE

C_Cust key = O_custkey

AND 0_ORDINATE <> = ‘1993 – 10 – 101’

AND 0_ordinate < - ADD _Months (‘1993-10-01’3)

GROUP by

C _ cust key:

C_name,

C_ acetbol,

C_Phone

N_name

C_address,

C-connect

ORDER By

We venue DESC;

SELECT operator _name, operator – details, table – name FROM EXPLAON – plan – table

WHERE statement – name = ‘TPc – H QSO’;

This table contains

Statement _ name

Operator _name

Operator _ details

Schema – Name

Table _name

Table – Type

Table – Size

Out put –Size

Subtee – cost

operator _ ID

Parent – operator – ID

Level

position

Host

Port

Timestamp

connection _ ID

Execution – Engine

Admin Console

To run this console. Go to the HANA install directory and execute SAP admin. SH

It is used to trace fro loading/analysis performance & provides landscapes, tables, views, …….. etc

Procedure click on plan execution (tab) – click on start (to tray)

Loading / analytic performer

And for every trace system will create.TPT in HANA DB working directory

It gives overviews of all plans.

Appendix DB

Checkout: SAP HANA Tutorial

SQL statements

SQL syntax: CREATE SCHEMA <Schema – name > [owned by <User-name>]

Description: The Create schema statement creates a schema in the current database

Example: CREATE SCHENA my – schema owned by the system;

SET SCHEMA

SQL schema syntax: SET SCHEMA <SCHEMA- NAME>

Parameters: <schema_name> :: = string literal which specie files schema name 

Description: We can change the current schema of the session. The current schema is used when database objects names such as table names are not prefixed with the schema name.

CREATE Table: CREATE[<table-tyupe>] TABLE <table-name> <table-contents – socrce.>[ <table_create_option_list>] [WITH PARAMETERS ( < parameter – key – value – list)]

Example: Create column table abc. (schema name) company (table) (int primary key, chp – Name varchar 2 (20))

This is for creating column table

EX:- To create row table abc. Company ((MP number int primary key, CMP – Name varchar (20))

Import:- To import from specified directory ;

SQL Syntax: IMPORT <TABLE – NAME – LIST> as < import _format> format <path> [with <options – name-list]   [threads <number – of – threads]

Parameters

<Table_name_list> :: = ,table – name>, …. | ALL

<import – format> :: = BINARY | csv

<option – name – list> :: = <option – name>,….

<option – name> :: = REPLACE | CATALOG ONLY

<path> :: = ‘full –path

<number-of – threads> :: > integrator to  indicate the no. of threads

EXPORT

SQL Syntax

Export <table – name-list> AS <Export – format> INTO <path>

[with <option- name- list>] Threads <number-of-thereads>]

Parameters

<table – name-list>:: = <table – name>,……. | All

<Export – format> :: = BINARY | CSV

<OPTION – NAME- LIST> :: = OPTION – NAME>,…….

<OPTION – NAME> :: = REPLACE | CATALOG ONLY | SCRABBLE [BY <PASSWORD>]

<Path>:: = A string literal to specify the path

<number-of-threads>:: = Integer to specify the number the threads

Database Tuning

SQL query tuning tips for the column engine

Optimizing expressions

The SAP HANA column store engine has been optimized for the most frequent pattern of OLAP greenies in the form of single block SPJG (select, project, join & group by). This selection lists high – cost features that are best avoided when formulating SQL – queries to bobbin the best performance from the column engine

Slower query                                    Select * From T

WHERE TO _DATA (Date_string, “yyyymmdd’) = CURRENI_DATE)

Faster Query                                     Select * from T

Where to – DATE (date-string) = current date;

Implicit type casting:

SAP HANA could perform type casts implicitly even if the user did not explicitly specify a type cast operation.

For Eg: if there is a comparison between a VARCHAR value and a DATE value, the database system performs an implicit typecast operation to convert the

VARCHAR Value into a DATE value. Implicit type casting is done from lower–precedence types to higher procedure types.

We can find type precedence rules in the SAP HANA SQL reference.

Slower Query                                 SELECT * FROM

WHERE date – string <Current – Date:

Faster Query                                   Select* from

Where date – string <to_char(current – date, ‘yyyymmdd’);

Non-Equijoin predicative

In other words, the column (not natively support join) engine supports only engines natively

EX:

Slower query:                                 

Select H year, M. month, SUM (T.ship-amount) From T JOIN M No T.Ship – Date BETWEEN H. first – date and M last – date Group By M.Year, M months

Faster Query:                                  

Select M.Yerar, M.Month, SUM (T. ship – amount) From T Join M ON Extract (Year from t-ship _date)= M.year and Extract (a month from T.Ship – date)= M.month group by M.year, m. month

Filter predicate inside outer Join predicate

This is used for inside outer join predicates and they cannot be sifted below the Join operation

Example

Slower Query                                       Select * from

Left joins on T.a = s.a and T.b=1;

Faster Query                                        Select - * from Left joins on T.a = S.a and t.b = s.one;

DDL for faster query                           Alter tables

Add (one integer Generated always As 1);

Appendix Business (bo) object Enterprise

It is a reporting tool to generate different flowers

After modelling views & tables we need to provide them as sconces to BO,

So, we need to integrate SAP BO, & SAP HANA with JDBC & DDBC connection

In BO flow

SAP (HANA) DB (Views & tables)

In BD we need to connect semantic layers first and designed a structure based on reports (a structure is nothing but the structure of the table we need to join them

After joining the semantic layers, we need to create reports

NOTE:- For HANA we can create only crystal reports web, explorer, and dashboards only

Data Flow

Screenshot_86

   ↓

Scheduling

   ↓

Deployment

   ↓

Publishing

   ↓

customization

   ↓

Migration

For in-depth knowledge on SAP HANA, click on below

You liked the article?

Like : 0

Vote for difficulty

Current difficulty (Avg): Medium

Recommended Courses

1/15

About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.