I am loading one table (cca 600 000 000 rows, 20 columns)... It is going pretty quickly. But, I need a new column add to this, from another table. Another table have like 5 000 000 rows.
It is very slowly if I use left join, what can make it faster?
Personally I would use mapping like the others already suggested. It avoids all the risks which could occur with joins (adding or removing records depending on the join-type) and it will be surely faster as a join. Further it provides the possibility to define any default-value if there is no match and also to do further calculations at once - and not like by join which would require another load for any further checks/calculations. Also I would look if the mapping-table might not be reduced to a lesser number of records with some kind of advanced filtering. This may look like:
TableForExists: load article from ArticleRawdata where category = 'very important';
/* it's just an example - it may also some generic approach like the recent one from yours (this with the concatenating) with creating the dates from the current year */
ExtractMappingData: load lookup, return from SecondTable where exists(article);
Map: mapping load * resident ExtractMappingData;
drop tables ExtractMappingData, TableForExists;
It may look like expensive efforts but nothing worked better to optimize the load-performance by large datasets - unless you used incremental approaches. In your case with such large tables I would try to implement incremental loadings at first and if it's then further necessary to reduce the load-times the above mentioned approach could be also implemented on top.
