I have two tables like TB_01 and TB_02 and have 60 million records and I want to perform left join but the execution takes more than 3 hours.
60 millon records
3 million records
What is best practices to reduce the execution time and RAM Utilization.
Note: I have removed the duplicate records and unwanted fields.
Thanks for you response.
Have doubt on ApplyMap().
How can I achieve ApplyMap() in above scenario?Is it possible to achieve above scenario?
I expect the below results.
Note: I just rename the Field_03 column values from A to A01 but the actual value is A01.
How are you getting A01 in your third field? How are both table related any specific condition you are trying to use to convert value from A to A01?
ApplyMap('MapTB01', Field_01, null()) as Field_02
From TB_02 ;
Try it and tell me
It's not really clear what do you want to do. By a n:n relation between both tables you will by using a join always get duplicate records which is usually not wanted (whereby your output example showed a duplicate) and which could need a lot of performance. Further you will need some efforts to "clean" this result.
Therefore I would go with the suggestion from Youssef and using a mapping for it whereby I would probably aggregate the second table before with: concat(Field_03, '|'). These mapped results could be read with interrecord-functions like peek() or previous() and splitted with subfield() maybe within some if-loops to get the final data-structure. Even if this meant some efforts I would assume this approach a lot faster than joining and cleaning/preparing the results.
Beside this it might be worth to re-think the whole approach of merging these tables because there may better ways to create the datamodel.
I have similar issues, only that mine
ID and Start have unique values so i need to make the values of Start Mapped to a New field (End), provided that ID belongs to the same Name in the next Row
I will be glad if i can some help on this