MadAdmin,
First of all, I updated attached scripts.
Made them smaller to narrow down the problem.
I agree with all your concerns.
Join on a function, TRIM, etc. - bad ideas.
The problem is.
My hands are tight.
The code is generated dynamically at runtime
from SP_EXEC_WORKFLOW recursive stor proc.
SQL is stored in a table TRANSFORM_DML in columns like
(WITH, SELECT,FROM,WHERE).
It is very hard to find out what exactly record needs to be changed.
This ETL will be discontinued in 4 months.
Management does not want to touch the code and
make modifications.
So, long story short.
Treat it as a black box and you can only
try to improve performance using indexes, RAM, CPU,
TempDB configuration, etc.
If nothing helps , this will be escalated
and if i get approval, i will change the code.