ESP to HANA connectivity via UnixODBC

[Former Member]

Hi there, Sending this out to the SDS/ESP community to see if anyone has any thoughts on this... We currently use SAP ESP 5.1-SP08 very closely with HANA SP08 (Rev.95) via an ODBC connection that is managed by UnixODBC 2.3.2, all running on Solaris SPARC x64. A number of our ESP projects call HANA stored procedures (via the getData() function) to retrieve some additional contextual information about the event just received. We have recently increased the volume of events that are flowing through our ESP projects, and I’m attempting to see the performance impact this has on the projects – in particular the calling of the HANA stored procedures. (Note that we are unable to use the HANA “Reference” element in ESP to take advantage of any caching/etc, as the HANA logic only becomes efficient when using stored procedures & we can filter data in the database by using the procedure input parameters). I also have some initial questions that hopefully one of the SAP ESP folks can help with (but happy to follow this via official SAP support channels if this is more appropriate). Basically, I’m wondering, in our scenario (where we call the getData() function to call a HANA procedure whenever an event is received in ESP): • is the ODBC connection from ESP to HANA via a single connection (i.e single-threaded)? • Is the ODBC connection from ESP to HANA open and closed for every single event? • Is there a concept of ODBC connection pooling between ESP and HANA? Thanks in advance for any advice! Cheers, Jason (DHS Australia).

[Former Member]

Great questions Jason!

1. Every stream that makes a get data call gets its own database connection. If you call getdata from 3 different streams, 3 different connections would be created.

2. Connections are cached and only reopened if a connection to the database is lost.

3. As mentioned in 1, a separate connection is maintained for each stream that would be making getdata calls. If you are also using our HANA adapter, it would maintain its own separate connections. Threads do not share connections (even if they are temporarily idle)

I hope this helps.

Joe

[Former Member]

Thanks for the quick and detailed reply Joe! You have provided me with information that I have been wondering about for a few months now (and hadn't got around to posting on here)!

One additional question then, and apologies if this is stating the obvious... but I assume that operations through the one connection (ie. for all events on the one Stream that makes a getData() call) are handled sequentially?

This leads me to thinking about the HANA Output Adapter - how do the multiple parallel ODBC connections work? Is there some kind of internal logic to split up the events into separate internal streams for parallel output to HANA?

I'm curious because what I'm essentially trying to achieve here is to maximise the throughout of events through the getData() function call, by looking at parallelising these calls.

I.e. by potentially creating multiple HANA database service definitions (that actually all point to the same database) & using a splitter to distribute the incoming events to different streams & getData() function calls (which each use a different HANA database service definition).

Some initial testing has shown this gives a minor performance boost to a sustained load of incoming events, but we will need to look into it in more detail, so any background/additional info would be most useful too!

Thanks again for your help, and sorry this turned into such a long reply!

[Former Member]

Not a problem at all. I always enjoy talking tech with our customers.

You're correct about there being internal logic inside the adapter that microbatches events and stripes the batches accross multiple connections.

Since each stream that uses getdata gets it own database connection you shouldn't need to define new service entries to see a speed up in performance. A splitter and multiple streams making getdata calls to the same data service should give you a speedup if that's the bottle neck in your project. Of course if the stored procedures themselves serialize access then you won't see much of a performance increase.