topic Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())? in QlikView

Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Fri, 06 Apr 2018 10:50:04 GMT

Hi,

I am trying to improve the performance of an app by trying to turn some LOADs into optimized LOADs and stuff.

I managed to take some time off the script-duration by replacing one RESIDENT_LOAD with a PRECEDING_LOAD and another one with a temporarily stored qvd_file - I might look at the possibility of having another PRECEDING_LOAD to replace that, too.

Now I have one more LOAD from a qvd-file with a filter applied, currently through an APPLYMAP - what this does is filter out several (currently two) values coming from an Excel file.

<=> The issue now is, an optimized LOAD from qvd allows an EXISTS() clause - but I want to EXclude the values in this Excel file - is there any possibility of inserting a NOT(EXISTS()) clause and still have an optimized LOAD or will I have to go the other way and first extract a listing of all possible values from somewhere and then INclude all of those (save those that should be filtered out)?

Thanks a lot!

Best regards,

DataNibbler

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

marcus_sommer — Fri, 06 Apr 2018 11:03:22 GMT

You could also use: where not exists(Field); to keep the load optimized because exists() just returned TRUE or FALSE and NOT will reverse this result. But you might need some further measures to get it working which could be slightly different to a pure exists() which rather worked like a white-listing and now you need a black-listing ...

- Marcus

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

rwunderlich — Fri, 06 Apr 2018 11:12:00 GMT

Adding NOT will not changed the optimized load. That is, if "Exists(Key)" gives you an optimized load, then "NOT Exists(Key)" will as well.

-Rob

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Fri, 06 Apr 2018 11:18:52 GMT

Many thanks!

That should work, I'll try out.

There is another issue, though: One of the fields in the table is transformed to a string using the TEXT() function. That would probably make the LOAD non-optimized again. I'll have a look to see whether and how I can avoid that.

Best regards,

DataNibbler

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

PrashantSangle — Fri, 06 Apr 2018 11:22:26 GMT

You can handle it while creating qvd.

Use preceding load in extractor.

Regards,

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Fri, 06 Apr 2018 11:27:44 GMT

Hi Rob,

I don`t quite get it - now I have a LOAD without any transformation of fields, only an EXISTS() clause at the end - I have just left out that TEXT() thingy, just to try - and still it is non-optimized.

The code is simple, just

>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

LOAD

blablubb,

blubberbla,

[more fields]

FROM

[path to qvd] (qvd)

WHERE EXISTS([what to look for], [in field])

;

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

(and prior to that I load from an Excel file currently two records which should later be excluded)

What could be the reason that this LOAD is still unoptimized?

Best regards,

DataNibbler

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

olivierrobin — Fri, 06 Apr 2018 12:08:36 GMT

hello

in v12, it works with not exists, it was not the case in v10

but even in v12, it doesn't work if the two parameters don't have the same name

so WHERE EXISTS([what to look for], [in field]) isn't optimized

but WHERE EXISTS([in field]) or WHERE EXISTS([in field],[in field]) will work

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

marcus_sommer — Fri, 06 Apr 2018 12:31:18 GMT

Only exists() with a single parameter will keep the optimized load because everything else will force a processing of the data which meant that the data couldn't directly transferred into the RAM.

This meant you need some more efforts to get the right fieldnames ... Beside developing the whole load-approach at the beginning in this way it's often useful to use RENAME to adjust the approach to the right fieldnames, for example with something like this:

...

rename field [in field] to [what to look for];

....

where exists([what to look for]);

...

Depending on the complexity of your load you might need several of such from and to renamings and/or creating of appropriate white/blacklist for the exists(). Therefore you should consider if a different load-order and/or using a more granular multi-layer approach might not easier to handle - maybe not by creating but if it comes to mainaining it will ...

- Marcus

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Fri, 06 Apr 2018 12:43:28 GMT

Hmm ... the issue is, I need that TEXT() transformation on the original qvd - it comes from an EXTRACTOR which a colleague has done in SAP with no transformations applied. I cannot access that.

That transformation means I cannot get that LOAD optimized anyway. For some reason, that last LOAD - there is a JOIN involved - now takes longer than it used to, outweighing all performance improvements I have achieved on the script so far ;-(

I'll have another look into it and if I can`t get that straight today, I`ll just leave it as it is.

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Fri, 06 Apr 2018 13:16:34 GMT

Hmm ... I guess the network is not extremely fast, so replacing any RESIDENT Loads by first saving a temporary file to qvd and then loading it again does not improve overall performance a lot (or next to nothing) because saving the temporary file takes so long ...

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

marcus_sommer — Fri, 06 Apr 2018 13:38:24 GMT

It sounds that there are multiple bottlenecks in your environment ... maybe you could merge some of the transformations into lesser load-steps and/or implementing any respectively more incremental approaches. But without using an appropriate multi-layer environment on modern hardware with local ssd-drives it will be far away from the potential performances.

- Marcus

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Fri, 06 Apr 2018 14:01:08 GMT

Hi Marcus,

I'm afraid you're right. We do have a multilayer environment here (there are extractors, several transform_steps, a datamodel_layer and finally the GUI) and I have a decent notebook here with 16GB of RAM, but I don't know about the server hardware and network - well, I can tell from the QMC that the QVS has 8 CPUs ... and I have the application locally, but not the data.

Not good ...

Well, I guess there's not too much I can do in that script. I'll have a look at a different one.

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Mon, 09 Apr 2018 09:05:11 GMT

Hmm ... the QMC tells me that this particular transformation step usually takes about 12 minutes to finish on the server - at night. That surprises me a bit because when I ran it locally on Friday, it took about 6 minutes (and it had to transfer the data back and forth).

Still, it seems worthwhile to look at that script once again as it seems to be the longest-running step in that entire report (there are two more transformations which take about 5sec each, a datamodel and a GUI which take about 2min). Sometimes, when looking at the same thing for the 100th time, you suddenly see something that you overlooked 99 times ...

Best regards,

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

marcus_sommer — Mon, 09 Apr 2018 09:52:04 GMT

There could be various causes to why a certain load-process is faster on one machine and slower on the other, for example the available resources, parallel running tasks, storage/network-speed, possible CPU frequencies in regard to single/multi-thread executions, the "age" of your hardware and probably there are some more reasons ...

- Marcus

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

datanibbler — Thu, 12 Apr 2018 08:19:33 GMT

OK.

I'll close this thread now. From what I have seen so far, it seems that just about every app there is takes about twice as long to run on the server as compared to my local notebook 😉

Optimizing performance is not easy when every temporary STORE takes ages and saving space is not easy, either as all the data is required by various people to always be there to look into without spending any time reloading.

So, for the time being, it seems there`s nothing I can do.

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

