SQL Execution slower in XS Engine (compared to hana studio)

[Former Member]

Hi,

I built a small XS Engine tool on Hana (48).

Now I have noticed that the SQL execution time in XS Engine is about 20%-40% slower when executed from within the server side javascript code as opposed to running directly in the HANA Studio.  This is considerable loss in performance.

Network latency and processing time i have ruled out as I have test down to a single line executing a sql statement, with a single row returned.

For example, I created a statement that takes 5 seconds in hana studio and in XS Engine the same statement takes 7 seconds to run.

Maybe this is a sql plan cache issue, perhaps it is not reused.

Anyone else experienced this?  Any ideas?

Just wondering about it, thats all.

[former_member198196]

Hi all,

We're facing the same issue: calling a HANA SQL procedure from XS takes 5 min, but from HANA studio <1min... we're on Amazon AWS rev. 70 and 73. It is an urgent issue... Any help will be great appreciated!

WPL

[thomas_jung]

Are you calling a stored procedure?  There is a known performance issue when calling a stored procedure from XS due the interface between the xsengine process and index server process.  We have a fix for this in later revisions and a work around called embedded mode (where the xsengine runs inside the index server instead of as a separate process) which can be supplied by SAP support for customers who encounter this issue on revisions older than when the fix is delivered.

[Former Member]

Hi Thomas,

Could you please confirm if the issue was solved with the latest revision? We are running on rev. 60 and a procedure executed in HANA Studio takes 8 seconds and from HANA XS takes 100 seconds.

Thanks.

[thomas_jung]

I do believe that a fix for most situations was delivered in Rev64.  If you still encounter the problem after that point, please enter a support ticket.  There may be very specific scenarios which aren't covered by the current solution.

[Former Member]

I've tested this extensively and XS performs great especially > Rev.64. There is an network overhead in the JSON or Atom-XML format for large volumes of data which can become a bounding factor.

John

[Former Member]

Thanks Thomas for your quick answer. I've already asked the update of the server. That's the reason for sure because the procedure returns 10 numbers and I've monitored both executions in HANA.

[Former Member]

Makes sense. Remember that XS is just another index server process so it makes use of shared query plans like in a scale-out appliance.

If there is a bug in the shared query plan code than XS (and Smart Data Access for that matter) will suffer.

John

[Former Member]

Hi Thomas

We tried setting configuration flag "embedded" to true in Rev 82 as per SAP Note 1849775. When this flag is set, querying SESSION_CONTEXT fails when executed from XSJS code:

     select SESSION_CONTEXT('CLIENT') as \"CLIENT\" from dummy

returns an empty string (it is not null, verified that using COALESCE). This used to work fine in Rev 73. It also runs fine in Studio.

What am I missing? We would love to have the efficiency and reduced memory requirements since we have a Native app so it is a major deal. But looks like there is much more to using this approach effectively.

Regards

Dinesh

[thomas_jung]

This sounds like a bug to me. There should be any such differences in functionality when using embedded mode. You should enter a support ticket.