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>”));
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! 🙂