What is the best way to avoid mixing SELECT and UPDATE in order to improve performance?

[former_member211797]

We've got a project (using HANA) that deals with big data real-time and we're following the guidelines - denormalized tables, avoiding joins etc. and, of course, SELECT queries are extremely fast. Now, we need to introduce UPDATE and INSERT - they are not real time and they don't have to be quick, but they are going to affect the same tables. My question is how we can avoid UPDATE and INSERT queries affecting the performance of SELECT queries? I'm thinking of using buffer tables - insert/update in those buffer tables and, say, once every 24 hours, push the ready data, all at once, into the "selectable" tables for a few minutes. Is this going to work? Is there a better strategy?

[lbreddemann]

Generally speaking, the column store table management allows for both high-performance reading and writing on a table without changes.

As changed data is written into the delta store of a table, the scan performance degrades linearly with the amount of changed data before a delta merge happens.

Once a delta merge finished, the table is highly read optimized just as before the data change.

Depending on how much you can influence or predict the occurrence and type of data changes, you may consider options like disabling the AUTO MERGE function (and using manual/smart merges) or partitioning the table.

Beyond that, your question doesn't contain enough information to make any further recommendations.

Try, test, measure and refine are the weapons of choice as per usual 🙂

[former_member211797]

Thanks Lars,

Can you, please, give some links/blogs/docs about the approaches you mentioned above? More precisely disabling AUTO MERGE and table partitioning.

Regards,

S.

[lbreddemann]

By now there is a lot of documentation available, so doing that googling for you really is not on my to-do list.

Beyond reading the standard documentation, the (SAP internal) HANA blue book, my book and of course all the HANA FAQ notes you might get something out of reading these:

https://blogs.saphana.com/2013/04/07/best-practices-for-sap-hana-data-loads/ - OLD!!

https://blogs.sap.com/2016/12/20/merging-right.-some-tests-with-data-loading-in-hana-from-down-under...

https://www.sap.com/documents/2012/05/9ec46a16-547c-0010-82c7-eda71af511fa.html - probably outdated

[former_member211797]

You are absolutely right - there's a lot of documentation available and this is why, it's hard to find exactly the pieces I need. Hence - the question.

Thanks, I will see what I can find ...