Speed up fetching data from interactions table of Mongo db

Question

We have a requirement in which we must get values from mongo db faster than the current scenario. We have few articles in our project and there is an option to fetch recent articles which are most popular ones. Popularity of articles are found by querying interaction table from mongo db and sorting articles based on the values. So more visit to an article increases its value and it becomes more popular.

We are using mongodb driver to connect to mongodb. Below is the code for getting interactions.

var query = MongoDB.Driver.Builders.Query.And(
    MongoDB.Driver.Builders.Query.GTE(
        Constants.AnalyticsInteractionsFields.StartDateTime, 
        DateTime.Now.AddDays(-dayscount)),
    MongoDB.Driver.Builders.Query.EQ(
        Constants.AnalyticsInteractionsFields.SiteName, 
        Sitecore.Context.Site.Name));

var interactions = driver.Interactions.FindAs<VisitData>(query);`

Since looking up in large table like Interaction table takes a considerable amount of time (around 30 -35 secs) we have to reduce the time to approximately around 3-5 secs.

We have decided to follow 2 approaches as described below.

1. Instead of querying mongodb, we can query Fact_PageViews table from SQL table and sort according to the values.
2. Write a scheduler which runs in every 20 mins to fetch the data from mongo db and host it as a service. Whenever request for most popular articles comes, call the service and get the latest set of articles from it.

Please suggest which is best approach or is there any other approach we can follow to speed up the process.

Answer 1

I usually use Fact_PageViews table from the reporting database (SQL), The only drawback is that table does not store item template or item path, so after you query the table you would need to filter the results based on your item template or the content path, Here is the SQL Query:

         SELECT TOP 100 ItemId, Sum(Views) AS VisitsCount FROM Fact_PageViews 
         WHERE [Date] > DATEADD(month, -1, GETDATE()) ) 
         GROUP BY ItemId ORDER BY VisitsCount DESC

I would also recommend that after you get your most popular pages, You cache the results for 1 hour or more, if it is not that valuable to have real-time accurate results for the popular pages.