topic Re: Execution is taking long time for where condition in QlikView

Execution is taking long time for where condition

kishorj1982 — Sat, 16 Nov 2024 02:43:26 GMT

Hi All,

PFA.

I have attached the screenshot of the log file.

In the script file we have a condition as per below,

==>
WHERE Num ([Original Planned Delivery Date]) < Num (Today ()) AND (Isnull ([Comparison Date]) OR ([Delivered Quantity Punctuality] - [Original Confirmed Quantity]) < 0);

We have a huge volume of data and it seems that because of the above condition takes too much long time to reload.

Could you please advise how do I reduce the calculation time for where condition.

Thanks

Re: Execution is taking long time for where condition

Gysbert_Wassenaar — Fri, 16 Aug 2019 11:11:16 GMT

It's not necessarily the where clause that's the problem. Unless you specifically tested with and without and seen that it's indeed the where clause that's causing the long processing time of the complete load statement.

Re: Execution is taking long time for where condition

MindaugasBacius — Fri, 16 Aug 2019 11:50:13 GMT

Incremental load could be an answer.

Please find the reference:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/QVD_Incremental.htm

Re: Execution is taking long time for where condition

marcus_sommer — Fri, 16 Aug 2019 13:30:39 GMT

I could imagine that rather too less RAM or a heavy workload on your machine are causing this long load-time. Beside this it might be an option to transfer the condition into a flag-field in the resident-table, maybe with:

...
if(([Original Planned Delivery Date] < Today ()) AND (Isnull ([Comparison Date]) OR ([Delivered Quantity Punctuality] - [Original Confirmed Quantity]) < 0), 1, null()) as Flag
...

and then your where-clause could look like:

where exists(Flag);

- Marcus

Re: Execution is taking long time for where condition

kishorj1982 — Mon, 19 Aug 2019 07:13:00 GMT

Hi Markus,

There are already a number of resident loads. From the script below is the sample code for this.

Could you please advise how to write the resident load on the top of this.

CONCATENATE ([Order Delivery Days])
LOAD *,
Dual (If ([Delivery Difference Days] > 0,
If ([Delivery Difference Days] <= 5, 'Week or Less',
If ([Delivery Difference Days] <= 10, 'Two Weeks or Less',
If ([Delivery Difference Days] <= 20, 'Month or Less',
If ([Delivery Difference Days] <= 40, 'Two Months or Less', 'Over Two Months'))))),
If ([Delivery Difference Days] > 0,
If ([Delivery Difference Days] <= 5, 1,
If ([Delivery Difference Days] <= 10, 2,
If ([Delivery Difference Days] <= 20, 3,
If ([Delivery Difference Days] <= 40, 4, 5)))))) as [Late Status];
LOAD [Sales Document Item Key],
Sum ([Date Type]) as [Delivery Difference Days]
GROUP BY [Sales Document Item Key];
LOAD [Sales Document Item Key],
ApplyMap ('FactoryCalendar_map', [Original Factory Calendar] & '-' & Num ([Original Planned Delivery Date] + Iterno ()), 0) as [Date Type]
WHILE Num ([Original Planned Delivery Date] + Iterno ()) <= Num (Today ());
LOAD [Sales Document Item Key],
[Original Factory Calendar],
[Original Planned Delivery Date]
RESIDENT [Order Delivery]
WHERE Num ([Original Planned Delivery Date]) < Num (Today ()) AND (Isnull ([Comparison Date]) OR ([Delivered Quantity Punctuality] - [Original Confirmed Quantity]) < 0);

Thanks

Re: Execution is taking long time for where condition

marcus_sommer — Mon, 19 Aug 2019 08:39:59 GMT

Beside a possible overload on your machine there are multiple things which might increase the performance. One was already mentioned by transferring the check of the where-clause into a flag-field of the source-table. The next is the aggregation with group by within the preceeding-chain. I assume it will be rather a disadvantage from a performance point of view and I suggest to test if a split of the chain into 3 loadings would be faster.

Another point is your if-loop which could be optimized if the result of each check would be a dual value, like:

