Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brindlogcool
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.

6 Replies
bi4usupport
Partner - Contributor
Partner - Contributor

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.

Miguel_Angel_Baeyens

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

hic
Former Employee
Former Employee

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

Miguel_Angel_Baeyens

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

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

dirk_konings
Creator III
Creator III

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