Hana client performance

[#1]

Hi guys!

We are facing some issues related to the performance of Hana (SP12) client when we perform queries involving a big data result (about 1,1 GB in file system, 1,8M rows). The query is over a column store table, so doesn´t involves processing so we assume the most of the response time is due to the data transfer.

The time system needs to executing the query from command line, redirecting to filesystem, is about:

- 1 minute inside the own data base server.

- 5 minutes from BW Netweaver server. Same data center, optimal networks conditions.

- 10 minutes from the Tableau AWS server.

Are they normal times?

Thanks.

[former_member234712]

Thansk for your help Lars. Unfortunately, we have to handle this kind of very large data sets. So, why Hana is not performing the connection in the most efficient way? Take a look at this graph: the first part is the bandwidht in a transfer of data through ODBC connection, and the second one is the bandwidht of transfer of information using SCP. The bandwidht the ODBC connection is using is 9.5 Mbits/s, wich is definitely insufficient.

Again, thanks in advance Lars. I appreciate your help.

[lbreddemann]

Some more comments on this:

Comparing network usage characteristics of a file copy tool like SCP with an interactive database protocol doesn't make any sense. SAP HANA is optimized for database processing - not to replace a file system or data copy tool.

Since I cannot (and want not) dig through trace files in this forum question, I like to point you to the relevant SAP notes:

• 2222200 - FAQ: SAP HANA Network
• 2081065 - Troubleshooting SAP HANA Network
• 2382421 - Optimizing the Network Configuration on HANA- and OS-Level
• 2503880 - SAP HANA Client Performance Tuning (you'll find some improvements with HANA 2)

You can also play around with the PACKETSIZE parameter for the ODBC driver (see client interface documentation for details).

[former_member234712]

Thanks Lars, i appreciate your help. We are opening an OSS message to verify the driver ODBC is doing whats is supposed to do. In the ODBC trace is taking PACKETSIZE (set in 268435424) in the request, but not in the reply:

<REQUEST>
  SESSION ID: 1895467157909775 PACKET COUNT: 7
  VARPART LENGTH: 256 VARPART SIZE: 268435424
  NO OF SEGMENTS: 1
    SEGMENT 1 OF 1 MESSAGE TYPE: FETCHNEXT
      LENGTH: 256 OFFSET: 0
      NO OF PARTS: 5 NUMBER: 1
      KIND: CMD AUTCOMMIT: 1
      OPTIONS: ()
      PART 1 SESSION CONTEXT
        LENGTH: 56 SIZE: 268435384
        ARGUMENTS: 6
        ATTRIBUTES: ()
        DATA:
      0|01 03 EB BB 06 00 02 1D 0C 00 31 30 2E 32 32 32|..........10.222|      
     10|2E 37 32 2E 37 31 03 03 3F 75 00 00 04 03 EB BB|.72.71..?u......|      
     20|06 00 05 1D 0C 00 31 30 2E 32 32 32 2E 37 32 2E|......10.222.72.|      
     30|37 31 06 03 3F 75 00 00                        |71..?u..        |      
      PART 2 STATEMENT CONTEXT
        LENGTH: 56 SIZE: 268435312
        ARGUMENTS: 1
        ATTRIBUTES: ()
        DATA:
      0|01 21 34 00 01 00 00 00 00 00 00 00 24 27 4A B7|.!4.........$'J.|      
     10|0A 00 00 00 CE 1A AC 8F 0A 00 00 00 2F AB 02 00|............/...|      
     20|00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|................|      
     30|00 00 00 00 FF FF FF FF                        |........        |      
      PART 3 PROFILE
        LENGTH: 20 SIZE: 268435240
        ARGUMENTS: 2
        ATTRIBUTES: ()
        DATA:
      0|00 04 43 00 00 00 00 00 00 00 01 04 19 45 00 00|..C..........E..|      
     10|00 00 00 00                                    |....            |      
      PART 4 RESULTSETID
        LENGTH: 8 SIZE: 268435200
        ARGUMENTS: 1
        ATTRIBUTES: ()
        DATA:
      0|9A 6B 62 E8 EB BB 06 00                        |.kb.....        |      
      PART 5 FETCHSIZE
        LENGTH: 4 SIZE: 268435176
        ARGUMENTS: 1
        ATTRIBUTES: ()
        DATA:
      0|FF 7F 00 00                                    |...            |      
</REQUEST>


<REPLY>
  SESSION ID: 1895467157909775 PACKET COUNT: 7
  VARPART LENGTH: 1362 VARPART SIZE: 29968
  NO OF SEGMENTS: 1
    SEGMENT 1
      LENGTH: 1362 OFFSET: 0
      NO OF PARTS: 2 NUMBER: 1
      KIND: RETURN
      FUNCTION CODE: 10
      PART 1 STATEMENT CONTEXT
        LENGTH: 66 SIZE: 1322
        ARGUMENTS: 2
        ATTRIBUTES: ()
        DATA:
      0|01 21 34 00 01 00 00 00 00 00 00 00 28 27 4A B7|.!4.........('J.|      
     10|0A 00 00 00 CE 1A AC 8F 0A 00 00 00 2F AB 02 00|............/...|      
     20|00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|................|      
     30|00 00 00 00 FF FF FF FF 02 04 5A 00 00 00 00 00|..........Z.....|      
     40|00 00                                          |..              |      
      PART 2 RESULTSET
        LENGTH: 1234 SIZE: 1234


<br>

[pietro_fontana2]

Greetings,

we are facing exactly the same problem, you got a response from SAP about this issue?

Thanks

Best regards.

[Former Member]

Not yet but we are still investigating. We'll post and update if we have one.

[former_member234712]

Hi again guys!! We currently keep working on this issue. We are tracing the ODBC communications between Hana and Tableau. It seems PACKETSIZE is not defined in the REQUEST message, so Hana REPLY has an abnormaly little size. The big numbers of packages turns the connection slow due to the network overhead.

What do you think? Anyone with some related experience?

Thanks in advance!

lars.breddemann sorry for the inconvenience. I know you are one of the Hana gurus in the forum. What do you think?

We know about PACKETSIZE ODBC connection parameter, but are not able to introduce it in the connection because Tableu uses a custom ODBC connection (using the standard driver), and doesn´t let us to specify connection parameters in the datasource definition. So, are there a way to control this parameter in Hana client installation, for example? This means: are there a way to add a parameter in the Hana ODBC driver? In the Hana server maybe?

Thanks in advance.

<REQUEST>
  SESSION ID: 1782701763445595 PACKET COUNT: 2405
  VARPART LENGTH: 256 VARPART SIZE: 1048544
  NO OF SEGMENTS: 1
    SEGMENT 1 OF 1 MESSAGE TYPE: FETCHNEXT
      LENGTH: 256 OFFSET: 0
      NO OF PARTS: 5 NUMBER: 1
      KIND: CMD AUTCOMMIT: 1
      OPTIONS: ()
      PART 1 SESSION CONTEXT
        LENGTH: 56 SIZE: 1048504
        ARGUMENTS: 6
        ATTRIBUTES: ()
        DATA:
      0|01 03 5C 55 06 00 02 1D 0C 00 31 30 2E 32 32 32|..\U......10.222|      
     10|2E 37 32 2E 37 31 03 03 3F 75 00 00 04 03 5C 55|.72.71..?u....\U|      
     20|06 00 05 1D 0C 00 31 30 2E 32 32 32 2E 37 32 2E|......10.222.72.|      
     30|37 31 06 03 3F 75 00 00                        |71..?u..        |      
      PART 2 STATEMENT CONTEXT
        LENGTH: 56 SIZE: 1048432
        ARGUMENTS: 1
        ATTRIBUTES: ()
        DATA:
      0|01 21 34 00 01 00 00 00 00 00 00 00 36 87 60 B4|.!4.........6.`.|      
     10|0A 00 00 00 F4 EF 4F 8F 0A 00 00 00 6C 74 02 00|......O.....lt..|      
     20|00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|................|      
     30|00 00 00 00 FF FF FF FF                        |........        |      
      PART 3 PROFILE
        LENGTH: 20 SIZE: 1048360
        ARGUMENTS: 2
        ATTRIBUTES: ()
        DATA:
      0|00 04 5D 00 00 00 00 00 00 00 01 04 B8 89 01 00|..].............|      
     10|00 00 00 00                                    |....            |      
      PART 4 RESULTSETID
        LENGTH: 8 SIZE: 1048320
        ARGUMENTS: 1
        ATTRIBUTES: ()
        DATA:
      0|CF 9E 63 56 5C 55 06 00                        |..cV\U..        |      
      PART 5 FETCHSIZE
        LENGTH: 4 SIZE: 1048296
        ARGUMENTS: 1
        ATTRIBUTES: ()
        DATA:
      0|C9 12 00 00                                    |....            |     --> We think the value is empty. 
</REQUEST>


<REPLY>
  SESSION ID: 1782701763445595 PACKET COUNT: 2405
  VARPART LENGTH: 167353 VARPART SIZE: 167353                                 --> Hana datapackage size is too small
  NO OF SEGMENTS: 1
    SEGMENT 1
      LENGTH: 167360 OFFSET: 0
      NO OF PARTS: 2 NUMBER: 1
      KIND: RETURN
------------------------------------------

[lbreddemann]

Right now I can only offer the following thoughts:

- 1.8 Mio rows as a result set to the reporting tool... That is the main performance problem right there! That should be fixed first.

- Packetsize can be set via ODBC connection parameters if I’m not off. These can be specified either in the connection string or in the DSN

- I haven’t personally seen problems that have been solved with changing the packet size for HANA connections, yet. AFAIK not every single message PART is equal to a network round trip and - from what I understand from your description of your setup - the high number of round trips is what you suspect to be the problem.

that’s what comes to my mind reading this question.

I’d fix the result set size and also determine what the largest packet size would be that you can send/receive off the Tableau server. No use, if you force HANA to send 1M packages when your TCP stack only allows 4K packets that needs to be acknowledged and reassembled.

[former_member234712]

Hi guys,

Currently we are working on note 2503378 - SAP HANA Client Interfaces Performance Tuning. Specifically, we are trying to change the packet data size, wich is the default 1MB in our case, and the only options applies us, since we are on SP12. Any advice or experience would be great!

Kind Regards.