[Solved] SQL Statement with the same multiple subqueries running very slow
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

SQL Statement with the same multiple subqueries running very slow

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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `TABLE2` ADD INDEX `table2_idx_id` (`Id`);
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.