What is the fastest way to populate table with dates after certain day?

Question

Let's assume that we have the following input parameters:

• date [Date]
• period [Integer]

The task is the following: build the table which has two columns: date and dayname.

So, if we have date = 2018-07-12 and period = 3 the table should look like this:

date      |dayname 
-------------------
2018-07-12|THURSDAY
2018-07-13|FRIDAY
2018-07-14|SATURDAY

My solution is the following:

 select add_days(date, -1) into previousDay from "DUMMY";
 for i in 1..:period do
     select add_days(previousDay, i) into nextDay from "DUMMY";
     :result.insert((nextDay, dayname(nextDay));
 end for;

but I don't like the loop. I assume that it might be a problem in the performance if there are more complicated values that I want to put to result table.

What would be the better solution to achieve the target?

Answer 1

Running through a loop and inserting values one by one is most certainly the slowest possible option to accomplish the task.

Instead, you could use SAP HANA's time series feature. With a statement like

SELECT to_date(GENERATED_PERIOD_START)
    FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 DAY', '01.01.0001', '31.12.9999')

you could generate a bounded range of valid dates with a given interval length.

In my tests using this approach brought the time to insert a set of dates from ca. 9 minutes down to 7 seconds...

I've written about that some time ago here and here if you want some more examples for that. In those examples, I even included the use of series tables that allow for efficient compression of timestamp column values.