if(condition, dual(string, number), else ...

A small benefit might come by replacing the if-loop with a pick(match()) approach. More should be possible if you could use class() with continuing bucket.

- Marcus

Re: Execution is taking long time for where condition

kishorj1982 — Wed, 21 Aug 2019 11:36:12 GMT

Hi Markus, As suggested by you I have splitted my transformed load to number of separate calculations using resident load. It has been observed that below code is the main culprit behind the large reload time. Order_Delivery_Temp2: LOAD [Sales Document Item Key], ApplyMap ('FactoryCalendar_map', [Original Factory Calendar] & '-' & Num ([Original Planned Delivery Date] + Iterno ()), 0) as [Date Type] resident Order_Delivery_Temp1 WHILE Num ([Original Planned Delivery Date] + Iterno ()) <= Num (Today ()); As I am unable to get how to get rid of this. Could you please share your thoughts on this. Thanks.

Re: Execution is taking long time for where condition

marcus_sommer — Wed, 21 Aug 2019 12:15:31 GMT

While-loops are usually quite fast whereby if the number of iterations is rather big it will increase the number of records appropriate. I don't know neither your data nor your requirements but I could imagine that there are also other ways to generate the [Late Status] which might be more efficient.

Beside this the following should be (a bit) faster:

LOAD [Sales Document Item Key], ApplyMap ('FactoryCalendar_map', [Original Factory Calendar] & '-' & N, 0) as [Date Type];
LOAD [Sales Document Item Key], [Original Factory Calendar], [Original Planned Delivery Date] + Iterno() -1 as N
WHILE [Original Planned Delivery Date] + Iterno() -1 <= Today();

Further I'm not sure that you need the mapping and/or it maybe significantely simplyfied so that no loop is needed. Maybe something like this is also enough: Today() - [Original Factory Calendar]

Furthermore you may consider to apply an incremental approach.

- Marcus

Re: Execution is taking long time for where condition

kishorj1982 — Fri, 23 Aug 2019 09:49:46 GMT

Hi Markus,

The purpose of the script is to calculate the working days' difference between the planned delivery date and the current date based on the factory calendar. Simple subtraction will not work in this case as it will include all days including holidays. Using networkdays() function does not work this case either because while we can exclude Mon-Friday holidays using it, it will also always exclude Saturdays and Sundays from the calculation, which is not what we want as some Saturdays or Sundays might be working days for particular factory calendar. The mapping is needed for different KC factory calendars, which might have different holidays and scheduled working weekends. Basically, we need to have a solution that will consider different factory calendars for each row and will calculate the date difference based on that factory calendar. I would welcome a better solution as the while + mapping loop is quite heavy, but it should be able to do the same functionality as the existing logic.

thanks

Re: Execution is taking long time for where condition

marcus_sommer — Fri, 23 Aug 2019 12:03:43 GMT

I think I would try a different approach and do all this work within the calendars and then just picking the wanted value.

This means to create a continuous workday-counter within the calendar. Each workday - defined by the weekdays and which aren't defined holidays - get a 1 and the others 0 (beside applying them within a listbox or as a set analysis condition is such flag-field quite useful to sum the values over periods).

The next step would be to cummulate them with interrecord-functions like peek or previous. With it you could pick there the cummulated value from today and then subtracting from it the cummulated value from your delivery date.

But I think you could go even a step further and adding in the calendar another field which calculates already there the differenz between each date and today - and to this result could be already the [Late Status] bucket mapped.

After all you don't need the whole approach of the while-looping, aggregating and clustering else just picking the right value with a mapping.
If there are multiple different factories and appropriate calendars you may need to repeat it a few times and adding them together but it shouldn't be too difficult and mean not more efforts as you already have with your calendar-creation.

- Marcus

Re: Execution is taking long time for where condition

kishorj1982 — Fri, 23 Aug 2019 14:30:02 GMT

Thanks, Markus,

Could you please share a sample example for this because I am unable to get how to proceed.

Regards,

Kishor

Re: Execution is taking long time for where condition

marcus_sommer — Fri, 23 Aug 2019 14:52:24 GMT

Some parts of it (creating a 0/1 workday flag based on weekdays and a separate holiday-list which is considered with a mapping) are included here: Master-Calendar-with-movable-holidays. Not included is the cumulating per Peek-or-Previous but it shouldn't be too complicated to apply especially if the calendars are created separately it would be quite simple, like:

load *, rangesum(peek('WorkdayFlagCum'), WorkdayFlag) as WorkdayFlagCum;
load ... normal calendar ...;

The mentioned assigning of the today() value might be possible with a lookup() in another preceeding load. If not it would be of course possible to fetch it with a resident-load on this calendar table and then mapping/joining it back and applying on it the bucket-mapping.

- Marcus

Re: Execution is taking long time for where condition

kishorj1982 — Tue, 27 Aug 2019 11:14:26 GMT

Hi Markus,

I am trying for this but unable to achieve the results. Also, my results are affecting.

My aim is to reduce the overall reload time for while condition without affecting the results..

Below is the sample code is this.

LOAD *,
Dual (If ([Delivery Difference Days] > 0,
If ([Delivery Difference Days] <= 5, 'Week or Less',
If ([Delivery Difference Days] <= 10, 'Two Weeks or Less',
If ([Delivery Difference Days] <= 20, 'Month or Less',
If ([Delivery Difference Days] <= 40, 'Two Months or Less', 'Over Two Months'))))),
If ([Delivery Difference Days] > 0,
If ([Delivery Difference Days] <= 5, 1,
If ([Delivery Difference Days] <= 10, 2,
If ([Delivery Difference Days] <= 20, 3,
If ([Delivery Difference Days] <= 40, 4, 5)))))) as [To Be Delivered Status];
LOAD [Sales Document Item Key],
Sum ([Date Type]) as [Delivery Difference Days]
GROUP BY [Sales Document Item Key];
LOAD [Sales Document Item Key],
ApplyMap ('FactoryCalendar_map', [Original Factory Calendar] & '-' & Num ([Original Planned Delivery Date] - Iterno () + 1), 0) as [Date Type]
WHILE Num ([Original Planned Delivery Date] - Iterno () + 1) >= Num (Today ());

Could you please suggest what kind of modifications do we need to do for this.

Thanks

Re: Execution is taking long time for where condition

marcus_sommer — Tue, 27 Aug 2019 11:59:51 GMT

My last suggestion was not to make some modifications else to change the whole datamodel. If my first suggestions with splitting the preceeding load-chain into several parts and the various mentioned approaches of optimization didn't speed enough the load-times - then you need to do it in a different way.

One would be to implement an incremental logic for it and another my last suggestion to transfer the date-calculations and their bucket-matching into the calendar-level and then just picking the values from there. I don't think that this approach is very difficult to develop else I assume that's easier than your existing one. But you shouldn't do it in your existing application else create a new one and build one or two of these calendars and a reduced dataset of your fact-table and devloping it step by step.

- Marcus