topic Join large tables in QlikView

Join large tables

Thu, 20 May 2010 15:37:51 GMT

Hi all,

I'm trying to join two large table from qvd files (each one has 40 millions of records) because I need to filter on two fields which are in the two different tables. Every time I try that QlikView starts loading the data, it waits a long time and at the end it simply says that it failed to load the data. I' running the software on a server with 4 cores and 10 GB of ram and during the loading I see the all the memory is used. When I try to run the same script in debug mode with a limited load of, for example, 10K records everything works well then there are no errors in the script. How can I solve this issue? Thanks a lot for your help.

Join large tables

Thu, 20 May 2010 17:12:47 GMT

Can you add more memory?

Join large tables

Thu, 20 May 2010 17:23:08 GMT

Yes I could, but I thought I could find another solution, maybe by changing some QlikView settings, by using some "hidden" optimizations in the script, before buying new hardware. 10 GB of ram is a huge quantity and I thought they were enought to manage my data. What is your experience with the ram consuming by QlikView? Is there another solution I can use to solve my issue? Thanks again.

Join large tables

boorgura — Thu, 20 May 2010 17:59:16 GMT

Is it because there are synthetic keys formed?

Try using a link table in that case.

Join large tables

johnw — Thu, 20 May 2010 18:41:09 GMT


b wrote:Yes I could, but I thought I could find another solution, maybe by changing some QlikView settings, by using some "hidden" optimizations in the script, before buying new hardware. 10 GB of ram is a huge quantity and I thought they were enought to manage my data. What is your experience with the ram consuming by QlikView? Is there another solution I can use to solve my issue? Thanks again.

First, how wide are the tables themselves? How compressible is the data? If each table held just 125 bytes of compressed information, that would require 10 GB of RAM right there. I suspect your tables would be smaller than that when compressed, but I don't know your data, so can't say for sure. Load each one separately. How much RAM is consumed by each separately? Add them up, and if it's over 10 GB, that's your problem. If that IS your problem, and you need all that data, then adding memory is the only solution.

But if, for instance, the first table takes 2 GB and the second takes 1 GB, then that isn't your problem. In that case, I suspect that something is going wrong with your join. For these two tables to only take 3 GB when joined, you would need to be doing a one to one join on a unique key shared by each table. If you're joining, say, ten different rows from table two to each row in table one, then you're going to run out of RAM. If you didn't intend to do a join like that, then changing it back to a 1:1 join should solve the problem. If you DO need to do a join like than, then adding memory is again the only solution.

Mind you, if it IS 1:1 with each table having the same unique ID, why are they in separate tables in the first place? (Not saying it's necessarily wrong - one of our source systems often has multiple actual tables all keyed by the same ID for various performance and memory reasons. I'll typically join them when building QVDs rather than when loading QVDs, but that doesn't solve your problem, just moves it to a different script.)

Join large tables

Fri, 21 May 2010 08:39:38 GMT

There are no synthetic keys.

Join large tables

Fri, 21 May 2010 09:31:01 GMT

Thanks for your answer!

If I load the two tables together without a join between them the present quantity of ram results to be enough. Here is the schema of my script:


qualify *;
unqualify A_field;
LOAD A_field, B_field FROM X_table.qvd (qvd);
join LOAD A_field, C_field FROM Y_table.qvd (qvd);

Then I expect the join is made on A_field. Is that right or am I making some mistakes? The tables come from an existing database which can't be changed. Thanks again for all your help.

Join large tables

Fri, 21 May 2010 15:34:34 GMT

Any other ideas welcomed! Thank you!

Join large tables

boorgura — Fri, 21 May 2010 15:37:21 GMT

Do you have a lot of number fields??

I have an idea from one of the Qliktech consultants to purge the trailing zeros after the decimal, which reduces atleast some of the memory consumption.

Join large tables

Fri, 21 May 2010 15:47:21 GMT

Thanks for your answer, but unfortunately the original data can't be modified. I have to find another solution.

Join large tables

boorgura — Fri, 21 May 2010 17:11:17 GMT

The original data need not be modified... But you can have them altered just before storing into QVDs.

If you have a situation where you should not even do that. Then this would not help you.

Join large tables

johnw — Fri, 21 May 2010 18:51:49 GMT


b wrote:qualify *;
unqualify A_field;
LOAD A_field, B_field FROM X_table.qvd (qvd);
join LOAD A_field, C_field FROM Y_table.qvd (qvd);

Is A_field a unique identifier on each table? Each value of A_field occurs only once on each table? Or can a value of A_field appear multiple times on each table? If multiple times, you're getting a many to many join which can vastly inflate the number of rows. Here's a quick example:

X_table:
A_field, B_field
A, B
A, C
B, D

Y_table:
A_field, C_field
A, X
A, Y
B, Z

Your join would produce this:

A_field, X_table.B_field, Y_table.C_field
A, B, X
A, B, Y
A, C, X
A, C, Y
B, D, Z

Join large tables

Mon, 24 May 2010 08:10:16 GMT

Thanks for your suggestion, but I can't do that because those QVDs are automatically created and need for other projects.

Join large tables

Wed, 26 May 2010 11:05:21 GMT

Thanks a lot for all your answers John.

I have just tried a left join (A_field occurs only once in Y_table) without any good result. Again, if I try a limited load (let's say 10000 rows) everything works well. Then is adding more RAM the only solution or can I try something else?