Loading performance of the app is very slow - Can the app be improved?

[DS395]

Hello together,

I have the following question. I use this code with two loops to get my desired result. Is it possible to make this code without loops with different joins to shorten the loading time of the APP?

//############################################## LOAD PRODUCTION TIMES ##############################################
[PRODUCTION_TIME]:
FIRST 5 LOAD
	 [department]	AS [department]
	,[machine_name]	AS [machine]
	,[start_time]	AS [start_time_original]
	,[end_time]		AS [end_time_original]
FROM 
[lib://DWH_Raw/DIM$MachineSchedule.QVD] (qvd)
WHERE 
	 reason = 'Auftragszeit';

//######################################## LOAD SHIFT AS CALENDAR TABLE##############################################
[Calendar]:
LOAD 
    DATE([Start Datum] + ITERNO() - 1) AS [Datum]
WHILE [Start Datum] + ITERNO() - 1 <= [End Datum];

LOAD
    MIN([start_time]) AS [Start Datum],
    MAX([end_time]) AS [End Datum]
FROM
    [lib://DWH/DIM$MachineSchedule.QVD] (qvd);

LEFT JOIN (Calendar)
LOAD * INLINE [
    shift, shift_start, shift_end
    Frühschicht, '05:00:00', '12:59:59'
    Spätschicht, '13:00:00', '20:59:59'
    Nachtschicht, '21:00:00', '04:59:59'
];

[Shifts]:
LOAD
    shift,
    If(shift = 'Nachtschicht', Timestamp(Date([Datum] & ' ' & shift_start), 'YYYY-MM-DD hh:mm:ss'), Timestamp(Date([Datum] & ' ' & shift_start), 'YYYY-MM-DD hh:mm:ss')) AS shift_start,
    If(shift = 'Nachtschicht', Timestamp(Date([Datum] & ' ' & shift_end) + 1, 'YYYY-MM-DD hh:mm:ss'), Timestamp(Date([Datum] & ' ' & shift_end), 'YYYY-MM-DD hh:mm:ss')) AS shift_end  
RESIDENT Calendar;

DROP TABLE [Calendar];

//############################################## RESULTTABLE INITALISIEREN ##############################################
ResultTable:
LOAD * INLINE [
id, machine, start_time_original, end_time_original, shift, start_time_shift, end_time_shift, duration_time
];
// Iteration durch die PRODUCTION_TIME
FOR i = 0 to NoOfRows('PRODUCTION_TIME') - 1
    LET v_department = Peek('department', i, 'PRODUCTION_TIME');
    LET v_machine = Peek('machine', i, 'PRODUCTION_TIME');
    LET v_start_time_original = Peek('start_time_original', i, 'PRODUCTION_TIME');
    LET v_end_time_original = Peek('end_time_original', i, 'PRODUCTION_TIME');

    // Iteration durch die Shifts
    FOR j = 0 to NoOfRows('Shifts') - 1
        LET v_shift = Peek('shift', j, 'Shifts');
        LET v_shift_start = Peek('shift_start', j, 'Shifts');
        LET v_shift_end = Peek('shift_end', j, 'Shifts');

        // Überprüfen, ob die Schicht innerhalb des Zeitrahmens liegt
        IF (v_start_time_original < v_shift_end) AND (v_end_time_original > v_shift_start) THEN
            // Berechnung der Start- und Endzeiten der Schicht
            LET v_start_time_shift = If(v_start_time_original < v_shift_start, v_shift_start, v_start_time_original);
            LET v_end_time_shift = If(v_end_time_original > v_shift_end, v_shift_end, v_end_time_original);

            // Ergebnis in die ResultTable laden
            CONCATENATE (ResultTable)
            LOAD 
                '$(v_department)' as department,
                '$(v_machine)' as machine,
                '$(v_start_time_original)' as start_time_original,
                '$(v_end_time_original)' as end_time_original,
                '$(v_shift)' as shift,
                '$(v_start_time_shift)' as start_time_shift,
                '$(v_end_time_shift)' as end_time_shift
            AUTOGENERATE 1;
        ENDIF
    NEXT j
NEXT i

ResultTable_neu:
LOAD 
	 department
    ,machine
    ,start_time_original
    ,end_time_original
    ,shift
    ,start_time_shift
    ,end_time_shift
    ,Interval([end_time_shift] - [start_time_shift], 'hh:mm:ss') AS duration
    ,([end_time_shift] - [start_time_shift]) * 24 AS duration_hours
RESIDENT ResultTable;


DROP TABLE PRODUCTION_TIME;
DROP TABLE Shifts;
DROP TABLE ResultTable;

Example Data:

Example Result Data:

I get the result with my code, but the loading times are extremely slow. I would be grateful for any advice on how I can improve the performance of my code.

Best regards,

DS395

[marcus_sommer]

Personally I would skip the the group by load because it's a rather heavy transformation especially against several fields. Of course it's a reduce of the data-set but the impact to the storage/RAM consumption and the UI calculation times is often quite low - in my experience often too low to the extra complexity in the script, the longer run-times within the script and the lost of granularity to validate the data respectively to use them within any views.

[DS395 · Accepted solution]

I have now checked both variants. There is a difference of 11 seconds with a large amount of data. As the report will only run once during the night. I think I will work with the filtered data, as it feels a bit better for me personally. But thank you all for the help and support!

My final result is like this with the example datas:

//################################ TEST DATA ################################
[ProductionTime]:
LOAD 
	 [id]
	,[machine]
	,[start_time_original]
	,[end_time_original]
	,TIMESTAMP([start_time_original])							AS [start_time]
	,TIMESTAMP([end_time_original])								AS [end_time]
INLINE [
id, machine, start_time_original, end_time_original
1, LOR011, 2025-03-03 12:30:00, 2025-03-03 13:21:00
2, LOR011, 2025-03-03 18:00:00, 2025-03-04 21:30:00
3, LOR012, 2025-03-03 04:00:00, 2025-03-03 05:59:00
];

[Shifts]:
LOAD 
	 [shift]
	,TIMESTAMP([shift_start])									AS [shift_start]
	,TIMESTAMP([shift_end]) 									AS [shift_end]
INLINE [
shift, shift_start, shift_end
Nachtschicht, 2025-03-02 21:00:00, 2025-03-03 04:59:59
Frühschicht, 2025-03-03 05:00:00, 2025-03-03 12:59:59
Spätschicht, 2025-03-03 13:00:00, 2025-03-03 20:59:59
Nachtschicht, 2025-03-03 21:00:00, 2025-03-04 04:59:59
Frühschicht, 2025-03-04 05:00:00, 2025-03-04 12:59:59
Spätschicht, 2025-03-04 13:00:00, 2025-03-04 20:59:59
Nachtschicht, 2025-03-04 21:00:00, 2025-03-05 04:59:59
];
//####################### PRODUCTION TIMES FOR EVERY SECOND #######################
[ProductionTimeExpanded]:
LOAD 
	 [id]
	,[machine]
	,[start_time_original]
	,[end_time_original]
	,TIMESTAMP([start_time] + (ITERNO()-1) * (1/24/60))			AS [time_stamp]
RESIDENT 
	 [ProductionTime]
WHILE 
	 [start_time] + (ITERNO()-1) * (1/24/60) <= [end_time];
     
JOIN INTERVALMATCH ([time_stamp])
LOAD
	 [shift_start]
	,[shift_end]
RESIDENT
	 [Shifts];
     
LEFT JOIN ([ProductionTimeExpanded])
LOAD
	 [shift_start]
	,[shift_end]
	,[shift]
RESIDENT
	 [Shifts];
     
DROP TABLE [ProductionTime];
DROP TABLE [Shifts];

//####################### FILTER PRODUCTION TIMES #######################
[FilteredProductionTime]:
LOAD
	 *
WHERE 
	 [filter] = -1;
LOAD
	 [id]
	,[machine]
	,[time_stamp]
	,[shift]
	,[shift_start]
	,[shift_end]
	,[start_time_original]
	,[end_time_original]
	,[time_stamp] >= [shift_start] 
     AND [time_stamp] <= [shift_end]							AS [filter]
RESIDENT 
	 [ProductionTimeExpanded];

DROP TABLE [ProductionTimeExpanded];
//####################### AGGREGATE PRODUCTION TIMES + CALC FIELDS #######################
ProductionTimePerShift:
LOAD
	 *
	,INTERVAL([used_end_time] - [used_start_time], 'hh:mm:ss') 	AS [duration]
	,([used_end_time] - [used_start_time]) * 24 				AS [duration_hours];
LOAD
	 [id]
	,[machine]
	,SUM([time_stamp])
	,[shift]
	,[shift_start]
	,[shift_end]
	,[start_time_original]
	,[end_time_original]
	,IF([start_time_original] < [shift_start]
    		,[shift_start]
            ,[start_time_original]) 							AS [used_start_time]
	,IF([end_time_original] > [shift_end]
    		,[shift_end]
            ,[end_time_original]) 								AS [used_end_time]
RESIDENT
	 [FilteredProductionTime]
GROUP BY 
	 [id]
	,[machine]
	,[shift]
	,[shift_start]
	,[shift_end]
	,[start_time_original]
	,[end_time_original];

DROP TABLE [FilteredProductionTime];