SELECTING TOP X based on last 30 days of activity

Question

I have a table

CREATE TABLE [dbo].[UserMoviesActions](
    [Actionid] [int] IDENTITY(1,1) NOT NULL,
    [MovieId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
    [CreatedDateUtc] [datetime2](7) NOT NULL,
    [MovieActionType] [int] NOT NULL

This table will have a lot of activities for movies, such as watched, liked, loved, ignored, added to watch list etc (MovieActionType field)

I would like to create a high performing query/index view that will get top 10 movies with the most actions for the last 30 days. Below is my query

SELECT TOP 10
    m.Title,
    m.posterpath,
    m.movieid,
    COUNT(*) AS ActionCount
FROM
    [dbo].[UserMoviesActions] ma
INNER JOIN Movies m on m.MovieID = ma.MovieId
WHERE CreatedDateUtc > GETUTCDATE()-30
GROUP BY
    m.MovieId, title, m.posterpath
ORDER BY ActionCount DESC

Would this be the best approach and what indexes should I have on the tables.

Would columnstore index apply here? if so how (not a dba, apologies for noob questions)

EDIT: Filled the table with 2M records

This is the plan without any suggested index (either from JD or SQL Server engine)

https://www.brentozar.com/pastetheplan/?id=HJDqGyf_a

Plan with the non-cluster index on UserMoviesAction suggested by JD

CREATE NONCLUSTERED INDEX IX_UserMovieActions_MovieId_CreatedDateUtc ON dbo.UserMoviesActions (MovieId, CreatedDateUtc);

https://www.brentozar.com/pastetheplan/?id=rkkUmyGu6

Plan with both indexes suggested by JD

--columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Movies_MovieId_Title_PosterPath ON dbo.Movies (MovieId, Title, PosterPath);

--non-cluster SO
CREATE NONCLUSTERED INDEX IX_UserMovieActions_MovieId_CreatedDateUtc ON dbo.UserMoviesActions (MovieId, CreatedDateUtc);

https://www.brentozar.com/pastetheplan/?id=BkT3Q1zuT

Plan with index suggested by sql server engine after running the raw query (same as JD but it has movieId in the INCLUDE column vs suggested by JD, not sure what the difference is between the two

--non-cluster sql server suggested
CREATE NONCLUSTERED INDEX [IX_UserMovieActions_CreatedDateUtc_Include_MovieId]
ON [dbo].[UserMoviesActions] ([CreatedDateUtc])
INCLUDE ([MovieId])

https://www.brentozar.com/pastetheplan/?id=SJY8N1fO6

Answer 1

As Martin pointed out, Indexed Views have limitations that prevent them from being able to use TOP and nondeterministic functions.

You might be better served with a Columnstore Index instead to invoke batch mode for your query. An example Columnstore Index that may help here is:

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Movies_MovieId_Title_PosterPath ON dbo.Movies (MovieId, Title, PosterPath);

I would couple this with a regular rowstore index on (MovieId, CreatedDateUtc) in the UserMoviesActions table, like so:

CREATE NONCLUSTERED INDEX IX_UserMovieActions_MovieId_CreatedDateUtc ON dbo.UserMoviesActions (MovieId, CreatedDateUtc);

The thought process is, Columnstore Indexes are usually helpful with aggregative type queries (e.g. grouping and counting) because they can process operations in batch mode and compress the data down the columns included in the index. I can't say for sure if it'll work in your case without being able to test it with actual data though. But it's worth a try.

Answer 2

For a simple solution, and if the performance is acceptable, use just the nonclustered index suggested by SQL Server, to which maybe you can apply page compression. This index will store a copy of just the columns CreatedDataUtc and MovieId (plus the table clustering key, that you did not specify in your question), sorted by CreatedDateUtc. Thus, SQL Server will be able to rank the movies by seeking into this index and sequentially reading the last 30 days from it, without resorting to the underlying table.

CREATE NONCLUSTERED INDEX [IX_UserMovieActions_CreatedDateUtc_Include_MovieId]
ON [dbo].[UserMoviesActions] ([CreatedDateUtc])
INCLUDE ([MovieId])