How can I optimize this actionresult for better performance? I need put put a timer on when to "GET" XML data from a url and etc

Question

I have a actionresult that I think is pretty heavy, so I wonder how can I optimize it so it gets better performance. This web application will be used for by +100, 000 users at same time.

Right now my Actionresult does the following things:

• Retrieve XML file from a internet url
• Fills the xml data to my DB
• DB data fills my Viewmodel
• Returns the model to the view

This 4 functions triggers everytime a user visits the view. This is why I think this Actionresult is very badly made by me.

How can I add this following things to my Actionresults?

add a timer to retrieve XML file and fill xml data to DB, like every 10 minute, so it doesnt trigger everytime a user visits the view. The only function that needs to trigger everytime a user visits the site is the viewmodel binding and returning the model. How can I accomplish this?

Note:

• the xml file gets updated with new data every 10 min or so.
• I have around 50 actionresults that does the same get xml data and adds to database but 50 different xml files.
• If the xml URL is offline it should skip the whole xml retrieve and DB add and just do the modelbinding

This is my actionresult:

public ActionResult Index()
        {
            //Get data from xml url (This is the code that shuld not run everytime a user visits the view)
            var url = "http://www.interneturl.com/file.xml";
            XNamespace dcM = "http://search.yahoo.com/mrss/";
            var xdoc = XDocument.Load(url);
            var items = xdoc.Descendants("item")
            .Select(item => new
            {
                Title = item.Element("title").Value,
                Description = item.Element("description").Value,
                Link = item.Element("link").Value,
                PubDate = item.Element("pubDate").Value, 
                MyImage = (string)item.Elements(dcM + "thumbnail")
               .Where(i => i.Attribute("width").Value == "144" && i.Attribute("height").Value == "81")
               .Select(i => i.Attribute("url").Value)
               .SingleOrDefault()
            })
            .ToList();

            //Fill my db entities with the xml data(This is the code that shuld not run everytime a user visits the view)
            foreach (var item in items)
            {
                var date = DateTime.Parse(item.PubDate);
                if (!item.Title.Contains(":") && !(date <= DateTime.Now.AddDays(-1)))
                    {
                        News NewsItem = new News();
                        Category Category = new Category();
                        var CategoryID = 2;

                        var WorldCategoryID = re.GetByCategoryID(CategoryID);
                        NewsItem.Category = WorldCategoryID;

                        NewsItem.Description = item.Description;
                        NewsItem.Title = item.Title.Replace("'", "");
                        NewsItem.Image = item.MyImage;

                        NewsItem.Link = item.Link;
                        NewsItem.Date = DateTime.Parse(item.PubDate);
                        re.AddNews(NewsItem);
                        re.save();
                    }
                }


            //All code below this commenting needs to run everytime a user visits the view
            var GetAllItems = re.GetAllWorldNewsByID();

            foreach (var newsitemz in GetAllItems)
            {
                if (newsitemz.Date <= DateTime.Now.AddDays(-1))
                {
                    re.DeleteNews(newsitemz);
                    re.save();
                }

            }

            var model = new ItemViewModel()
            {
               NewsList = new List<NewsViewModel>()
            };

            foreach (var NewsItems in GetAllItems)
            {
                FillProductToModel(model, NewsItems);
            }

            return View(model);
        }

Right now everytime a user visits the index view, it will get XML data and add it to the DB, so the bad fix Ive done in my repository is following on addNews:

 public void AddNews(News news)
        {
            var exists = db.News.Any(x => x.Title == news.Title);

             if (exists == false)
            {
                db.News.AddObject(news);
            }
            else
            {
                db.News.DeleteObject(news);
            }
        }

Any kind of solution and info is highly appreciated!

Answer 1

There are great many things that could be done here: does the file have to be XML (very verbose compared to JSON)? Does it have to be saved to the DB every time?

However, assuming that you have to do every step you have two bottlenecks:

1. Waiting for the XML file to download/parse
2. Saving all the XML data to the DB

There are a couple of ways you can speed this up:

Set up a polling interval

If you're happy not seeing updates immediately then you can do something like this:

• Check the DB for the last update.
• If (and only if) the last update is more than 10 mins old:
  • Retrieve XML file from a internet url
  • Fills the xml data to my DB
• DB data fills my Viewmodel
• Returns the model to the view

This means that your data may be up to 10 mins out of date, but the vast majority of requests will only have to populate the model.

Depending how you're using this you could make this even simpler - just add an OutputCache attribute:

