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

[datanibbler]

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

[marcus_sommer]

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

[rwunderlich]

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

[datanibbler]

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

[PrashantSangle]

You can handle it while creating qvd.

Use preceding load in extractor.

Regards,

[datanibbler]

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

[olivierrobin]

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

[marcus_sommer]

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

[datanibbler]

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.

[datanibbler]

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 ...