Skip to Content

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

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • May 01, 2018 at 04:16 AM

    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 :)

    Add comment
    10|10000 characters needed characters exceeded