topic Re: Left Keep vs Where Exists in QlikView

Left Keep vs Where Exists

brindlogcool — Tue, 18 Dec 2012 14:53:42 GMT

Hi,

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

Re: Left Keep vs Where Exists

bi4usupport — Tue, 18 Dec 2012 15:02:08 GMT

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

Miguel_Angel_Baeyens — Tue, 18 Dec 2012 15:03:09 GMT

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

hic — Tue, 18 Dec 2012 15:10:12 GMT

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

Miguel_Angel_Baeyens — Tue, 18 Dec 2012 15:33:20 GMT

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

Re: Left Keep vs Where Exists

brindlogcool — Tue, 18 Dec 2012 16:51:49 GMT

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

Re: Left Keep vs Where Exists

dirk_konings — Thu, 14 Aug 2014 12:45:34 GMT

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