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.