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

[Or]

From a cursory scan, it looks like you're manually applying IntervalMatch(), so perhaps have a look at that?

[jatin_qlikie]

Hi

Your code can definitely be optimized if we use Interval match on hours data ignoring the date to map the shifts Owner.
Do let us know if this helps.

[DS395]

Thank you for the quick response.

I'll have a look at it right away and test it. I hadn't heard of IntervalMatch() and will have a look at the examples.

When I've done it and it works, I'll let you know again, thanks.

[marcus_sommer]

Because of the fact that you want to distribute each record to n (shift) records you will need a loop-approach. But this could be done with internal-loops instead of external ones which made a huge difference in regard to the run-times.

The external loop accessed each single record from a source, grabbed n field-values to assign them to variables, which are then evaluated to initiated a load from a single record to the new target table. This will cause a lot of hops between the different engine parts and also I/O calls against the OS - each one with n milliseconds of delay. Or in other words - it must be slow.

Much better are internal loops which may go in your scenario in such a direction:

load *, applymap('Shifts', Hour, '#NV') as Shift;
load *, hour(start + iterno()) as Hour
from Source while iterno() <= ceil((end - start) / 24);

That's now not a final solution else a starting point and you will need to include a date-level for the overlapping dates, checking the round-logic, further date/time-calculations and probably various exception-handling for holidays, open orders, what ever ... and a more optimized while-loop logic to reduce the number of iterations or an appropriate filtering on the first record per shift.

[DS395]

I have just tried the IntervalMatch() and I am not sure if this is correct. Because you can only check a date to see if it is between a value. And then output the respective shift. Or have I used this incorrectly? Here is my source code example:

ProductionTime:
LOAD 
    id, 
    machine, 
    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 06:16:00, 2025-03-03 18:00:00
2, LOR011, 2025-03-03 18:00:00, 2025-03-04 21:30:00
3, LOR012, 2025-03-03 05:00:00, 2025-03-03 12:30:00
];

Shift:
LOAD 
    shift, 
    Timestamp(shift_start) AS shift_start, 
    Timestamp(shift_end) AS shift_end
INLINE [
shift, shift_start, shift_end
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
];

INNER JOIN IntervalMatch ( start_time )
  LOAD shift_start, shift_end
RESIDENT Shift;

[marcus_sommer]

The intervalmatch() is only useful if there is an added value to keep two tables in the data-model to match numerical ranges against a dedicated numeric value. I think this will be rather seldom. Of course it's possible to join the results back but IMO it's not the simplest and most powerful way to get such a result else the above hinted while-loop approach would be more suitable.

Beside this you could simplify your logic by relating the shifts to hours instead of times which makes it easier possible to create appropriate distribution-loops which may completely relate to predefined mappings. Here an example how such mapping-basis might developed and then applied:

m1: mapping load recno() - 1 as Hour, if(recno() - 1 >= 21, 'nacht', if(recno() - 1 >= 13, 'spät', if(recno() - 1 >= 5, 'früh', 'nacht'))) autogenerate 24;

t:

load *, rowno() as RowNo, if(Key <> previous(Key), Shift, if(Shift <> previous(Shift), peek('Shifts') & ' + ' & Shift, peek('Shifts'))) as Shifts, -(IterNo = Diff) as MaxFlag;

load *, applymap('m1', mod(StartHour + iterno() - 1, 24)) as Shift, iterno() as IterNo
while iterno() <= Diff;

load *, Diff * 100 + StartHour as Key;

load *, applymap('m1', StartHour) as StartShift;

load recno() as RecNo, recno() as Diff, iterno() - 1 as StartHour
autogenerate 100 while iterno() <= 24;

m2: mapping load Key, Shifts resident t where MaxFlag;

final:

load *, 

time(

if(IterNo = 1, pick(match(Shift, 'früh', 'spät', 'nacht'), 5/24, 13/24, 21/24) - frac(Start),

if(IterNo = substringcount(Shift, ' + ') + 1, frac(Ende) - pick(match(Shift, 'früh', 'spät', 'nacht'), 5/24, 13/24, 21/24), 8/24))) as Duration;

load *, subfield(applymap('m2', ceil((End - Start) / 24) * 100 + Hour(Start)), ' + ', iterno()) as Shift, iterno() as IterNo

resident Source 

while iterno() <= substringcount(Shift, ' + ') + 1;

which is again no final solution and may need some fine-tuning. Just comment the final part to look and comprehend the temporary table t which used three internally nested load-loops (autogerate 100 for 100 hours difference between End and Start, while with 24 to create the hours and another while with again 100). The final mapping is then appropriate filtered - and although the run-times isn't essential it could be later stored as qvd.

[DS395]

I don't understand the complete code. First, the possible shifts are created in hours, do I understand that correctly?

How should the start of the shift be linked to the end of the shift in the table?

Example: 4.3.25 2:30 am to 5.3.25 6:07 am?

Somehow I have the feeling that I am thinking wrongly, as I have been working on this topic for some time. Sorry, I think I will need some more time to understand this way of solving.

[marcus_sommer]

Yes, the example relates to hours because they are fitting to exactly the mentioned shifts. This is a simplification to work with integers which are simple to control the loop-approaches - and it's in general avoiding to handle the double-stuff. Even if shifts are defined to a half/quarter hour-level it would be possible to remain by such a logic - it would be simply 48 respectively 96 parts of a day instead of 24 hours.

The record-tracking is also simple because recno() tracked the source-records and iterno() will numbers the populated records (by nested logic n IterNo could be integrated) and rowno() will count all new records. All origin record-information will remain in the records if they are directly defined or loaded with the wildcard * which means in each origin and populated record are all information available even by n preceding-steps.

Just take a closer look on the temporary table t within a table-box by ensuring that the load-order is enabled and then making this and that selection and it's not difficult to track the progress-process from the RecNo to the n IterNo to see what has changed respectively added.

After that you should also recno() to the final and you may need some more if-loops (like the one for the duration) to add/define all further shift-information.

[DS395]

I have looked at your post and worked out the following as a solution for myself, but currently only tested it on my 3 test data sets. I will test it later today on the 100000+ data sets and see if the performance is better than with the loop before. Although there is certainly room for improvement here too.

//################################ 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];
LEFT JOIN ([ProductionTimeExpanded])
LOAD 
	 [shift]
	,[shift_start]
	,[shift_end]
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];