Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator 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
Highlighted
Partner
Partner

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.

Highlighted

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

Highlighted

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

Highlighted

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

Highlighted
Creator III
Creator 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

Highlighted
Creator III
Creator 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