Question Re: Seeking Advice: Tips on using large table without table key for reporting and VDM in Technology Q&A

Seeking Advice: Tips on using large table without table key for reporting and VDM

Sebastian_Gesiarz — Thu, 09 Feb 2023 08:25:39 GMT

Hello,

I am seeking advice on optimizing the process of using a large table without a key defined for reporting. (DWC)

I happen to have a 1bln+ and a quickly growing table needed for reporting. It has about 16 columns. There will be an archiving & compression process in place. It has about 7GB, planning to store it in memory.

Currently, using DWC Data Flow to populate the table with a key defined takes over 11 hours for 19 million records, which is not feasible. However, appending 24 million records to the table without a key specified takes only 7 minutes. This is why no key is used. The data source is a virtual model running on already replicated data. I suppose creating indexes, updating the data dictionary and unique checks take more time. I have not yet tried to populate the table using a procedure instead of a DWC Data Flow.

There are additional lookups created on this table (VDM, Standard Views) for value calculation and amount conversion.
The plan, for now, was to create multiple aggregate views for specific SAC widgets and add input parameters based on time and key dimensions.

Any suggestions or alternative perspectives that could steer me in the right direction would be greatly appreciated

Cheers,

Sebas

Re: Seeking Advice: Tips on using large table without table key for reporting and VDM

michael_eaton3 — Thu, 09 Feb 2023 08:40:45 GMT

When inserting data into very large tables, a common technique is to load a staging table in the same database as the target table, then use a single statement to insert the whole staging table data in one hit. Partitioning the main table and running multiple parallel insertion processes can also increase throughput.

Re: Seeking Advice: Tips on using large table without table key for reporting and VDM

Sebastian_Gesiarz — Thu, 09 Feb 2023 08:45:21 GMT

Thank you, Michael. I will try that with Data Flow and if it performs too slowly then directly with SQL.

Re: Seeking Advice: Tips on using large table without table key for reporting and VDM

michael_eaton3 — Thu, 09 Feb 2023 09:09:15 GMT

I think you'll be best running directly via SQL, because the logic must be "pushed down" to the database.

Re: Seeking Advice: Tips on using large table without table key for reporting and VDM

Sebastian_Gesiarz — Thu, 09 Feb 2023 11:32:22 GMT

A small update, a conversion of a table without a key to one with a key is supported and runs fast. it's interesting to note that adding a key to the table has a negative impact on front-end performance, particularly when it comes to time series analysis (calender day is part of the key). I will keep you posted. I suppose I need to read and look more into SYS tables to understand how HANA handles tables without the key.