Did you know that Direct UPDATE / SELECT statements in scale-out HANA DB runs much faster when STATEMENT_ROUTING is used

CONCEPT OF STATEMENT ROUTING:

Statement routing is the method of evaluating the correct server node of a distributed system before statement execution. This can be controlled with the help of parameter client_distribution_mode in the indexserver.ini, connection selection and/or statement routing are executed (the default setting is statement for statement routing). But this will not be the case with the direct SQL statements that we run on a sale-out HANA DB . When we execute direct SQL -commands, it tends to execute on the same server where we have logged in, which is mostly the master node . In order to speed up our HANA queries we can as a HINT to our statement .

Update query with statement routing :

UPDATE TOP 10000000 “SAPSID”.”<TABLE_NAME>” SET LOGSYS = ‘YYYCLNT100’ WHERE LOGSYS = ‘XYXCLNT100’ WITH HINT(ROUTE_BY(“SAPPW3″.”<TABLE_NAME>”));

OR

UPDATE “SAPSID”.”<TABLE_NAME>” SET LOGSYS = ‘YYYCLNT100’ WHERE LOGSYS = ‘XYXCLNT100’ WITH HINT(ROUTE_BY(“SAPPW3″.”<TABLE_NAME>”));

In above, the master nodes hands over the execution part to the node where this <TABLE_NAME> is present which in turn reduces the load on the master node .

Other available clauses:

ROUTE_TO : Routes the query to the specified volume ID.

NO_ROUTE_TO: Avoids query routing to a specified volume ID.

ROUTE_BY: Routes the query to the specified table location.

ROUTE_BY_CARDINALITY: Routes the query to the location of the table with the highest cardinality from the input list.

Similar HINT can also be used in SELECT queries .

Follow for more such posts!
Like and leave a comment or suggestion if any!
Follow for more such posts! 🙂