$.hdb vs $.db Interface - Performance/Problems

[Former Member]

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. :grin:

Best,

Mathias