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.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `TABLE2` ADD INDEX `table2_idx_id` (`Id`);
SELECT
tbl1.ID,
tbl1.A,
tbl1.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,
tbl1.f,
tbl1.g,
tbl1.h,
tbl1.i
FROM
TABLE1 tbl1