topic Re: Preceding Load Performance in QlikView

Preceding Load Performance

Anonymous — Tue, 20 Mar 2018 12:35:20 GMT

Hello, i have read

and https://www.quickintelligence.co.uk/preceding-load-qlikview/ but i still have some dubts about preceding load. Is this:

Load ..., ReferenceDate,

Age( ReferenceDate, BirthDate ) as Age;

Load *,

Date( FromDate + IterNo() – 1 ) as ReferenceDate

Resident Policies

While IterNo() <= ToDate - FromDate + 1 ;

Is faster than This:

TmpTable:

Load *,

Date( FromDate + IterNo() – 1 ) as ReferenceDate

Resident Policies

While IterNo() <= ToDate - FromDate + 1 ;

Table:

Load ..., ReferenceDate,

Age( ReferenceDate, BirthDate ) as Age

From TmpTable;

And Why. So It's preceding load doing some kind of shortcut or just a way to to don't have the need to have lots of temp tables when you do calculations like flags etc.

Re: Preceding Load Performance

hic — Tue, 20 Mar 2018 12:43:22 GMT

Preceding Load is indeed a kind of shortcut: Records from the first (bottom) Load are piped into the preceding Load (the upper load), so that only one pass is made through data.

With a resident load from a temp table, multiple passes are made, and this usually takes more time.

HIC

Re: Preceding Load Performance

Anonymous — Tue, 20 Mar 2018 13:15:47 GMT

Thank you hic‌, I assume then that the time difference is due to save, rename, load and drop and it is not like the time that takes to load one per register is less with the preceding load like happen if you compare the loadtime per register of a QVD file compared to a csv (and maybe a resident ?).

Re: Preceding Load Performance

hic — Tue, 20 Mar 2018 15:13:59 GMT

The time difference is due to the fact that a record needs to be read twice when using a resident load (once in the original load, and once in the resident load). It has nothing to do with the file format.

However, we have had a bug that affected the multi-threading of a preceding load, and made this slower, so you got the "opposite" result. I'm not sure about the status of this bug, but if it is fixed, the preceding load is faster.

HIC

Re: Preceding Load Performance

marcus_sommer — Tue, 20 Mar 2018 16:06:56 GMT

Hi Henric,

are you sure that a preceeding load-chain should be always faster as a chain of resident-loads?

Without a special need to optimize the load-times I prefer the preceeding load-approach because it's easier and provides a better overview but I had had cases in which a resident load-chain was approximately 2 - 3 times faster than the preceeding load-chain. Of course it's a bit comparing apples with pears because I reduced a 5-load-chain into a 2-load-chain but even if I had also reduced the preceeding-chain to maybe a 2/3-load-chain it wouldn't be faster as the resident-chain.

Further I should mention that the loadings contains a lot of fields and dozens of quite heavily nested calculations and the used release was QV 11.2 SR 12.

There is already a good testing to this topic which results in a quite significantely overhead by preceeding loads - is it really caused from a bug or are there further impacts?

The Cost of Preceding Load | Qlikview Cookbook

- Marcus

Re: Preceding Load Performance

hic — Wed, 21 Mar 2018 08:06:53 GMT

Marcus

I am aware of Rob's test that you link to, and Rob's findings are correct. They are however caused by what I consider to be a bug. The bug is that a preceding Load causes the Load to become single-threaded, and thus slower. So if you compare a single-threaded preceding Load with a multi-threaded resident Load, then the latter is faster.

I am unsure about the status of the bug, but once fixed I dare say that a preceding Load (one step in a preceding-Load-chain) should always be faster than a resident Load.

HIC

Re: Preceding Load Performance

dionverbeke — Wed, 21 Mar 2018 08:10:03 GMT

Thanks,

I also have a long outstanding question:

Which is better/faster :

RESIDENT load or

WRITING TO QVD AND OPTIMIZED load?

Dion.

Re: Preceding Load Performance

hic — Wed, 21 Mar 2018 12:45:38 GMT

I would guess that a resident Load is faster, mainly because of the disk access time of the Store command. However, with today's solid state disks, disk access is fast, so I am not sure...

But what's the use case? If you need to load the table once more, then it is probably because you need to transform data in some way, and then the QVD will not be loaded optimized. Or ... ?

HIC

Re: Preceding Load Performance

Anonymous — Thu, 22 Mar 2018 15:42:21 GMT

And why is that the preceding load does not need to read the records twice? I guess that is kind of a way (the only one) to crate a calculated column and read it while reading the same row in order not to do the calculation twice. More or less the same that would happen if existed the function "peek( Calculated_Column, 0 )"

Re: Preceding Load Performance

Peter_Cammaert — Thu, 22 Mar 2018 16:28:51 GMT

Mainly because in a preceding load, the result of the calculations done on each individual row during the first load are kept in a 1-record buffer and this single-record buffer then serves as source for the calculations done during the preceding load before the end-result is stored as a single row in the final table. And this is done record-by-record.

That is probably what Henric describes as "Records from the first (bottom) Load are piped into the preceding Load".

Or as a list of operations:

Open the source table
Read the first record
Perform 1st LOAD operations on this record
Perform 2nd LOAD operations on this record
Store row in target table
Read second record
...

Re: Preceding Load Performance

olivierrobin — Thu, 22 Mar 2018 16:36:16 GMT

i've also noticed a point (and retested just now)

if you do a preceding load, you loose the optimized load of a qvd

so in this case, it's better to do 2 load : 1 optimized from qvd, one from resident

Re: Preceding Load Performance

rwunderlich — Thu, 22 Mar 2018 20:03:22 GMT

In the case of a preceding load, I found reloading from a QVD significantly faster than a Resident load. 2-3 times faster. I have test data going all the way back to V9.

In the Nov 2017 QV release, things have changed. I believe the bugfix that Henric mentioned was released. Testing against that release shows resident is now faster than qvd. Multi-processing is back!

The really excellent news is that I no longer see any penalty in using preceding load. I'll do some more extensive testing and get a blog post out next week.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Preceding Load Performance

rwunderlich — Tue, 27 Mar 2018 21:26:18 GMT

I've blogged about the performance update here

Preceding Load Performance Update | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com