Large MultiMap+Reduce or Denormalize Count fields

Question

Just wondering your opinion on how I should model my documents for this scenario.

At the moment I have what seems a complex MultiMap index which is pulling in counters / stats from several other document collections, on my dev machine it's returning a small subset of test data in under 80ms (which i'm happy with).

What is the performance going to be like when this goes on my production server, on average each mix will receive around 500 plays per week, 200 downloads per week and a handful of likes, favourites and comments. I will be displaying 20-25 mixes per page.

Would you keep this design, or would it be better to denormalize my counters and store them on the Audio document, using the index would be alot less work as long as it will perform ok?

public class AudioWithCounters : AbstractMultiMapIndexCreationTask<AudioWithCounters.AudioViewModel>
    {
        public class AudioViewModel
        {
            public string Id { get; set; }
            public string ArtistName { get; set; }
            public string Name { get; set; }
            public int TotalComments { get; set; }
            public int TotalDownloads { get; set; }
            public int TotalPlays { get; set; }
            public int TotalLikes { get; set; }
            public int TotalFavourites { get; set; }
            public int WeeksComments { get; set; }
            public int WeeksDownloads { get; set; }
            public int WeeksPlays { get; set; }
            public int WeeksLikes { get; set; }
            public int WeeksFavourites { get; set; }
        }

        public AudioWithCounters()
        {
            AddMap<Audio>(audios => from audio in audios
                                    select new
                                    {
                                        Id = audio.Id,
                                        ArtistName = audio.ArtistName,
                                        Name = audio.Name,
                                        TotalDownloads = 0,
                                        TotalComments = audio.CommentsCount,
                                        TotalPlays = 0,
                                        TotalLikes = 0,
                                        TotalFavourites = 0,
                                        WeeksDownloads = 0,
                                        WeeksPlays = 0,
                                        WeeksComments = 0,
                                        WeeksLikes = 0,
                                        WeeksFavourites = 0
                                    });

            AddMap<AudioComments>(comments => from audioComment in comments
                                              from comment in audioComment.Comments
                                              where comment.CreatedAt >= DateTimeOffset.Now.AddDays(-7)
                                    select new
                                    {
                                        Id = audioComment.Audio.Id,
                                        ArtistName = (string)null,
                                        Name = (string)null,
                                        TotalDownloads = 0,
                                        TotalComments = 0,
                                        TotalPlays = 0,
                                        TotalLikes = 0,
                                        TotalFavourites = 0,
                                        WeeksDownloads = 0,
                                        WeeksPlays = 0,
                                        WeeksComments = 1,
                                        WeeksLikes = 0,
                                        WeeksFavourites = 0
                                    });


            AddMap<AudioCounter>(counters => from counter in counters
                                             where counter.Type == Core.Enums.Audio.AudioCounterType.Download
                                    select new
                                    {
                                        Id = counter.AudioId,
                                        ArtistName = (string)null,
                                        Name = (string)null,
                                        TotalDownloads = 1,
                                        TotalComments = 0,
                                        TotalPlays = 0,
                                        TotalLikes = 0,
                                        TotalFavourites = 0,
                                        WeeksDownloads = 0,
                                        WeeksPlays = 0,
                                        WeeksComments = 0,
                                        WeeksLikes = 0,
                                        WeeksFavourites = 0
                                    });

            AddMap<AudioCounter>(counters => from counter in counters
                                             where counter.Type == Core.Enums.Audio.AudioCounterType.Play
                                             select new
                                             {
                                                 Id = counter.AudioId,
                                                 ArtistName = (string)null,
                                                 Name = (string)null,
                                                 TotalDownloads = 0,
                                                 TotalPlays = 1,
                                                 TotalComments = 0,
                                                 TotalLikes = 0,
                                                 TotalFavourites = 0,
                                                 WeeksDownloads = 0,
                                                 WeeksPlays = 0,
                                                 WeeksComments = 0,
                                                 WeeksLikes = 0,
                                                 WeeksFavourites = 0
                                             });

            AddMap<AudioCounter>(counters => from counter in counters
                                             where counter.Type == Core.Enums.Audio.AudioCounterType.Download
                                             where counter.DateTime >= DateTimeOffset.Now.AddDays(-7)
                                             select new
                                             {
                                                 Id = counter.AudioId,
                                                 ArtistName = (string)null,
                                                 Name = (string)null,
                                                 TotalDownloads = 0,
                                                 TotalPlays = 0,
                                                 TotalComments = 0,
                                                 TotalLikes = 0,
                                                 TotalFavourites = 0,
                                                 WeeksDownloads = 1,
                                                 WeeksPlays = 0,
                                                 WeeksComments = 0,
                                                 WeeksLikes = 0,
                                                 WeeksFavourites = 0
                                             });

            AddMap<Like>(likes => from like in likes
                                             select new
                                             {
                                                 Id = like.AudioId,
                                                 ArtistName = (string)null,
                                                 Name = (string)null,
                                                 TotalDownloads = 0,
                                                 TotalPlays = 0,
                                                 TotalComments = 0,
                                                 TotalLikes = 1,
                                                 TotalFavourites = 0,
                                                 WeeksDownloads = 0,
                                                 WeeksPlays = 0,
                                                 WeeksComments = 0,
                                                 WeeksLikes = 0,
                                                 WeeksFavourites = 0
                                             });

            AddMap<Favourite>(favs => from fav in favs
                                  select new
                                  {
                                      Id = fav.AudioId,
                                      ArtistName = (string)null,
                                      Name = (string)null,
                                      TotalDownloads = 0,
                                      TotalPlays = 0,
                                      TotalComments = 0,
                                      TotalLikes = 0,
                                      TotalFavourites = 1,
                                      WeeksDownloads = 0,
                                      WeeksPlays = 0,
                                      WeeksComments = 0,
                                      WeeksLikes = 0,
                                      WeeksFavourites = 0
                                  });

            AddMap<AudioCounter>(counters => from counter in counters
                                             where counter.Type == Core.Enums.Audio.AudioCounterType.Play
                                             where counter.DateTime >= DateTimeOffset.Now.AddDays(-7)
                                             select new
                                             {
                                                 Id = counter.AudioId,
                                                 ArtistName = (string)null,
                                                 Name = (string)null,
                                                 TotalDownloads = 0,
                                                 TotalPlays = 0,
                                                 TotalComments = 0,
                                                 TotalLikes = 0,
                                                 TotalFavourites = 0,
                                                 WeeksDownloads = 1,
                                                 WeeksPlays = 0,
                                                 WeeksComments = 0,
                                                 WeeksLikes = 0,
                                                 WeeksFavourites = 0
                                             });

            AddMap<Like>(likes => from like in likes
                                  where like.DateCreated >= DateTimeOffset.Now.AddDays(-7)
                                  select new
                                  {
                                      Id = like.AudioId,
                                      ArtistName = (string)null,
                                      Name = (string)null,
                                      TotalDownloads = 0,
                                      TotalPlays = 0,
                                      TotalComments = 0,
                                      TotalLikes = 0,
                                      TotalFavourites = 0,
                                      WeeksDownloads = 0,
                                      WeeksPlays = 0,
                                      WeeksComments = 0,
                                      WeeksLikes = 1,
                                      WeeksFavourites = 0
                                  });

            AddMap<Favourite>(favs => from fav in favs
                                      where fav.DateCreated >= DateTimeOffset.Now.AddDays(-7)
                                      select new
                                      {
                                          Id = fav.AudioId,
                                          ArtistName = (string)null,
                                          Name = (string)null,
                                          TotalDownloads = 0,
                                          TotalPlays = 0,
                                          TotalComments = 0,
                                          TotalLikes = 0,
                                          TotalFavourites = 0,
                                          WeeksDownloads = 0,
                                          WeeksPlays = 0,
                                          WeeksComments = 0,
                                          WeeksLikes = 0,
                                          WeeksFavourites = 1
                                      });

            Reduce = results => from result in results
                                group result by result.Id
                                    into g
                                    select new
                                    {
                                        Id = g.Key,
                                        ArtistName = g.Select(x => x.ArtistName).Where(x => x != null).FirstOrDefault(),
                                        Name = g.Select(x => x.Name).Where(x => x != null).FirstOrDefault(),
                                        TotalDownloads = g.Sum(x => x.TotalDownloads),
                                        TotalPlays = g.Sum(x => x.TotalPlays),
                                        TotalComments = g.Sum(x => x.TotalComments),
                                        TotalLikes = g.Sum(x => x.TotalLikes),
                                        TotalFavourites = g.Sum(x => x.TotalFavourites),
                                        WeeksComments = g.Sum(x => x.WeeksComments),
                                        WeeksDownloads = g.Sum(x => x.WeeksDownloads),
                                        WeeksPlays = g.Sum(x => x.WeeksPlays),
                                        WeeksLikes = g.Sum(x => x.WeeksLikes),
                                        WeeksFavourites = g.Sum(x => x.WeeksFavourites)
                                    };
        }

Answer 1

You model is good, stick with it. You won't notice any performance issues at query time, because queries will be run against the precomputed index. So all the work is done asynchronously in a background thread on the server - that makes your queries fast.

Also, you don't need to worry about performance in the index execution, because the Map functions will only be run on new or changed documents and the Reduce function is executed in groups, thus only computing new map results.

Assuming that it is a website we're talking about, your alternative suggestion to denormalize the count into the audio documents would put you intro trouble, because then you would need to load and update the audio document each time somebody clicks on download or play. Of course you wouldn't notice that on a small site, but if you have some simultaneous visitors this will become a problem. In addition, it is much easier to scale out using the Map/Reduce approach and the AudioCounter documents, because then you need to worry less about concurrency and replication - instead, when someone downloads or plays a title, just put in a new AudioCounter document and go on.

One thing that you should be aware of though, are the week-counters. If my assumptions about what they are intended for are true, then they won't work the way you have them now. The problem is, that you cannot have a "range aggregation" (don't know the correct word) inside a map/reduce index - it's always about totals. To do that, you can come up with a facet query that counts the number of records or instead use the index replication bundle to populate a SQL table on which you can do ad-hoc queries. I'm sorry, I don't find a good example for the facet approach right now, maybe I'll put together one on the weekend...