Skip to Content
0
May 16, 2020 at 05:35 PM

HANA Express Bad performance calling stored procedure in XSA by nodejs and @sap/hdbext

273 Views

Hello experts,

if have a stored procedure which has a table parameter as input and a table parameter as output.

When i call the procedure in the WebIDE database explorer sql console I have a good performance.

DO BEGIN 

DECLARE ltMessages TABLE ( CONNECTOR_ID NVARCHAR(100), DEVICE_ID NVARCHAR(30), MSG_TYPE NVARCHAR(30),  MESSAGESTRING NCLOB, USER NVARCHAR(50) );
DECLARE ltResult   TABLE (  DEVICE_ID NVARCHAR(30), RESPONSESTRING   NCLOB  );

:ltMessages.INSERT(('TEST', '', 'DEVICECONNECTOR_STATE', 'TESTEMSSAGE','TESTUSER' ),1);

 
CALL "processX2CDeviceConnectorMessages"(PIT_MESSAGES => :ltMessages ,POT_RESULTS => ltResult);


END

When I call the procedure the first time the processing time is long, I think because of compilation. Subsequent call took 10 milli seconds. Every thing is fine.

Statement 'DO BEGIN DECLARE ltMessages TABLE ( CONNECTOR_ID NVARCHAR(100), DEVICE_ID NVARCHAR(30), ...'executed in 10 ms.

When I call the procedure in a NodeJs app in XSA every call has a proccessing time over 3000 ms (measured in the node application.

var xsenv = require("@sap/xsenv");
var hdbext = require("@sap/hdbext");  // package.json: "@sap/hdbext": "6.2.2" 

var hanaConfig = xsenv.cfServiceCredentials({
	tag: 'hana'
});

hanaConfig.statementCacheSize = 200;
var globalstoredProcedure = null;
 
hdbext.createConnection(hanaConfig, function (err, client) 
{
    hdbext.loadProcedure(client,null,'processX2CDeviceConnectorMessages',function(err,storedProcedur) 
    {		
         globalstoredProcedure = storedProcedure;	
    });
});

function intervalFunc() {
  var aInputParameter = []; 
  aInputParameter.push({ CONNECTOR_ID: "TEST",DEVICE_ID:"",MSG_TYPE:"DEVICECONNECTOR_STATE",
		MESSAGESTRING: "TESTEMSSAGE",USER: "TESTUSER"
	});                
 
  var before = Date.now();
  globalstoredProcedure(aInputParameter, function (err, parameters, dummyRows, tableRows) 
  {
     var after = Date.now();								
      console.log("Procedure Result", dummyRows,"Duration: " + (after - before ));

  });                

}
setInterval(intervalFunc, 10000);





Output:

5/16/20 5:02:45.759 PM [APP/11-0] OUT Procedure Result [ { DEVICE_ID: '', RESPONSESTRING: '' } ] Duration: 5988
5/16/20 5:02:53.513 PM [APP/11-0] OUT Procedure Result [ { DEVICE_ID: '', RESPONSESTRING: '' } ] Duration: 3746
5/16/20 5:03:03.794 PM [APP/11-0] OUT Procedure Result [ { DEVICE_ID: '', RESPONSESTRING: '' } ] Duration: 4024
5/16/20 5:03:13.213 PM [APP/11-0] OUT Procedure Result [ { DEVICE_ID: '', RESPONSESTRING: '' } ] Duration: 3439
5/16/20 5:03:23.312 PM [APP/11-0] OUT Procedure Result [ { DEVICE_ID: '', RESPONSESTRING: '' } ] Duration: 3532
5/16/20 5:03:33.682 PM [APP/11-0] OUT Procedure Result [ { DEVICE_ID: '', RESPONSESTRING: '' } ] Duration: 3894

I assume that HANA compiles every time before the procedure is executed. Setting hanaConfig.statementCacheSize = 200 has no effect.

With HANA 2.0 SPS02 the performance was messured under 100 ms. Now after Upgrade to HANA 2.0 SPS04 RC45 the problem came up. Going back to versions of @sap/hdbext or node.js used with SPS02 do not solve the problem.

Has somebody a hint to solve the problem?

Best regards

Achim