Is a CASE Statement a Read or a Write?

[#1]

Hi,

My question is: is a HANA CASE statement considered a READ or a WRITE?

When using 1000s of case statements over 100s of columns in a view, and then trying to consume that view in a Calculation View and then Lumira, the data fails to load, and sometimes crashes the HANA server. If we view the data from the CV directly in HANA the query time is about 40 seconds. If we view the source data table (without the view with CASE statements) the data is retrieved in ~ 1 second.

Would changing from a column store to row store table improve this performance?

Thanks,
Matt

[lbreddemann]

CASE is not a SQL statement but an expression within a SQL statement.

I don't understand what your question about READ or WRITE about. Maybe you want to clarify on that a bit.

Concerning the comparison between selecting (presumely all the data of all the columns) and the data preview in HANA studio/WEB IDE: it's important to realise that HANA Studio and the WEB IDE both add a limitation on the number of selected records to the statement. This has a major impact on the runtime and memory requirements of statements.

Finally, comparing a view with hundreds of case expressions to a select against a plain table does not make a whole lot of sense at all. And switching the table storage to row store is likely not a good idea here either.

All in all: with HANA (and pretty much every other data analytics solution) the reporting performance depends to a large part on how much data should be transferred to the client. Select only the columns you actually need and only the data you require.

[Former Member]

Hi Lars and Lumira Team,

I'll be more detailed about the question and why I'm asking it.

We have a table with raw, codified data.

We then take this codified data and layer a view on top of it decoding the columns with case statements (see attached file seer-breast-view.txt)

We then consume this view in a Calculation View, using a projection and aggregation, having 1 column (PUBCSNUM: count) aggregation and all the others 197 Facets/Dimensions.

We then consume the CV in a Lumira Document via Live Hana Data Connection.

We notice that Lumira cannot actually load this document.

But if we change to a materialized view, and use the materialized view (table) in the projection node, the performance problem is solved.

without using a materialized view, I tested row table vs column table and then data preview in Hana studio: Row table performs better when viewing raw data (seer-breast-results.jpg)

However, The column table performs better for doing analysis with label and value axes.

For lumira, I though that when you use a Hana Live OLAP connection, the calculations are delegated to hana and only the resulting aggregation is sent back to Lumira for display. But in our experience the Lumira document will be 100x slower when compared to Hana Studio Data Preview Analysis tab.

It gets even worse when we try to use a Virtual Table as the source data and layer a view and CV on top of the Virtual table (VT from another HANA source on the same network). This network is so slow that Lumira won't even load the columns into the user interface.

So the reason why I wanted to ask if CASE declaration is considered an Insert or a Select is because I've read there are performance differences between row and column tables with regards to Insert and Select.

When we were sold on Hana, we were told that we could do On-The-Fly ETL and not have to keep materialized tables. So call us surprised when we loaded our data to Hana, and tried to consume it on Lumira without materialized tables, and any change (filter, add dimension) on Lumira would take minutes to complete.

--Matt

seer-breast-view.txt

[Former Member]

hana-overview.jpg -FYI

mt-breast-table-runtime.jpg -FYI Column vs Row Store table

I see the benefits of column store compression in the table runtime.