How to optimize SQL of select x divide by subquery with sum(y) with different table?

I have this query in T-SQL 2008:

SELECT a.Amount / (
    SELECT SUM(b.Amount) FROM Revenue b 
    WHERE YEAR(b.RevenueDate) = YEAR(a.ExpenseDate)
    AND MONTH(b.RevenueDate) = MONTH(a.ExpenseDate)
    AND b.HotelKey = a.HotelKey
)
FROM Expense a

The problem is it takes too long to finish the query. I think it's caused by the subquery "SELECT SUM(b.Amount) FROM Revenue b..." which is executed for each r开发者_运维技巧ow in table Expense.

How to optimize that kind of query? Is there any better alternative for the query?

EDIT: I'm sorry I forget the "AND b.HotelKey = a.HotelKey" clause in the subquery. The above original query has been updated.

@damien:

Here is your query added with HotelKey join:

SELECT
    a.Amount / b.Amount
FROM
    Expense a
    inner join
    (SELECT
        HotelKey,
        DATEADD(month,DATEDIFF(month,0,RevenueDate),0) as MonthStart,
        DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0) as MonthEnd,
        SUM(Amount) as Amount
     FROM
        Revenue
     GROUP BY
        HotelKey,
        DATEADD(month,DATEDIFF(month,0,RevenueDate),0),
        DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0)
    ) b 
    ON
        a.ExpenseDate >= b.MonthStart and
        a.ExpenseDate < b.MonthEnd 
        and a.HotelKey = b.HotelKey

---

Try to change the where clause in your inner query to this:

where b.RevenueDate >= dateadd(month, datediff(month, 0, a.ExpenseDate), 0) and
      b.RevenueDate < dateadd(month, 1+datediff(month, 0, a.ExpenseDate), 0)

It will give the query a chance to use an index on Revenue.RevenueDate if you have one.

---

If you're using a lot of the rows in Revenue to satisfy this query, you might do better to do a single subquery that computes all of the totals. (Also, using Mikael's suggestion for allowing some indexing to occur):

SELECT
    a.Amount / b.Amount
FROM
    Expense a
        inner join
    (SELECT
         DATEADD(month,DATEDIFF(month,0,RevenueDate),0) as MonthStart,
         DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0) as MonthEnd
         SUM(Amount) as Amount
     FROM
         Revenue
     GROUP BY
         DATEADD(month,DATEDIFF(month,0,RevenueDate),0),
         DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0)
    ) b 
         ON
              a.ExpenseDate >= b.MonthStart and
              a.ExpenseDate < b.MonthEnd

---

You don't specify how big the tables are. But you can get the query to be faster by creating a computed column (and indexing it) from year-month combination in table Revenue and in table Expense as well (if that table is not very small). So these computed columns (and the indexes) would be used in the query for joining the two tables.

See: Computed Columns

and: Creating Indexes on Computed Columns

---

You could try calculating the two sums first ant then joining the two together afterwards.

SELECT a.ExpenseAmount / b.RevenueAmount

FROM
(
    SELECT SUM(Expense.Amount) As ExpenseAmount,
    YEAR(Expense.ExpenseDate) AS ExpenseYear, 
    MONTH(Expense.ExpenseDate) AS ExpenseMonth
    GROUP BY 
    YEAR(Expense.RevenueDate), 
    MONTH(Expense.RevenueDate)
) AS a INNER JOIN 
(
    SELECT SUM(Revenue.Amount) AS RevenueAmount,
    YEAR(Revenue.RevenueDate) AS RevenueYear, 
    MONTH(Revenue.RevenueDate) AS RevenueMonth
    FROM Revenue
    GROUP BY YEAR(Revenue.RevenueDate), MONTH(Revenue.RevenueDate)
) as b ON a.ExpenseYear = b.RevenueYear AND a.ExpenseMonth = b.ExpenseMonth