Skip to Content

Hi folks,

I want to share my experience concerning the two xsjs-engine database connection implementations:

  • $.hdb (since SPS 9)
  • $.db

The Story:

Some days ago I used the new HDB interface implementation for the xsjs engine to process and convert a result set in a xsjs service. Problematic for this service is the size of the result set. I am not very happy with the purpose of the service but we somehow need this kind of service.

The result set contains about 200.000 rows.

After setting up everything and having multiple test with small result sets < 10.000 rows everything works fine with the new $.hdb implementation. But requesting the first real sized set caused heavy trouble on the maschine (all xsjs connections) and the request never terminated.

As a result I found myself implementing a very basic xsjs service to get all files in the HANA Repository. (Because per default there are more then 40.000 elements in it.) I duplicated the service to get one $.db and one $.hdb implemenation with almost the same logic.

The Test:

HDB – Implementation


// >= SPS 9 - HDB connection
var conn = $.hdb.getConnection();
// values to select
var keys = [
    "PACKAGE_ID",
  "OBJECT_NAME",
  "OBJECT_SUFFIX",
  "VERSION_ID",
  "ACTIVATED_AT",
  "ACTIVATED_BY",
  "EDIT",
  "FORMAT_VERSION",
  "DELIVERY_UNIT",
  "DU_VERSION",
  "DU_VENDOR"
];
// query
var stmt = conn.executeQuery( ' SELECT ' + keys.join(", ") + ' FROM "_SYS_REPO"."ACTIVE_OBJECT"' );
var result = stmt.getIterator();
// result
var aList = [];
while(result.next()){
    var row = result.value();
    aList.push({
        "package" : row.PACKAGE_ID,
        "name" : row.OBJECT_NAME,
        "suffix" : row.OBJECT_SUFFIX,
        "version" : row.VERSION_ID,
        "activated" : row.ACTIVATED_AT,
        "activatedBy" : row.ACTIVATED_BY,
        "edit" : row.EDIT,
        "fversion" : row.FORMAT_VERSION,
        "du" : row.DELIVERY_UNIT,
        "duVersion" : row.DU_VERSION,
        "duVendor" : row.DU_VENDOR
    });
}
conn.close();   
   
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.headers.set("Content-Disposition", "attachment; filename=HDBbench.json" );
$.response.setBody(JSON.stringify(aList));

DB – Implementation


// < SPS 9 - DB connection
var conn = $.db.getConnection();
// values to select
var keys = [
    "PACKAGE_ID",
  "OBJECT_NAME",
  "OBJECT_SUFFIX",
  "VERSION_ID",
  "ACTIVATED_AT",
  "ACTIVATED_BY",
  "EDIT",
  "FORMAT_VERSION",
  "DELIVERY_UNIT",
  "DU_VERSION",
  "DU_VENDOR"
];
// query
var stmt = conn.prepareStatement( ' SELECT ' + keys.join(", ") + ' FROM "_SYS_REPO"."ACTIVE_OBJECT"' );
var result = stmt.executeQuery();
// vars for iteration
var aList = [];
var i = 1;
while(result.next()){
    i = 1;
    aList.push({
        "package" : result.getNString(i++),
        "name" : result.getNString(i++),
        "suffix" : result.getNString(i++),
        "version" : result.getInteger(i++),
        "activated" : result.getSeconddate(i++),
        "activatedBy" : result.getNString(i++),
        "edit" : result.getInteger(i++),
        "fversion" : result.getNString(i++),
        "du" : result.getNString(i++),
        "duVersion" : result.getNString(i++),
        "duVendor" : result.getNString(i++)
    });
}
result.close();
stmt.close();   
conn.close();   
   
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.headers.set("Content-Disposition", "attachment; filename=DBbench.json" );
$.response.setBody(JSON.stringify(aList));

The Result:

  1. Requesting DB-Implementation: File-Download for all 43.000 rows is starting within 1500 ms.
  2. Requesting HDB-Implementation: Requesting all rows leads to an error. So I trimmed the result set by adding a TOP to the select statement.
    • TOP  1.000 : done in 168ms
    • TOP  2.000 : done in 144ms
    • TOP  5.000 : done in 297ms
    • TOP 10.000 : done in 664ms
    • TOP 15.000 : done in 1350ms
    • TOP 20.000 : done in 1770ms
    • TOP 30.000 : done in 3000ms
    • TOP 40.000 : The request is pending for minutes (~5 min) then responding with 503. The session of the logged in user expires.

As summary: The new hdb implementation performs worse then the old one and there is a treshold in hdb that leads to significant problems on the system.

I appreciate every comment on that topic. 😀

Best,

Mathias

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Georgi Farashev

    Hi Mathias,

    This is a known issue. We have improved $.hdb in SPS10 and after your blog we have made some improvements for SPS9. It will be available with revision 97.02.

    Thank you for your post.

    Best regards,

    Georgi

    (0) 

Leave a Reply