[OutputCache(Duration=600)]
public ActionResult Index() { ...

This will tell the browser to only refresh every 10 mins. You can also set the Location attribute to make that just cached by the browser or on the server for everyone.

Make the XML retrieval async

During the download of the XML file your code is basically just waiting for the URL to be loaded - using the new async keyword in C# you don't need to wait here.

public async Task<ActionResult> Index()
{
    // Get data from xml url
    string url = "http://www.interneturl.com/file.xml";
    XNamespace dcM = "http://search.yahoo.com/mrss/";

    // The await keyword tells the C# code to continue until the slow action completes
    var xdoc = await LoadRemoteXmlAsync(url, dcM);

    // This won't fire until LoadRemoteXmlAsync has finished
    var items = xdoc.Descendants("item")

There's a lot more to using async than I can practically cover here, but if you're on the lastest C# and MVC it could be fairly simple to start using it.

Make only 1 DB call

Your current DB save action is very sub-optimal:

• Your code suffers from something commonly called the N+1 problem.
• Each time you add you're first checking the title and deleting the record. This is a very slow way to do the update and will make if very difficult to use any indexes to optimise it.
• You're looping through all of your news articles every time and deleting all the old ones one by one. That's much slower than an single delete from News where ... query.

Based on this I'd try the following changes (in rough order of how easy they should be):

1. Change your AddNews method - if the new data is not newer then don't save any changes for that item.

2. Change your deletion loop to be a single delete from News where Date <= @yesterday

3. Look at indexes on the news item title and the date, these appear to be the fields that you're querying most.

4. Look at replacing your AddNews method with something that does an upsert/merge

5. Does re.GetByCategoryID hit your DB? If so consider splitting that out and either building it into the update query or populating a dictionary to look it up more quickly.

Basically you should have (at most) 1 DB operation per new news article and 1 DB operation to delete the old ones. You currently have 3 per article less than a day old (re.GetByCategoryID + db.News.Any + db.News.Add|DeleteObject) another 1 (re.GetAllWorldNewsByID) and then yet another 1 per article to delete (re.DeleteNews).

Add Profiling

You can add profiling to MVC projects that will tell you exactly how long each step is taking and help find how to optimise them using MiniProfiler. It's used on StackOverflow and I've used it a lot myself - it will tell you which steps are slowing you down and which aren't worth micro-optimising.

If you don't want to use that there are optimisation tools in Visual Studio as well as third party ones like RedGate ANTS.

Answer 2

Move the retrieval of the XML data and population of the database to a back-end process. This way, your action will just retrieve the data from your database and return it.

More specifically,

1. Create a program that will run in the background (like a Windows service)
2. In a loop, retrieve your XML data, update your database, then wait for your desired delay period.

For example, to delay, you can use the following to delay for 1 minute (60 seconds):

System.Threading.Thread.Sleep(60*1000);

Sleep may not be the best method of delaying for your needs, but it will work as a start.

Answer 3

One way to solve this is with ASP.NET output caching.

Output caching allows you to do your action once - the generated page is then cached, so that the action is not performed every time. You can specify the action (or class) to cache, as well as the duration to keep the item cached. When the cached item expires, the action will be run again.

There is a C#/ MVC tutorial at the ASP.NET site: Improving Performance with Output Caching.

I would recommend you read the link, but here are some relevant parts:

Imagine, for example, that your ASP.NET MVC application displays a list of database records in a view named Index. Normally, each and every time that a user invokes the controller action that returns the Index view, the set of database records must be retrieved from the database by executing a database query.

If, on the other hand, you take advantage of the output cache then you can avoid executing a database query every time any user invokes the same controller action. The view can be retrieved from the cache instead of being regenerated from the controller action. Caching enables you to avoid performing redundant work on the server.

You enable output caching by adding an [OutputCache] attribute to either an individual controller action or an entire controller class.

---

Edit - As an example, this would cache your ActionResult for 1 hour:

[OutputCache(Duration = 3600, VaryByParam = "none")]
public ActionResult Index()
{
...

Answer 4

100000+ concurrent users and writing to sql database, I think that will be hard to achieve without rethinking whole infrastructure.

I would agree with Matt, that it would be best to separate writes to database (have indexer process) from generating results to user. In such case you would have 1 writer and 100000+ readers and that would be still too much for single server instance and relational database scaling is hard. Looking from this perspective, I would think about non relational persistence solution, especially because your data doesn't look very critical.

Looks like output caching would work for you as information is not user specific, so question is, if simple output caching would be good enough - may be you need distributed cache for this. To know that, you need to count - how often will expire your cache and how much resources you need to generate response for expired cache. Keep in mind, that single server won't be able to serve 100000+ concurrent users, so you will have multiple front end instances, each requiring to generate own result to cache it - that is where distributed cache would kick in - servers could share generated result.

Answer 5

You can implement a class for managing execution of your xml processing and db operations at a chosen time interval. I wouldn't use timers since I don't think it's necessary: if you have 100 000 making requests, you can just check if you need to execute your function every request.

Here is a class you can use:

public static class DelayedAction
{
    private static Dictionary<Action, Tuple<DateTime, TimeSpan>> _actions;

    static DelayedAction()
    {
        _actions = new Dictionary<Action, Tuple<DateTime, TimeSpan>>();
    }

    public static void Add(Action a, TimeSpan executionInterval)
    {
        lock (_actions)
        {
            _actions.Add(a, new Tuple<DateTime, TimeSpan>(DateTime.MinValue, executionInterval));
        }
    }

    public static void ExecuteIfNeeded(Action a)
    {
        lock (_actions)
        {
            Tuple<DateTime, TimeSpan> t = _actions[a];
            if (DateTime.Now - t.Item1 > t.Item2)
            {
                _actions[a] = new Tuple<DateTime, TimeSpan>(DateTime.Now, t.Item2);
                a();
            }
        }
    }
}

It is thread safe, and you can add as much delayed actions as you want.

To use it, just move your xml retrieval and save code to a function, let's call it updateNews:

private void updateNews()
{
       //Get data from xml url (This is the code that shuld not run everytime a user visits the view)
        var url = "http://www.interneturl.com/file.xml";
        XNamespace dcM = "http://search.yahoo.com/mrss/";
        var xdoc = XDocument.Load(url);
        var items = xdoc.Descendants("item")
        .Select(item => new
        {
            Title = item.Element("title").Value,
            Description = item.Element("description").Value,
            Link = item.Element("link").Value,
            PubDate = item.Element("pubDate").Value, 
            MyImage = (string)item.Elements(dcM + "thumbnail")
           .Where(i => i.Attribute("width").Value == "144" && i.Attribute("height").Value == "81")
           .Select(i => i.Attribute("url").Value)
           .SingleOrDefault()
        })
        .ToList();

        //Fill my db entities with the xml data(This is the code that shuld not run everytime a user visits the view)
        foreach (var item in items)
        {
            var date = DateTime.Parse(item.PubDate);
            if (!item.Title.Contains(":") && !(date <= DateTime.Now.AddDays(-1)))
                {
                    News NewsItem = new News();
                    Category Category = new Category();
                    var CategoryID = 2;

                    var WorldCategoryID = re.GetByCategoryID(CategoryID);
                    NewsItem.Category = WorldCategoryID;

                    NewsItem.Description = item.Description;
                    NewsItem.Title = item.Title.Replace("'", "");
                    NewsItem.Image = item.MyImage;

                    NewsItem.Link = item.Link;
                    NewsItem.Date = DateTime.Parse(item.PubDate);
                    re.AddNews(NewsItem);
                    re.save();
                }
            }
}

Then add a static constructor to your controller:

static MyController()
{
    DelayedAction.Add(updateNews, new TimeSpan(0, 10, 0)); // set interval to 10mn
}

and then in your Index method:

public ActionResult Index()
{
    DelayedAction.ExecuteIfNeeded(updateNews);

    //All code below this commenting needs to run everytime a user visits the view
    ....
}

That way, everytime you get a request for this page, you can check if you need to update your data. And you can use that for every other processing that needs to be delayed.

That could be a good addition to caching.

Answer 6

First of all you should't deleting of news in runtime. You can do that manually or by shedule and instead of

var GetAllItems = re.GetAllWorldNewsByID();
foreach (var newsitemz in GetAllItems)
{
    if (newsitemz.Date <= DateTime.Now.AddDays(-1))
    {
        re.DeleteNews(newsitemz);
        re.save();
    }
}

use code:

var GetAllItems = re.GetAllWorldNewsByID().Where(x=>x.Date > DateTime.Now.AddDays(-1)).ToList();

GetAllWorldNewsByID() must return IQuaryable, because if you return List, you lose benefits of deffered execution (What are the benefits of a Deferred Execution in LINQ?). When you don't deleting at runtime, you havn't big delay for service operation (because that operation not for users, but for cleaning your database)

At the second, you can use caching

//Get data from xml url (This is the code that shuld not run everytime a user visits the view)
var url = "http://www.interneturl.com/file.xml";
// Get data from cache (if available)
List<TypeOfItems> GetAllItems = (List<TypeOfItems>)HttpContext.Current.Cache.Get(/*unique identity of you xml, such as url*/ url);
if (GetAllItems == null)
{
    var xdoc = XDocument.Load(url);
    items = xdoc.Descendants("item").Select(item => new
        {
            Title = item.Element("title").Value,
            Description = item.Element("description").Value,
            Link = item.Element("link").Value,
            PubDate = item.Element("pubDate").Value, 
            MyImage = (string)item.Elements(dcM + "thumbnail")
           .Where(i => i.Attribute("width").Value == "144" && i.Attribute("height").Value == "81")
           .Select(i => i.Attribute("url").Value)
           .SingleOrDefault()
        })
        .ToList();

    // Fill db

    GetAllItems = re.GetAllWorldNewsByID().Where(x=>x.Date > DateTime.Now.AddDays(-1)).ToList()

    // put data into the cache
    HttpContext.Current.Cache.Add(/*unique identity of you xml, such as url*/url, /*data*/ GetAllItems, null,
        DateTime.Now.AddMinutes(1) /*time of cache actual*/,
        System.Web.Caching.Cache.NoSlidingExpiration,
        System.Web.Caching.CacheItemPriority.Default, null);
}

Answer 7

Right now my Actionresult does the following things:

• Retrieve XML file from a internet url
• Fills the xml data to my DB
• DB data fills my Viewmodel
• Returns the model to the view

In all that steps the XML file is one of the "weak/slow link".

In my applications I have change the serialization of XML with the Protobuf (for net) and the speed was change dramatically !

The read,write,transfer of the XML file make this steps slow, especially if you make it on each call.

So this is the first point that I will change, the XML serialization with a faster one.