Will there performance improvement over separate tables vs single table with multiple partitions?

[Former Member]

Will there performance improvement over separate tables vs single table with multiple partitions? Is advisable to have separate tables than having a single big table with partitions? Can we expect same performance having single big table with partitions? What is the recommendation approach in HANA?

[former_member184768]

Adding to what Lars mentioned above, I think there is also a point regarding maintenance and usage of multiple tables vs single table. Any use of splitting the table to multiple objects in HANA information model, will result in additional development effort for the multiple tables. Also from the maintenance perspective, any change to the table structure will have to be reflected to all the other tables and associated HANA objects.

I will also suggest to consider this point too.

Regards,

Ravi

[lbreddemann]

Suren,

first off a friendly reminder: SCN is a public forum and for you as an SAP employee there are multiple internal forums/communities/JAM groups available. You may want to consider this.

Concerning your question:

You didn't tell us what you want to do with your table or your set of tables.

As tables are not only storage units but usually bear semantics - read: if data is stored in one table it means something else than the same data in a different table - partitioned tables cannot simply be substituted by multiple tables.

Looked at it on a storage technology level, table partitions are practically the same as tables. Each partition has got its own delta store & can be loaded and displaced to/from memory independent from the others.

Generally speaking there shouldn't be too many performance differences between a partitioned table and multiple tables.

However, when dealing with partitioned tables, the additional step of determining the partition to work on is always required. If computing the result of the partitioning function takes a major share in your total runtime (which is unlikely) then partitioned tables could have a negative performance impact.

Having said this: as with all performance related questions, to get a conclusive answer you need to measure the times required for both alternatives.

- Lars