Skip to Content

SAP HANA Performance Issue?

Hello,

I am running a cube in SAP HANA and i did it in two ways, first is with HANA GUI tools and the second one is made with a table function, only SQL Script.

When I try to browse the raw data from them I get the following results:

1) HANA with GUI = 53 seconds to show results

2) Table Function with SQP Script = 1 second to show results

I dont know why I have this difference between the cubes, once I put a view into aggregation node or anything from HANA GUI it's just increase the time of processing.

My cube has 150millions rows unaggregated and 128millions aggregated.

I am using SAP HANA SPS04 {EDGE (256GB RAM) }

As far as i know 150million rows is not much at all for HANA so it should run like in 1 second when I interrogate the cube.

If you have any ideas why I have this issue or places to check, please tell me.

Many thanks

union.jpg (62.5 kB)
uni2.png (31.3 kB)
tfff.png (31.9 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Aug 06, 2020 at 08:04 AM

    Ok, we don't know how your table function looks like, how the SELECTs for the data previews look like in each case and we don't know the execution plans for the queries.

    With that little information, we cannot tell why one statement is faster than the other or whether they are actually comparable at all.

    However, the "raw data" preview is not at all a good indicator for runtime. Not even a tiny bit.

    For the "raw data" to produce results, HANA needs to materialize all columns for all records. Effectively this means "uncompressing" the data that HANA otherwise would just keep in its internal form (highly compressed).

    So, while 150 Mio records is not a big data set for HANA to manage and perform computations on, it is a huge data set to bring back to any client as a result set.
    This is why you should always filter and aggregate and only fetch the columns you actually need.

    It's worth mentioning that this is very different from other data platforms, where there is little difference in how the data is stored and processed internally and how it is returned to the client. For those platforms (e.g. row-based systems) 1000 MB of records stored are 1000 MB processed and (if not filtered/aggregated) 1000 MB returned.

    For HANA this is more like 100MB of records stored, 200MB processed,1000MB returned.

    Add a comment
    10|10000 characters needed characters exceeded

    • Also when I try to run a WizPlan for my Cube i have the following error:

      AP DBTech JDBC: [2048]: column store error: search table error: [9] Memory allocation failed;exception 1000002: Allocation failed ; $size$=9704063168; $name$=Results; $type$=pool; $inuse_count$=13; $allocated_size$=49240539564; $alignment$=8; $failure_type$=STATEMENT_MEMORY_LIMIT; $failure_flag$= ,Exception in executor plan187827@******:30003 while executing pop 11 [01:15:08 PM] A server-side runtime exception occurred during execution. The execution plan is displayed, but some operators may not contain accurate measurements.

      Sometimes it takes like 1-2 hours to execute a WizPlan, sometime less.

      Might be this a SAP HANA configuration Issue or a Cube Optimization Issue?

      What should I check? Any advice?

      https://easyupload.io/m/tcmhsm

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.