Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to know which is faster the Left keep or the 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.
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
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
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
Thanks for all your responses, I will modifiy the Exisits with the Left keep and let you know the results
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