Is there a way to get rid of the WITH clause (Common Table Expression)? Is this beneficial for performance?

Question

I have the following code:

WITH 
    SO_Req_ AS 
        (
        SELECT
            SO_REQ.SALESMAN AS Sales_Person, 
            SO_REQ.SALES_CAT AS Sales_Category, 
            SO_REQ.SO_REQ_ID AS SO_Req__ID, 
            SO_REQ.SO_REQ_NO AS SO_Req__No_, 
            SO_REQ.ORDER_PLACED_BY AS Order_Placed_By
        FROM
            ITIMS.SO_REQ SO_REQ
                LEFT OUTER JOIN ITIMS.APP_LINK APP_LINK
                ON SO_REQ.APP_LINK_ID = APP_LINK.APP_LINK_ID
                    FULL OUTER JOIN ITIMS.TIMS_APP TIMS_APP
                    ON TIMS_APP.TIMS_APP_ID = APP_LINK.SRC_TIMS_APP_ID
        ), 
    PROJECT__CR_ AS 
        (
        SELECT DISTINCT 
            PRJ.PROJECT_ID, 
            PRJ.PROJECT_CD, 
            PRJ.PROJECT_DESC, 
            'Y' AS ITIMS_PROJECT, 
            ACTIVE_TIMS_SITE_FLAG
        FROM
            ITIMS.PROJECT PRJ, 
            ITIMS.SITE_USER SU 
        WHERE 
            PRJ.PROJECT_ID = SU.PROJECT_ID
        ), 
    CR_Project AS 
        (
        SELECT
            PROJECT__CR_.PROJECT_CD AS Project_Code, 
            PROJECT__CR_.PROJECT_ID AS Project_ID
        FROM
            PROJECT__CR_
        ), 
    CR_OCTG_Hdr__Adjustment AS 
        (
        SELECT
            CR_ADJ.CR_ADJ_ID AS CR_Adjustment_ID, 
            CR_ADJ.TAX_FLAG AS Tax_Flag, 
            CR_ADJ.USD_RATE AS Credit_Rate__USD_, 
            CR_ADJ.UOM AS Quantity_UOM, 
            CR_ADJ.PROCESS_STATE AS CR_Adjustment_Status, 
            CR_ADJ.QTY AS Quantity, 
            CR_ADJ.ADJ_SIGN AS Adjustment_Sign, 
            CR_ADJ.COST_RATE AS Cost_Rate, 
            APP_LINK.SRC_REF_2 AS Source_Ref2
        FROM
            ITIMS.TIMS_APP TIMS_APP
                LEFT OUTER JOIN 
                ITIMS.APP_LINK APP_LINK
                    INNER JOIN ITIMS.CR_ADJ CR_ADJ
                    ON APP_LINK.APP_LINK_ID = CR_ADJ.APP_LINK_ID
                ON TIMS_APP.TIMS_APP_ID = APP_LINK.SRC_TIMS_APP_ID
                    LEFT OUTER JOIN ITIMS.UOM_CONV UOM_CONV__FT___CR_ADJ_
                    ON 
                        CR_ADJ.UOM = UOM_CONV__FT___CR_ADJ_.UOM_FROM AND
                        UOM_CONV__FT___CR_ADJ_.UOM_TO = 'FT'
                        LEFT OUTER JOIN ITIMS.UOM_CONV UOM_CONV__M___CR_ADJ_
                        ON 
                            CR_ADJ.UOM = UOM_CONV__M___CR_ADJ_.UOM_FROM AND
                            UOM_CONV__M___CR_ADJ_.UOM_TO = 'M' 
        WHERE 
            APP_LINK.SRC_TIMS_APP_ID = 743
        ), 
    CR_Req__Bill_To AS 
        (
        SELECT
            BIZENT.BIZENT_GRP_CD AS Bill_to_Bizent_Group_Code, 
            BIZENT.BIZENT_GRP_NAME AS Bill_to_Bizent_Group_Name, 
            BIZENT.BIZENT_CD AS Bill_to_Bizent_Code, 
            BIZENT.BIZENT_NAME AS Bill_to_Bizent_Name, 
            BIZENT.OUTSIDE_SALESMAN AS Outside_Salesman, 
            BIZENT.BIZENT_ID AS Bill_to_Bizent_ID
        FROM
            ITIMS.BIZENT_GRP BIZENT_GRP
                LEFT OUTER JOIN ITIMS.BIZENT BIZENT
                ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
        ), 
    CR_Req__Customer AS 
        (
        SELECT
            BIZENT.BIZENT_CD AS Customer_Bizent_Code, 
            BIZENT.BIZENT_NAME AS Customer_Bizent_Name, 
            BIZENT.BIZENT_ID AS Customer_Bizent_ID
        FROM
            ITIMS.BIZENT_GRP BIZENT_GRP
                LEFT OUTER JOIN ITIMS.BIZENT BIZENT
                ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
        )
    SELECT
        'CR' AS SRC,
        'ADJ - OCTG HDR' AS ITEM_CLASS,
        CR_Req_.PROJECT_ID,
        CR_Project.Project_Code, 
        CR_Req_.CR_ID, 
        CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID, 
        CR_Req__Bill_To.Bill_to_Bizent_Group_Code, 
        CR_Req__Bill_To.Bill_to_Bizent_Group_Name, 
        CR_Req__Customer.Customer_Bizent_Code, 
        CR_Req__Customer.Customer_Bizent_Name, 
        CR_Req__Bill_To.Bill_to_Bizent_Code, 
        CR_Req__Bill_To.Bill_to_Bizent_Name, 
        SO_Req_.Sales_Person, 
        SO_Req_.Sales_Category, 
        CR_Req_.CR_NO, 
        CR_Req_.DATE_CREATED, 
        CR_Req_.ACCT_PERIOD, 
        SO_Req_.SO_Req__ID, 
        SO_Req_.SO_Req__No_, 
        SO_Req_.Order_Placed_By, 
        CR_Req_.DISC_TAKEN_FLAG, 
        CR_Req_.AFE_NO, 
        CR_Req__Bill_To.Outside_Salesman, 
        CR_Req_.RETURN_DATE, 
        CR_OCTG_Hdr__Adjustment.Tax_Flag, 
        CR_OCTG_Hdr__Adjustment.Credit_Rate__USD_, 
        CR_OCTG_Hdr__Adjustment.Quantity_UOM, 
        CR_OCTG_Hdr__Adjustment.Quantity, 
        CR_OCTG_Hdr__Adjustment.Adjustment_Sign, 
        ifnull((CR_OCTG_Hdr__Adjustment.Cost_Rate * CR_OCTG_Hdr__Adjustment.Quantity) * CASE 
            WHEN CR_OCTG_Hdr__Adjustment.Adjustment_Sign LIKE 'A' THEN -1
            ELSE 1
        END, 0) COST_USD
    FROM
        SO_Req_
            LEFT OUTER JOIN ITIMS.INVC Invoice_Req_
            ON SO_Req_.SO_Req__ID = Invoice_Req_.SO_REQ_ID
                LEFT OUTER JOIN ITIMS.CR CR_Req_
                ON Invoice_Req_.INVC_ID = CR_Req_.INVC_ID
                    FULL OUTER JOIN CR_Project
                    ON CR_Project.Project_ID = CR_Req_.PROJECT_ID
                       LEFT OUTER JOIN ITIMS.CR_OCTG_HDR CR_OCTG_Hdr_
                        ON CR_Req_.CR_ID = CR_OCTG_Hdr_.CR_ID
                            LEFT OUTER JOIN CR_OCTG_Hdr__Adjustment
                            ON CR_OCTG_Hdr_.CR_OCTG_HDR_ID = CR_OCTG_Hdr__Adjustment.Source_Ref2
                                FULL OUTER JOIN CR_Req__Bill_To
                                ON CR_Req__Bill_To.Bill_to_Bizent_ID = CR_Req_.BILLTO_BIZENT_ID
                                    FULL OUTER JOIN CR_Req__Customer
                                    ON CR_Req__Customer.Customer_Bizent_ID = CR_Req_.CUST_BIZENT_ID 
    WHERE 
        NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID IS NULL ) AND
        NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_Status LIKE 'X' ) AND
        NOT ( CR_Req_.PROCESS_STATE LIKE 'X' )

