TRACE & Logging
Firstly add some trace commands so you can follow the progress of your script.
And enable the document log so you can see which parts of your script are taking time to execute.
Use Tabs
I would also suggest separating your load script into multiple tabs so there is one logical task or even one table per tab, to make testing simpler. Add a trace commands to identify the start of each tab as a minimum.
Form the Tab menu, you can use Insert Tab at Cursor to split your load into different tabs.
The load scripts executes the tabs in sequence from left to right, Tab 1 top to bottom, then tab 2....
Name your tables
I would also name each table you are loading rather than relying on the default name based on the FROM table as I find it more reliable to control the table names myself. Obviously this only applies to the topmost preceeding load.
TABLENAME:
load
...
Use EXIT SCRIPT for debugging.
Add a new tab "## Exit Script ##" which contains the following commands
TRACE ;
TRACE === EXIT SCRIPT === ;
exit script ;
This tab will exit the script cleanly and can be promoted and demoted between the tabs so you can control the data load rather than loading everything and seeing what works (or not!).
Demote the EXIT SCRIPT tab so you just load your mapping tables & first table load tab.
Run the script to load the first table, if it works, promote the EXIT SCRIPT tab, and load the next table.
If the load fails or you have synthetic keys, fix the issues before loading the next table and debugging will be much simpler.
I find moving an EXIT SCRIPT tab through the script tab by tab invaluable when testing.
Fix the errors early on and they do not propagate through the model.
Use FIRST N to limit data.
If a global Limited Load from debug does not load valid data for testing you can use a FIRST command to limit the rows from a large table for testing. This has to prefix each load you want to limit. The number is an integer with no thousand separator e.g. FIRST 10000
REMEMBER TO REMOVE ANY FIRST N COMMANDS WHEN TESTING IS FINISHED - USE SEARCH & REPLACE!
Avoid using Joins
I see you have some joins of tables that only have a few columns - I would not use a join but would use a mapping load even if it needs 5 or 6 mapping tables to get the result as this is faster than using joins.
For example this join could be done using 2 mapping tables Material_2_MUG_Desc & Material_2_Mat_Desc
LEFT JOIN(TEMP_CustomFLWithOutTPData)
LOAD Material,
[MUG Description] AS CFLMUGDescription,
[Material Description] AS CFLMaterialDescription
RESIDENt TABLE_PDM;
For more details see this post Don't join - use Applymap instead
Try using QVDs instead of RESIDENT load
It may be worth testing using QVDs instead of resident tables.
I have found in some cases it is faster to store a table to QVD, drop the original table and reload the QVD compared to using RESIDENT.
Finally read the Best Practices for Data Modelling that has been posted earlier in this thread.
