my xsjs service taking more time to generate result if db result contains morthan 2 lakhs records

[former_member665201]

this is my xsjs if my db result morethan 2 lakhs then its taking 3- 4 mins but db result executed with 88 ms  . kindly help me with this . due to this my ajax calls also returning no response from service 
thanks in advance


var connection = $.hdb.getConnection();
// var businessDate = $.request.parameters.get("businessDate");
// var  FSDPTables=$.request.parameters.get("FSDPTables");
// var SourceSystemID=$.request.parameters.get("SourceSystemID");
// var CountryCode=$.request.parameters.get("CountryCode");
// var IDSystem=$.request.parameters.get("IDSystem");
// FSDPTables=decodeURIComponent(FSDPTables);


if(CountryCode==="KR" && SourceSystemID==="MX2KR")
{
	IDSystem="MX2-KR";
}
function formatCOBDate(rawDate) {
	// var str = rawDate;
 // var res = str.substring(0, 10);
	var date = new Date(rawDate);
	var day = date.getDate();
	if (day < 10) {
		day = "0" + day;
	}
	var month = (date.getMonth() + 1);
	if (month < 10) {
		month = "0" + month;
	}
	var COBDate = day + "/" + month + "/" + date.getFullYear();
	//var COBDate = date.getFullYear() +"-"+ month +"-"+ day;
	return COBDate;
}


 //businessDate = businessDate.split("/").reverse().join("-");
 //businessDate=formatCOBDate_yymmdd(businessDate);
var businessDate="2019-12-31",
FSDPTables="sap.fsdm::ProductClassificationOfFinancialContract",
SourceSystemID="MX2",
CountryCode="GLOBAL",
IDSystem="MX2-XX";


try {
	var resStatus = {};
	resStatus.statusCode = 0;
	resStatus.status = false;
	resStatus.msg = "";
	resStatus.data = "";
	var sqlQuery = "SELECT  \"COBDate\"," +
		"\"SourceSystemID\"," +
		"\"CountryCode\"," +
		"\"ErrorCode\"," +
		"\"FSDPTableName\"," +
		"\"SourceKey\"," +
		"\"SouceColumnName\"," +
		"\"FSDPColumnName\"," +
		"\"ErrorMessage\"" +
		"FROM \"::ErrorRecord*****\"" +
		"(placeholder.\"$$IP_Date$$\"=>'" +businessDate + "',"
		+ "placeholder.\"$$IP_Tables$$\"=>'" + FSDPTables + "',"
		+"placeholder.\"$$IP_SourceSystemID$$\"=>'" + SourceSystemID + "',"
		+"placeholder.\"$$IP_CountryCode$$\"=>'" + CountryCode + "',"
		+"placeholder.\"$$IP_IDSystem$$\"=>'" + IDSystem + "')";
	var result = connection.executeQuery(sqlQuery);
	
	if (result.length > 0) {
	resStatus.statusCode = 1;
	resStatus.msg="Data Retrived Successfully";
	resStatus.status = true;
		for (var i in result) {
		result[i].COBDate = formatCOBDate(result[i].COBDate);
	}
		resStatus.data = result;
	} else {
		resStatus.data = "NO DATA FOUND";
		resStatus.msg="";
	}


} catch (err) {
	resStatus.statusCode = -2;
	resStatus.status = false;
	resStatus.msg = err;
} finally {
	if (connection !== null) {
		connection.close();
	}
}


$.response.contentType = "application/json";
$.response.setBody(resStatus);
$.response.status=$.net.http.OK;

[jhodel18]

You can improve the performance if you let the HANA DB do the formatting of date by creating a calculation view that does the date formatting. Then in your XSJS logic, you just have to query from the calculation view you have created. This technique is called code push-down. I'm referring a blog here that uses ABAP as runtime, but the principle is the same for XSJS runtime. Here's the blog: https://blogs.sap.com/2014/02/03/abap-for-hana-code-push-down/