The question here is if there's a way to get rid of all the CTEs (Common Table Expressions) created using the WITH clause and get the same results? Also, will it have some beneficial impact on the performance?

I've been trying to do this task but I have troubles when trying to unbox this CTEs.

Thanks a lot for the help, it is really appreciated!

Answer 1

So the simple answer is YES! The CTE provides two function chopping stuff up, and allow select to a Common Table as an Expression.

So

WITH cte_name AS (
    SELECT struff 
    FROM tables
)
SELECT stuff 
FROM tables
JOIN cte_name

is the same as:

SELECT stuff 
FROM tables
JOIN (
    SELECT struff 
    FROM tables
)

It's just a cut-n-paste.

now if you are using the CTE table multiple times, in most DB's that common sub-expression is evaluated only once. In Snowflake this can slow things down, if the Read IO is slower than the CPU required to do the work of the CTE, and two parallel reads might read less, thus run faster. As with all things test.

so to your code all your CTE's can be unfolded, and it will make zero difference for speed. And I would believe it will make your code more unreadable.

so starting with how I would mostly refactor your existing code:

WITH so_req_ AS (
    SELECT
        s.salesman AS sales_person, 
        s.sales_cat AS sales_category, 
        s.so_req_id AS so_req__id, 
        s.so_req_no AS so_req__no_, 
        s.order_placed_by AS order_placed_by
    FROM itims.so_req AS s
    LEFT OUTER JOIN itims.app_link as a
        ON s.app_link_id = a.app_link_id
    FULL OUTER JOIN itims.tims_app AS t
        ON t.tims_app_id = a.src_tims_app_id
        
), project__cr_ AS (
    SELECT DISTINCT 
        prj.project_id, 
        prj.project_cd, 
        prj.project_desc, 
        'Y' AS itims_project, 
        active_tims_site_flag
    FROM itims.project AS prj
    CROSS JOIN itims.site_user AS su 
    WHERE prj.project_id = su.project_id
            
), cr_project AS (
    SELECT
        p.project_cd AS project_code, 
        p.project_id AS project_id
    FROM project__cr_ AS p
    
), cr_octg_hdr__adjustment AS (
    SELECT
        c.cr_adj_id AS cr_adjustment_id, 
        c.tax_flag AS tax_flag, 
        c.usd_rate AS credit_rate__usd_, 
        c.uom AS quantity_uom, 
        c.process_state AS cr_adjustment_status, 
        c.qty AS quantity, 
        c.adj_sign AS adjustment_sign, 
        c.cost_rate AS cost_rate, 
        al.src_ref_2 AS source_ref2
    FROM itims.tims_app AS t
    LEFT OUTER JOIN itims.app_link AS al
    INNER JOIN itims.cr_adj AS c
        ON al.app_link_id = c.app_link_id
        ON t.tims_app_id = al.src_tims_app_id
    LEFT OUTER JOIN itims.uom_conv AS u1
        ON c.uom = u1.uom_from 
            AND u1.uom_to = 'FT'
    LEFT OUTER JOIN itims.uom_conv AS u2
        ON c.uom = u2.uom_from 
            AND u2.uom_to = 'M' 
    WHERE 
        al.src_tims_app_id = 743
        
), cr_req__bill_to AS (
    SELECT
        BIZENT.BIZENT_GRP_CD AS Bill_to_Bizent_Group_Code, 
        BIZENT.BIZENT_GRP_NAME AS Bill_to_Bizent_Group_Name, 
        BIZENT.BIZENT_CD AS Bill_to_Bizent_Code, 
        BIZENT.BIZENT_NAME AS Bill_to_Bizent_Name, 
        BIZENT.OUTSIDE_SALESMAN AS Outside_Salesman, 
        BIZENT.BIZENT_ID AS Bill_to_Bizent_ID
    FROM
        ITIMS.BIZENT_GRP BIZENT_GRP
            LEFT OUTER JOIN ITIMS.BIZENT BIZENT
            ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID

), cr_req__customer AS (
    SELECT
        BIZENT.BIZENT_CD AS Customer_Bizent_Code, 
        BIZENT.BIZENT_NAME AS Customer_Bizent_Name, 
        BIZENT.BIZENT_ID AS Customer_Bizent_ID
    FROM
        ITIMS.BIZENT_GRP BIZENT_GRP
            LEFT OUTER JOIN ITIMS.BIZENT BIZENT
            ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
            
)
SELECT
    'CR' AS SRC,
    'ADJ - OCTG HDR' AS ITEM_CLASS,
    CR_Req_.PROJECT_ID,
    CR_Project.Project_Code, 
    CR_Req_.CR_ID, 
    CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID, 
    CR_Req__Bill_To.Bill_to_Bizent_Group_Code, 
    CR_Req__Bill_To.Bill_to_Bizent_Group_Name, 
    CR_Req__Customer.Customer_Bizent_Code, 
    CR_Req__Customer.Customer_Bizent_Name, 
    CR_Req__Bill_To.Bill_to_Bizent_Code, 
    CR_Req__Bill_To.Bill_to_Bizent_Name, 
    s.Sales_Person, 
    s.Sales_Category, 
    CR_Req_.CR_NO, 
    CR_Req_.DATE_CREATED, 
    CR_Req_.ACCT_PERIOD, 
    s.SO_Req__ID, 
    s.SO_Req__No_, 
    s.Order_Placed_By, 
    CR_Req_.DISC_TAKEN_FLAG, 
    CR_Req_.AFE_NO, 
    CR_Req__Bill_To.Outside_Salesman, 
    CR_Req_.RETURN_DATE, 
    CR_OCTG_Hdr__Adjustment.Tax_Flag, 
    CR_OCTG_Hdr__Adjustment.Credit_Rate__USD_, 
    CR_OCTG_Hdr__Adjustment.Quantity_UOM, 
    CR_OCTG_Hdr__Adjustment.Quantity, 
    CR_OCTG_Hdr__Adjustment.Adjustment_Sign, 
    ifnull((CR_OCTG_Hdr__Adjustment.Cost_Rate * CR_OCTG_Hdr__Adjustment.Quantity) * CASE 
            WHEN CR_OCTG_Hdr__Adjustment.Adjustment_Sign LIKE 'A' THEN -1
            ELSE 1
        END, 0) COST_USD
