3111615 - SAP Data Services Performance after upgrade

• Run jobs from SAP Data Services Designer and see huge performance impacts.
• Same job triggered in both QA and DEV system but performance issue captured only in QA
• Collected HANA SQLtrace for both systems (as per note 2031647 - How to enable SQL Trace in SAP HANA Studio) and result looks like:

> DEV - eds_landing_<hostname>_30603_000.py

...
2021-10-06 18:01:31.173510  UPDATE begin
2021-10-06 18:01:31.175321  UPDATE finish  duration: 1811
2021-10-06 18:01:31.177249  INSERT begin  latency: 1928
2021-10-06 18:01:31.177915  INSERT finish  duration: 666
2021-10-06 18:01:31.179860  UPDATE begin  latency : 1945
2021-10-06 18:01:31.181723  UPDATE finish  duration: 1863
2021-10-06 18:01:31.183645  INSERT begin  latency : 1922
...

Avg latency: 1,920 (microsecond)

> QA - eds_landing_<hostname>_30440_000.py

...
2021-10-14 17:54:18.218845  UPDATE begin
2021-10-14 17:54:18.222540  UPDATE finish  duration: 3695
2021-10-14 17:54:23.595376  INSERT begin  latency: 5 second 372836  (?!)
2021-10-14 17:54:23.598180  INSERT finish  duration: 2804
2021-10-14 17:54:23.914077  UPDATE begin latency: 315897
2021-10-14 17:54:23.918985  UPDATE finish duration: 4908
2021-10-14 17:54:24.242236  INSERT begin latency: 323251
2021-10-14 17:54:24.244343  INSERT finish duration: 2107
...

Avg latency: 350,000 (microsecond)

• Huge latency difference between UPDATE and INSERT statement executions
• Checked NIPING test (with hostname with fully qualified)from app server to QA and app server to DEV but no issue found
• Since QA is scale-out HANA system, checked distribution mode (client_distribution_mode) on HANA server and client connection property DISTRIBUTION on HANA client. Both are set as 'statement' which is correct based on
  "Settings on client and server side are negotiated in the following way" from the note 
  2200772 - FAQ: SAP HANA Statement Routing and Client Distribution Mode
  2079372 - SAP HANA - Statement Routing
  
  

• Environment upgraded to

Azure

SAP BI IPS 4.2 SP7 Patch 6
SAP DS 4.2 SP 13 Patch 4
SAP HANA 2.0 SP05 (scale-out)
SLES 12 SP4

• Environment upgraded from

HEC

SAP BI IPS 4.2 SP7 Patch 6
SAP DS 4.2 SP 13 Patch 1
SAP HANA 2.0 SP03 (scale-up)
SLES 12 SP3

SAP, Data Services, HANA, scale out, performance, hostname,  , KBA , HAN-DB , SAP HANA Database , EIM-DS-EXE , Job Execution , Problem