SQL Statement with the same multiple subqueries running very slow-CodePudding

I am hoping someone here can help me. I have been given this query that takes a long time to run (over 30 min). I have been trying to rewrite the query but with no luck thus far. I am posting an example of the query so that you can get an idea of what it is I am trying to do.

Table1 has 2000 records. Table2 has 2000000 records.

Table2 may not contain the record it is trying to lookup. Therefore a CROSS APPLY or INNER JOIN cannot be used. I think this can be solved with a LEFT JOIN, but I have not been able to figure out a way to re-write it.

SELECT
    ID, A, B,

    CASE WHEN c IS NULL then
        (SELECT max(distinct c) FROM TABLE2 tbl2 WHERE tbl1.Id = tbl2.Id)
    ELSE
        C
    END as C,

    CASE WHEN d IS NULL then
        (SELECT max(distinct d) FROM TABLE2 tbl2 WHERE tbl1.Id = tbl2.Id)
    ELSE
        D
    END as D,

    CASE WHEN e IS NULL then
        (SELECT max(distinct e) FROM TABLE2 tbl2 WHERE tbl1.Id = tbl2.Id)
    ELSE
        e
    END as e,
    f, g, h, i
FROM TABLE1 tbl1

Instead of running the 'select max' query 3 times, is there a way to join the table just once so that the "same" query is not run 3 times? The reason for the Max is that Table2 may contain several records with the same Id. In this case think of Id as an Order_Id with multiple order lines numbers. Hope it makes sense.

CodePudding user response：

It would seem your query should boil down to the following, does this work for you and perform better?

select
    t1.ID, t1.A, t1.B,
    IsNull(t1.c, t2.c) C,
    IsNull(t1.d, t2.d) D,
    IsNull(t1.e, t2.e) E,
    t1.f, t1.g, t1.h, t1.i
from TABLE1 t1
outer apply (
    select Max(c) c, Max(d) d, Max(e) e
    from TABLE2 t2
    where t2.Id = t1.Id
)t2