FROM so_req_ AS s
LEFT OUTER JOIN itims.invc AS invoice_req_
    ON s.so_req__id = invoice_req_.so_req_id
LEFT OUTER JOIN itims.cr AS cr_req_
    ON Invoice_Req_.INVC_ID = CR_Req_.INVC_ID
FULL OUTER JOIN CR_Project
    ON CR_Project.Project_ID = CR_Req_.PROJECT_ID
LEFT OUTER JOIN ITIMS.CR_OCTG_HDR CR_OCTG_Hdr_
    ON CR_Req_.CR_ID = CR_OCTG_Hdr_.CR_ID
LEFT OUTER JOIN CR_OCTG_Hdr__Adjustment
    ON CR_OCTG_Hdr_.CR_OCTG_HDR_ID = CR_OCTG_Hdr__Adjustment.Source_Ref2
FULL OUTER JOIN CR_Req__Bill_To
    ON CR_Req__Bill_To.Bill_to_Bizent_ID = CR_Req_.BILLTO_BIZENT_ID
FULL OUTER JOIN CR_Req__Customer
    ON CR_Req__Customer.Customer_Bizent_ID = CR_Req_.CUST_BIZENT_ID 
WHERE 
    NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID IS NULL ) AND
    NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_Status LIKE 'X' ) AND
    NOT ( CR_Req_.PROCESS_STATE LIKE 'X' )

