Joining 2 Large Tables

[johnhorneramllp]

I have 2 tables (actually more but 2 will do for this example) that i wish to combine / Outer Join. Each table has about 20 attribute columns (the same in both tables) and 1 Metric each. Each table contains about 6 million rows of data. I wish to have one table that has the 20 attribute columns and 2 Metrics. Now i know that i can do a Join (left, right, Inner, outer) or a Concatenate and indeed either option is working for me, however it appears to be extremely slow in joining the tables and also appears to mess up the optimized qvd load speed.

Any thoughts on how else this could be done for best performance would be most welcome.

Regards

JH

[rwunderlich]

How about CONCATENATE instead of JOIN?

-Rob

[johnhorneramllp]

Yes i have actually used Concatenate but even with concatenate i am not getting the qvd optimization loading from my qvd files. Also i actually have about 10 tables to "merge" and this is leading to a 45 million row table which I subsequently need to add a calculated field to. Add to that the fact that i then need to create a moving 12 month average summary table from this table and my dataload is getting to be very time consuming.

John H

[#4]

We are doing similar things -- concatenating 9 tables (ranging from 500k rows to 200 million rows each) -- with five common fields but also 8 fields which pertain to one set of tables or the other (inventory or sales).

Concatenation should work and you should have an optimized load unless you are renaming the fields or doing a calculation during the load. However, I've noticed that as soon as you concatenate the first 'different' table (that has different fields than the tables already loaded into memory), QlikView sits there for a minute or two.

I don't know if there is a strategy here on which set of tables to load first, or whether to load an initial table w/ 1 row of nulls but that has all the fields?

For your moving average table, are you trying to do that in the QlikView load script or in the front end? You could use a Group By statement where the data loaded (resident the big table you just loaded into memory) is within the last 12 months.

You could also consider creating the moving average table in the front end using a chart object and then exporting the table to a QVD.