Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
brindlogcool
Contributor III

Left Keep vs Where Exists

Hi,

I would like to know which is faster the Left keep or the where exists.

Tags (4)
6 Replies
bi4usupport
New Contributor

Re: Left Keep vs Where Exists

Hi brindlogcool, "Where Exists" is a little faster, because on the Left Keep you load all the Data and on the Where Exists you load only the data which you need.

Re: Left Keep vs Where Exists

Hi,

Based on what QlikView does with JOINs and KEEPs, if the data set is large enough (more than several hundreds of thousands), I'd stick to the WHERE EXISTS() not only because it will likely perform faster, but mainly because the KEEP might exhaust all available memory.

Of course, it depends not only on the number of rows but on your data as well, size of fields, distinct values... and on your hardware too.

Hope that helps.

Miguel

Re: Left Keep vs Where Exists

I have seen several cases where Left Keep is considerably faster. The reason is that a where Exists() need to perform the test (which could be a slow test, like a string comparison) on each record of the data. Whereas the Left Keep is a binary operation performed in one single step.

So I suggests you test with your own data...

HIC

Re: Left Keep vs Where Exists

I was replying but a quick test answered myself.

Anyway, I was assuming (and I was wrong) that KEEP works in a similar way JOIN does. I have tested JOINing two tables with 100K rows and 1M rows worth of data and I have exhausted 8Gb RAM (three fields, numeric values, autogenerated tables, just for testing).

Modifying the script to KEEP instead of JOIN took some time and memory, but finished quite fast, indeed. Way faster than the EXISTS() with the same script actually.

Thanks,

Miguel

brindlogcool
Contributor III

Re: Left Keep vs Where Exists

Thanks for all your responses, I will modifiy the Exisits with the Left keep and let you know the results

dirk_konings
Contributor III

Re: Left Keep vs Where Exists

Hi,

I have done these tests today with 11.20 SR5. Left keep is still much faster than a Where Exists when reading QVD's over 25million records.

Henric Cronström : maybe spend a Blog Post on this to put it in the picture again?


Dirk Konings