becomes (I had to alias a few more things):

SELECT
    'CR' AS SRC,
    'ADJ - OCTG HDR' AS ITEM_CLASS,
    CR_Req_.PROJECT_ID,
    cp.Project_Code, 
    CR_Req_.CR_ID, 
    cha.CR_Adjustment_ID, 
    cbt.Bill_to_Bizent_Group_Code, 
    cbt.Bill_to_Bizent_Group_Name, 
    rc.Customer_Bizent_Code, 
    rc.Customer_Bizent_Name, 
    cbt.Bill_to_Bizent_Code, 
    cbt.Bill_to_Bizent_Name, 
    s.Sales_Person, 
    s.Sales_Category, 
    CR_Req_.CR_NO, 
    CR_Req_.DATE_CREATED, 
    CR_Req_.ACCT_PERIOD, 
    s.SO_Req__ID, 
    s.SO_Req__No_, 
    s.Order_Placed_By, 
    CR_Req_.DISC_TAKEN_FLAG, 
    CR_Req_.AFE_NO, 
    cbt.Outside_Salesman, 
    CR_Req_.RETURN_DATE, 
    cha.Tax_Flag, 
    cha.Credit_Rate__USD_, 
    cha.Quantity_UOM, 
    cha.Quantity, 
    cha.Adjustment_Sign, 
    ifnull((cha.Cost_Rate * cha.Quantity) * CASE 
            WHEN cha.Adjustment_Sign LIKE 'A' THEN -1
            ELSE 1
        END, 0) COST_USD
