• 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.