Finding the Max(fieldname) by load from QVD takes longer than if loar from Resident.

[Peony]

Hi all. 

I have a table with approximately 20 000 000 lines in it. I need to define Max(Timestamp) from this table for incremental load. Currently I discovered that if I get max like below, it takes about 4 minutes.

tmp:
LOAD
Max(LASTUPDATE)
FROM Table.qvd(qvd);

But when I get max in 2 steps, it takes 6 seconds. 

tmp:
LOAD
LASTUPDATE
FROM Table.qvd(qvd);

tmp2:
Load
Max(LASTUPDATE) as maxupdate
Resident tmp;

Could anyone explain why these 2 steps much faster than finding Max while QVD is loading? 

[JandreKillianRIC · Accepted solution]

Hi @Peony 

Because the 2nd way you do a optimized load. Once this is in memory Qlik can get the max much easier then doing this from the QVD. I generally test these things as sometimes (Most cases) just loading the file and then doing a resident the results differ A LOT! 

Regards Jandre

[Peony]

@JandreKillianRIC  Hi and thank you for your reply. Now things become a way understandable for me. 
And maybe you have some advice how to make load using NOT Exists ( field1, field2) a bit optimized? Since this part is next in incremental load process, it is loading speed is like another bottleneck for performance.

[rwunderlich]

You can do it in one second 😉 like:

tmp:
LOAD
LASTUPDATE
FROM Table.qvd(qvd);

tmp2:
Load
Max(FieldValue('LASTUPDATE', RecNo())) as LASTUPDATE
AutoGenerate FieldValueCount('LASTUPDATE');

If you use the same name for the LASTUPDATE field, you can use a single parameter "Exists(LASTUPDATE)" which will be optimized.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[JandreKillianRIC]

Hi @Peony 

If @rwunderlich post doesn't solve your problem I suggest creating a new post with this question. 

But @rwunderlich answer is spot on! 

Regards Jandre

[Peony]

@rwunderlich thank you much for your answer!!