FROM (
    SELECT
        s.salesman AS sales_person, 
        s.sales_cat AS sales_category, 
        s.so_req_id AS so_req__id, 
        s.so_req_no AS so_req__no_, 
        s.order_placed_by AS order_placed_by
    FROM itims.so_req AS s
    LEFT OUTER JOIN itims.app_link as a
        ON s.app_link_id = a.app_link_id
    FULL OUTER JOIN itims.tims_app AS t
        ON t.tims_app_id = a.src_tims_app_id
        
) AS s
LEFT OUTER JOIN itims.invc AS invoice_req_
    ON s.so_req__id = invoice_req_.so_req_id
LEFT OUTER JOIN itims.cr AS cr_req_
    ON Invoice_Req_.INVC_ID = CR_Req_.INVC_ID
FULL OUTER JOIN (
    SELECT
        p.project_cd AS project_code, 
        p.project_id AS project_id
    FROM (
        SELECT DISTINCT 
            prj.project_id, 
            prj.project_cd, 
            prj.project_desc, 
            'Y' AS itims_project, 
            active_tims_site_flag
        FROM itims.project AS prj
        CROSS JOIN itims.site_user AS su 
        WHERE prj.project_id = su.project_id            
    ) AS p  
) as cp
    ON cp.Project_ID = CR_Req_.PROJECT_ID
LEFT OUTER JOIN ITIMS.CR_OCTG_HDR CR_OCTG_Hdr_
    ON CR_Req_.CR_ID = CR_OCTG_Hdr_.CR_ID
LEFT OUTER JOIN (
        SELECT
            c.cr_adj_id AS cr_adjustment_id, 
            c.tax_flag AS tax_flag, 
            c.usd_rate AS credit_rate__usd_, 
            c.uom AS quantity_uom, 
            c.process_state AS cr_adjustment_status, 
            c.qty AS quantity, 
            c.adj_sign AS adjustment_sign, 
            c.cost_rate AS cost_rate, 
            al.src_ref_2 AS source_ref2
        FROM itims.tims_app AS t
        LEFT OUTER JOIN itims.app_link AS al
        INNER JOIN itims.cr_adj AS c
            ON al.app_link_id = c.app_link_id
            ON t.tims_app_id = al.src_tims_app_id
        LEFT OUTER JOIN itims.uom_conv AS u1
            ON c.uom = u1.uom_from 
                AND u1.uom_to = 'FT'
        LEFT OUTER JOIN itims.uom_conv AS u2
            ON c.uom = u2.uom_from 
                AND u2.uom_to = 'M' 
        WHERE 
            al.src_tims_app_id = 743
            
    ) as cha
    ON CR_OCTG_Hdr_.CR_OCTG_HDR_ID = cha.Source_Ref2
FULL OUTER JOIN (
        SELECT
            BIZENT.BIZENT_GRP_CD AS Bill_to_Bizent_Group_Code, 
            BIZENT.BIZENT_GRP_NAME AS Bill_to_Bizent_Group_Name, 
            BIZENT.BIZENT_CD AS Bill_to_Bizent_Code, 
            BIZENT.BIZENT_NAME AS Bill_to_Bizent_Name, 
            BIZENT.OUTSIDE_SALESMAN AS Outside_Salesman, 
            BIZENT.BIZENT_ID AS Bill_to_Bizent_ID
        FROM
            ITIMS.BIZENT_GRP BIZENT_GRP
                LEFT OUTER JOIN ITIMS.BIZENT BIZENT
                ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID

    ) as cbt
    ON cbt.Bill_to_Bizent_ID = CR_Req_.BILLTO_BIZENT_ID
FULL OUTER JOIN (
        SELECT
            BIZENT.BIZENT_CD AS Customer_Bizent_Code, 
            BIZENT.BIZENT_NAME AS Customer_Bizent_Name, 
            BIZENT.BIZENT_ID AS Customer_Bizent_ID
        FROM
            ITIMS.BIZENT_GRP BIZENT_GRP
                LEFT OUTER JOIN ITIMS.BIZENT BIZENT
                ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
                
    ) as rc 
    ON rc.Customer_Bizent_ID = CR_Req_.CUST_BIZENT_ID 
WHERE 
    NOT ( cha.CR_Adjustment_ID IS NULL ) AND
    NOT ( cha.CR_Adjustment_Status LIKE 'X' ) AND
    NOT ( CR_Req_.PROCESS_STATE LIKE 'X' )

which is a lot less readable. And will have the exact same performance.