optimize this SQL query

Question

I have this SQL query but its running soo slow,

SELECT 
    wr.wr_re_id as ReID, 
    sum(wr.wr_total) as PRTotal 
FROM 
    Workorder2 w
    INNER JOIN 
    Workorder_Row2 wr ON wr.wr_wo_id = w.wo_id
WHERE 
    (w.wo_type = 1 or w.wo_type = 2)
    AND wr.wr_row_type = 2
    AND w.wo_lo_id like '%'
    AND w.wo_date_time >= '2010-01-01' 
    AND w.wo_date_time <= '2010-01-31'
    AND wr.wr_wo_id IN
        (SELECT 
            wr2.wr_wo_id 
        FROM 
            Workorder_Row2 wr2 
            INNER JOIN Workorder2 w2 ON w2.wo_id = wr2.wr_wo_id 
            AND w2.wo_date_time >= '2010-01-01' 
            AND w2.wo_date_time <= '2010-01-31' 
        WHERE 
            wr2.wr_row_type = 1)
GROUP BY 
    wr.wr_re_id

any advice how I can speed it up? it takes almost 1min to execute.
I think the problem is with the AND wr.wr_wo_id IN (SELECT ... but i need this to know if there are product sales on the same workorder that contains a threatment.

Answer 1

If you are using MySQL, you can use EXPLAIN to get an idea why your query is slow. See http://dev.mysql.com/doc/refman/5.1/en/explain.html for more information about EXPLAIN syntax.

Answer 2

Firstly, have a look at your execution plan. It's hard for us to optimize it as we don't know your data.

The thing that jumps out the most is the wr.wr_wo_id IN (SELECT...) part. This would be far more efficient as a join, like this:

SELECT wr.wr_re_id as ReID, sum(wr.wr_total) as PRTotal 
FROM Workorder2 w
    INNER JOIN Workorder_Row2 wr on wr.wr_wo_id = w.wo_id
    INNER JOIN (
        SELECT DISTINCT wr2.wr_wo_id
        FROM Workorder_Row2 wr2 
            INNER JOIN Workorder2 w2 on w2.wo_id = wr2.wr_wo_id 
        WHERE w2.wo_date_time >= '2010-01-01' 
            AND w2.wo_date_time <= '2010-01-31'
            AND wr2.wr_row_type = 1
    ) T ON T.wr_wo_id = wr.wr_wo_id
WHERE (w.wo_type = 1 or w.wo_type = 2)
    AND wr.wr_row_type = 2
    AND w.wo_lo_id like '%'
    AND w.wo_date_time >= '2010-01-01' AND w.wo_date_time <= '2010-01-31'
GROUP BY wr.wr_re_id

It is worth considering whether it would help to add indices. It depends on how often you update/insert into/delete from those tables, and how selective each column is.

ADDITIONAL:

To do the reverse, i.e. to replace WHERE wr.wr_wo_id NOT IN (SELECT...), you would use:

SELECT wr.wr_re_id as ReID, sum(wr.wr_total) as PRTotal 
FROM Workorder2 w
    INNER JOIN Workorder_Row2 wr on wr.wr_wo_id = w.wo_id
    LEFT JOIN (
        SELECT DISTINCT wr2.wr_wo_id
        FROM Workorder_Row2 wr2 
            INNER JOIN Workorder2 w2 on w2.wo_id = wr2.wr_wo_id 
        WHERE w2.wo_date_time >= '2010-01-01' 
            AND w2.wo_date_time <= '2010-01-31'
            AND wr2.wr_row_type = 1
    ) T ON T.wr_wo_id = wr.wr_wo_id
WHERE T.wr2.wr_wo_id IS NULL
    AND (w.wo_type = 1 or w.wo_type = 2)
    AND wr.wr_row_type = 2
    AND w.wo_lo_id like '%'
    AND w.wo_date_time >= '2010-01-01' AND w.wo_date_time <= '2010-01-31'
GROUP BY wr.wr_re_id

However, it is more readable and (I would guess, though try it) more efficient to use:

SELECT wr.wr_re_id as ReID, sum(wr.wr_total) as PRTotal 
FROM Workorder2 w
    INNER JOIN Workorder_Row2 wr on wr.wr_wo_id = w.wo_id
WHERE (w.wo_type = 1 or w.wo_type = 2)
    AND wr.wr_row_type = 2
    AND w.wo_lo_id like '%'
    AND w.wo_date_time >= '2010-01-01' AND w.wo_date_time <= '2010-01-31'
    AND NOT EXISTS (
        SELECT NULL
        FROM Workorder_Row2 wr2 
            INNER JOIN Workorder2 w2 on w2.wo_id = wr2.wr_wo_id 
        WHERE w2.wo_date_time >= '2010-01-01' 
            AND w2.wo_date_time <= '2010-01-31'
            AND wr2.wr_row_type = 1
            AND wr2.wr_wo_id = wr.wr_wo_id
    )
GROUP BY wr.wr_re_id

Answer 3

Every SQL datbase engine has a query analyzer that will help you figure out why it's running slow. But in this case the solution is probably as simple as creating indexes on these columns:

• Workorder2.wo_id
• Workorder_Row2.wr_wo_id

Try adding these indexes as clustered indexes. If that doesn't work check the query analyzer for further ideas.

• In SQL Server this is found in the Management Studio by entering your query in the query window and clicking on the buttons "Display Estimated Execution Plan" and "Analyze Query in Database Tuning Advisor"
• In MySQL you can use the EXPLAIN command

Note: Don't worry about your "IN (SELECT ...)" section. It will be just as efficient as a join on any modern database engine.