Skip to Content

I just came across SAP note 1850112 – Changes to technical properties row/column store again and found it to be an interesting and reassuring collection of design decisions taken over time.

The decisions taken are about changing the storage type for SAP Netweaver tables on SAP HANA systems.

Counting how many tables were “moved” from one storage engine to another in which SAP Netweaver SP results in the table below:

Version to COLUMN STORE to ROW STORE
7.40 SP 3 88
7.40 SP 5 12
7.40 SP 8 57 1
157 1

In total 157 times the developers found that moving a table from row store to column store would be beneficial.

Only in one case, table QRFC_I_QIN_LOCK, the decision was to move a column store table to row store.

(* the way this table is used, like many other RFC and message queue tables, is quite specific, which makes it less suited for the column store)

Now, why is this remarkable?

Back in 2011 when SAP Netweaver porting to SAP HANA started, SAP internal developers had been advised to choose the storage for “their” tables based on the recommendations that can still be found in today’s SAP HANA Administration Guide.

Seeing that the decisions taken back then have been revised not only shows that SAP HANA’s column store is capable of handling production workload.

More than this it underlines the whole point of column store concepts.

It is made for business application.

It lends itself to the way, data is used in such systems and it allows to automatically take advantage of improvements in hard and software. Automatic parallel query processing and efficient mass data processing via SIMD are just two examples for that.

One conclusion of this can be:

If your application is using data in a similar way, then using column store tables is very likely the best single design decision you can take.

In fact, at least in my opinion, it is about time to change the default table type (parameter indexserver.ini • sql [] • table_type) to COLUMN as it really is the new “normal table type”.

Cheers,

Lars

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. John Appleby

    I heard that 7.3 had 3000 row store tables and they are now below 700. Mostly RS* basis tables, the ABAP repository and queues. I myself have almost never built a row store table except by mistake.

    By the way I believe that COLUMN was the default type until SPS03, when it was reverted to ROW. Why, I don’t know, but I remember my first few HANA projects defaulted to column store objects.

    Fun times.

    (0) 
  2. Andrew Thuroczy

      The question is whether this applies to

     

    1. A new NW 7.40 SP03/SP05/SP08 based installation on HANA.
    2. A migration to HANA of a system already on  NW 7.40 SP03/SP05/SP08.
    3. Upgrade of an ABAP based  system already on HANA  from NW  7.31  to NW 7.40 SP03SP05/SP08

    Also it is not clear from the note what is the minimum HANA revision required.

    (0) 
    1. Lars Breddemann Post author

      Hi Andrew,

      the note applies to new installations as well (why wouldn’t it?).

      And obviously also for migrated systems.

      Look, it’s simply a setting on DB level – the application (ECC) above doesn’t change here.

      Finally: these settings are valid for all SAP HANA revisions supported for the NW 7.40 stacks mentioned in the SAP note.

      It’s not complicated here: whenever your system is on the level indicated in the SAP note, the tables should be in column store (or in row store as we’ve seen in the one example).

      Cheers, Lars

      (0) 
      1. Andrew Thuroczy

        Lars,

        Thanks for your fast reply!.

        I will rephrase the question.

         

        I understand that it is on the HANA DB level and it is transparent to the ABAP level application.

          The question is whether it is happening automatically during the upgrade when an already HANA based  BW  on  NW 7.31 is upgraded to BW on NW 7.40 SP05  or it requires manual change of the table type to columnar for each table after the upgrade.

        Andras

        (0) 
        1. John Appleby

          Are you asking whether if the tables that were row-store tables and are now later defined to be column-store tables, does SAP do the table conversion automatically?

          If so then I am also curious how this works. Does a BW SPS send an ALTER TABLE command?

          My understanding is these tables are defined at migration/install time and this defines what store they are in. For a row store to become a column store table, an ALTER TABLE command must be issued. But how/when does that happen?

          (0) 
          1. Praveen Kumar

            it is mostly as you assume here.

            for SPS updates , the LM tool handles it (SUM  / SPAM).

            depending  on tool and mode used (standard / shadow mode etc) , name of phase where these db DDLs are fired., differs.

            for new installations , migrations, R3load uses  ABAP dictionary definitions to handle this.

            BR

            Praveen

            (0) 
            1. Andrew Thuroczy

              Praveen,

              I also think that if the starting release  BW 7.31  is already on HANA then the proper SUM  level should  take care of the table type change.  I have a customer that upgraded from BW 7.31 to 7.40 SP05, but all the tables listed in note 1850112 are still in row store. The upgrade happened in March 2014 well before 1850112 was released.  I think the SUM tool the customer used was not  yet adjusted to change the table types.  The question is of course whether we can just go ahead an change it manually now as the BW is at 7.40 SP05.

              The next question whether it would require a new dbsl  version, although the table type in HANA should be transparent.

              (0) 
              1. Praveen Kumar

                Hey Andrew – try using RSDU_TABLE_CONSISTENCY (note 1750965, 1937062 ).

                I wouldn’t see dbsl to be an issue. If system was updated on 7.40 SP05, kernel would’ve been updated with it (if done ‘correctly’, of course).

                Plus, table type would come from ABAP dictionary, not dbsl – so dbsl doesn’t influence this part at least.

                BR

                (0) 

Leave a Reply