3

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.

  • Have you considered using sitecore analytics search index? It should give you much better performance. – grg Nov 23 '17 at 9:07
  • @grg While trying to rebuild analytics search index, its not getting rebuild. Items added is coming as 0. What could be the possible fix for that? – Anjo Dec 1 '17 at 9:54
  • You should ask a new question regarding the rebuild and provide appropriate details. – grg Dec 7 '17 at 14:03
  • A recommended starting point would be to review the Explain Results for this query in MongoDB to make sure it is efficiently supported by indexes. If you need help optimising a query, I would suggest posting on DBA StackExchange to reach a wider audience with MongoDB expertise. This blog post from one of the MongoDB driver developers also provides a great intro: Optimizing MongoDB Compound Indexes. – Stennie Mar 30 '18 at 21:51
2

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.

  • This approach has a problem. In CD server the reporting db will be disabled as per Sitecore recommendation . So only in CM servers I will be able to get the reporting db. – Anjo Dec 1 '17 at 9:53
  • @Anjo You can keep the reporting db connection string on CD, it will not affect anything else – Ahmed Okour Dec 1 '17 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.