• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Performance Tuning in SAP HANA

Performance Tuning

To monitor performance of views we can use two tools

1) Explain plan

2)  To administration control.

Explain plan

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

The SQL statement must be data manipulation statement, thus a schema definition language stunt con not be used with EXPLAIN PLAN command

We can obtain SQL plan from EXPLAIN – PLAN – TABLE view. The view is shied by all users. Here is example of reading an SQL plan from the view.

Screenshot_85

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

1. 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

Inclined to build a profession as SAP HANA Developer? Then here is the blog post on SAP HANA Training in Bangalore.

Admin Console

To run this console. Go to 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

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 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 | SCRAHBLE [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 from 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 can perform type casts implicitly even if the user did not explicitly specify a type cast operation.

For Eg: if there is comparison between a VARCHAR value and a DATE value, the database system performs an implicit type cast 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 * FROMT

WHERE date – string <current – Date:

Faster Query                                   Select* fromt

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

Non Equijoin predicative

In other words, the coloumn (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 (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 * fromt

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

Faster Query                                   Select – * fromt
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 reporting tool to generate different flowers

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

So, that we need to integration 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 designed structure based on reports (structure is nothing but structure of 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, dashboards only

Data Flow

Screenshot_86

   ↓

Scheduling

   ↓

Deployment

   ↓

Publishing

   ↓

customization

   ↓

Migration

For an Indepth knowledge on SAP HANA, click on below

Summary
Review Date
Reviewed Item
Performance Tuning in SAP HANA
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Performance Tuning in SAP HANA"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.