Basically what i am doing is concatenating 2 table then joining a 3rd table, then i try to load the final table (combination of the 3) but it keeps running forever and the memory just keeps climbing (i am working on a server with 64gig ram). So the first and second table is my transaction tables which i concatenate, then the 3rd table is a customer grouping table which i left join to the transactions. I do this so i can create a key on the transaction month and customer group in order to link budgets to the transactions. Hope this make sense.
Total lines for the concatenation - 42 000
Left join table contains 33mil loaded from a QVD
Currently memory usage is sitting on 14gig and climbing
See below my script. Not sure if i am doing anything wrong as when i debug the script it runs fine.
With a reduced load (100 records from the initial two tables, but 33mil from the 3rd one), try to get an idea of what is happening in the LEFT JOIN. Do you get a correct number of resultant rows, or does the initial table explode. Then try to predict the number of rows you would get if you combined 42000 rows with 33mil rows. Does it still match the expected outcome?
You may want to review the logic behind the LEFT JOIN, as it may attempt to perform a hugely expensive operation.