The first index does look like a better fit for the query. Please provide the actual execution plans.
I would try using window functions instead of the four correlated subqueries. Or a single correlated subquery (with OUTER APPLY
) and see which of the two indexes is used.
Both ideas are to coerce the optimizer to use a single index scan to gather the rolling sums instead of 4 (that both your plans do).
It would also be worth checking and comparing the two execution plans, when asking for all the columns and when asking for only the columns in the index:
-- window functions
SELECT
-- m.*,
movie_title, start_date_time,
price, quantity,
SUM(CASE WHEN price = 0 THEN quantity ELSE 0 END)
OVER
(PARTITION BY movie_title
ORDER BY start_date_time
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS [Admissions Free],
SUM(CASE WHEN price > 0 THEN quantity ELSE 0 END)
OVER
(PARTITION BY movie_title
ORDER BY start_date_time
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS [Admissions Paid],
SUM(CASE WHEN price > 0 THEN quantity * price ELSE 0 END)
OVER
(PARTITION BY movie_title
ORDER BY start_date_time
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS [Admissions Revenue],
SUM(quantity)
OVER
(PARTITION BY movie_title
ORDER BY start_date_time
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS [Total Admissions]
FROM
[movies] AS m ;
using OUTER APPLY
:
-- using OUTER APPLY
SELECT
-- m.*,
m.movie_title, m.start_date_time,
m.price, m.quantity,
c.[Admissions Free],
c.[Admissions Paid],
c.[Admissions Revenue],
c.[Total Admissions]
FROM
[movies] AS m
OUTER APPLY
( SELECT
SUM(CASE WHEN price = 0 THEN quantity ELSE 0 END)
AS [Admissions Free],
SUM(CASE WHEN price > 0 THEN quantity ELSE 0 END)
AS [Admissions Paid],
SUM(CASE WHEN price > 0 THEN quantity * price ELSE 0 END)
AS [Admissions Revenue],
SUM(quantity)
AS [Total Admissions]
FROM [movies] AS i
WHERE i.movie_title = o.movie_title
AND i.start_date_time <= o.start_date_time
) AS c ;
This index may be a little better than the first one:
(
movie_title ASC,
start_date_time ASC
)
INCLUDE (price, quantity)