evan_kurowski — Mon, 16 Apr 2018 17:14:35 GMT

There are definitely environmental considerations that may not be fully understood, or documented, that are capable of inhibiting QVD load optimization (even when all conditions conducive to optimization are present). This may be giving some of our users false-readings (myself included).

I have seen QVD occasionally stop reporting optimization, even when read stand-alone with no transformation.

Also, as has been mentioned, using NOT EXISTS() with two different field name arguments is working optimized for me at the present moment.

But I wanted to use this very same technique last week, and using NOT EXISTS (with no transformation beyond an alias) and it was definitely deactivating the optimization.

[FILTER]:
LOAD FILTER FROM [FILTER.QVD] (qvd);

//on the latest test 4/16/2018, this syntax with two different field names passed as arguments to NOT EXISTS performed as optimized load
[FACT]:
LOAD TARGET_FIELD FROM [FACT.QVD] (qvd)
WHERE NOT EXISTS(FILTER, TARGET_FIELD);

//However, there have been many examples observed where the sole transformation was passing two different field names to NOT EXISTS and this deactivated the optimized load

There is something else at play here, something creating inconsistency in our end-user experience. (not sure if this could be impacted by things that happen in earlier parts of scripts? environmental issues?)

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

marcus_sommer — Tue, 17 Apr 2018 07:52:21 GMT

AFAIK applying a where exists clause with two parameters won't never result in an optimized load. At least I have never seen it whereby I didn't tried every possible combination and there might be something undocumented.

I think the important point will be if any data-processing on the row-level is necessary or not. This meant a renaming of a field for example is just applied to the xml-header on the column-level. Also on column-level happens the writing of the fieldvalues within the symboltables and only a new fieldvalue will be there stored - this meant there happens always an exists-check and therefore a where exists-clause will work.

But there are further dependencies for example if tables are concatenated both explicitly per statement and also by automatic concatenations if for instant statements like DISTINCT are included (which always effect all merging loadings): Turning Unoptimized Loads into Optimized Loads.

Beside this I could imagine that various settings like those in the easter egg like various caching-settings and also some configuration within the OS and/or VM might have side-effects.

- Marcus

Re: Optimized LOAD - EXISTS, but no NOT(EXISTS())?

evan_kurowski — Tue, 17 Apr 2018 23:12:56 GMT

Hello Marcus, those are good points identifying potential causes, originating from both syntax and external effects.

Checking for impacts from distinction, or multi-table contributions merging on a field makes sense. It's effects from cache, vm, or os that have me concerned. Sands could be shifting under the script's feet if those environmental settings weren't assured to remain static.

You always have great advice, thank you!