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.
Can't you add directly into the fact table for new column also?
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Have you looked at using ApplyMap? Here is an article by Henric Cronström explaining why it is better than joining if you just want to get a single value from a table.
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Rename Table Table1 to Temp; NoConcatenate Table1: LOAD column1, ApplyMap('Table2', column1, Null()) as column2 Resident Temp;
Drop Table Temp;
In your case you won't have to do the rename and drop as you'll already have the table. You can obviously use logic to only use ApplyMap if the original table didn't have a value, or if it was 0, etc.
Then you can have a preceding LOAD with a GROUP BY clause to contract the table into what you want. It doesnt matter whether the fields are dimensions or not in the load script. What you want to do is get rid of the null values and merge the rows that belong together....
But you might have to do a RESIDENT on the big concatenated table so I don't know if the performance or memory consumption will be good... ApplyMap might work better for you here. You'll have to give it a try.
