Question Re: Late materialization insert processing in Technology Q&A

Late materialization insert processing

former_member234712 — Mon, 03 Dec 2018 13:56:30 GMT

Hi gurus, I´ve a performance problem inserting a large result-set (180M), coming from a select clause, into Hana DB. Although the sql clause runs ok, when i add the insert clause, i get an OOM. I have studing the case carefully, and looking for a late materialization issue. The plan of the select with and without insert are the next:

In the insert, we dont have a Late Materialization processing. Since it´s an error new for me and is difficult to find appropiate documentation, i can´t figure out if thats the normal behaviour, or not. Should has the insert COLUMN SEARCH operator the LATE MATERIALIZATION processing?

lars.breddemann any inside advicing would be really appreciated! Sorry for the inconvenience, if you came to Mallorca you have some beers for sure 🙂

Kind regards!

Re: Late materialization insert processing

former_member234712 — Mon, 03 Dec 2018 15:32:35 GMT

Forget to say that the late materialization threshold for both select and insert are much less than the expected number of records, so shouldn´t be that the problem.

Re: Late materialization insert processing

lbreddemann — Tue, 04 Dec 2018 02:28:16 GMT

I'd take the beer and the island trip - long time that I've been to Mallorca.

Anyhow, the late materialisation is not the critical factor here. Your system simply does not seem to have enough memory to hold the result set and the target table storage structures (MAIN, DELTA, DELTA2, MAIN2) for the time the INSERT statement is running.

The whole result set has to be materialized and then written into the target table delta. In between, delta merges will happen, that copy large amounts of data between DELTA and MAIN as well as MAIN, DELTA and MAIN2 (assuming there will be multiple delta merges).

I wouldn't give too much on the cardinality estimation in the explain plan - out of memory errors occur when memory for actual data is requested and in this case this data seems to be too much.

How large is the system memory, by the way? And have you checked with PlanViz how much memory/how many records actually end up in the target table?

One option for a workaround could be to partition the target table so that the delta merges can occur in smaller chunks.

Re: Late materialization insert processing

former_member234712 — Wed, 05 Dec 2018 14:25:29 GMT

For sure you have your beers! 🙂 Thanks for your help Lars, i´ve been working tunning the SQL in 2 main points:

- Partitioning destination table like you recomend--> get us major improvements in inserting time and memory consumption

- Rewriting logic from calculation view to plain sql--> major improvements in memory consumption.

At the end, the memory consumption becomes stable and in reazonable limits.

Thanks again Lars!!!!!!