SQL query takes 10+ minutes to run, split in two it takes 26 seconds

Question

I am writing a SQL query to pull manufacturing data and figure out the cost per month compared to output. The query structure is massive (200+ lines over 4 queries). It takes over 5 minutes to run. I don't think I ever actually let it run completely.

The production cost part takes 13 seconds to run. I could not understand how it would take so long to run the rest of the report. So dumped the production cost into a table and ran the sales report using the table. Voila! Now the sales report takes 13 seconds!

Now how can I make my query only take 26 seconds to run?

Here is the line in the sales report query that pulls in the production cost. [Production Cost Per Job] is the production cost query.

OUTER APPLY (
    SELECT TOP 1
        bpc.StartingMonth,  
        bol.[Item No_] as ItemNo, 
        SUM(bpc.Amount) / SUM(bpc.Quantity) as Cost
    FROM "Production Output Line" as bol
    INNER JOIN [Production Cost Per Job] as bpc
        ON bol.[JobID] = bpc.[JobID]
        AND bol."Output Item Size" = bpc.ItemSize
        AND DATEDIFF(mm, 0, bol.[Starting Date]) = bpc.StartingMonth
    WHERE bpc.Quantity <> 0 
    AND bpc.StartingMonth <= a.MonthDate
    AND bpc.StartingMonth >= 1380 -- January 2015
    AND bol.[Item No_] = a.ItemNo
    GROUP BY bpc.StartingMonth, bol.[Item No_]
    ORDER BY bpc.StartingMonth
) as b 

Answer 1

Based on your logic, I think something like the following would work quite effectively (I don't know what you have in a so I've just created a.a_rows as well as the MonthDate field):

SELECT
    a_rows
    , MonthDate
    , StartingMonth
    , ItemNo
    , Cost
FROM
    (
        SELECT
            a_rows
            , MonthDate
            , StartingMonth
            , ItemNo
            , Cost
            , ROW_NUMBER() OVER (PARTITION BY a_rows, MonthDate ORDER BY StartingMonth) R
        FROM
            (
                SELECT
                    a.a_rows
                    , a.MonthDate
                    , bpc.StartingMonth
                    , bol.[Item No_] ItemNo
                    , SUM(bpc.Amount) / SUM(bpc.Quantity) Cost
                FROM
                    [a] a
                    LEFT JOIN [Production Output Line] bol ON bol.[Item No_] = a.ItemNo
                    LEFT JOIN [Production Cost Per Job] bpc ON
                        bpc.StartingMonth <= a.MonthDate
                        AND bpc.StartingMonth >= 1380
                        AND bpc.Quantity <> 0
                GROUP BY
                    a.a_rows
                    , a.MonthDate
                    , bpc.StartingMonth
                    , bol.[Item No_]
            ) Q
    ) Q2
WHERE R = 1