Comparing prices with a webservice Working on a project that parses through an excel document to find price changes off of a webservice that is in place. The code works but is slower than expected. At first it was processing 28 products a minute, and now after some work I have it up to 80 products a minute. 

I've used Jetbrains dottrace to determine the bottleneck is on this line of code

 xmlstr = wc.DownloadString(@"http://awebsiteservice/prices?SearchString=" + UPC);


I used System.Net.WebClient to make the calls thinking it was the fastest way.

I've done some research and do not know if there is a way to eliminate this bottleneck or not. I am posting the code below for general code review, and also in hopes that there is something simple I am missing that can help the API calls go faster or smoother. 

 private static void PriceDifferences(ExcelWorksheet ws, int maxval)
    {
        bool looping = true;
        int i = 2;
        var wc = new WebClient();
        XmlDocument xml = new XmlDocument();
        string xmlstr;
        Console.WriteLine("Press X to save results and stop process");
        do
        {
            if (Console.KeyAvailable)
            {
                if (Console.ReadKey(true).Key == ConsoleKey.X)
                {
                    looping = false;
                }
            }

            if (ws.Cells[i, 8].Value != null)
            {                                                                                           
                if (DateTime.ParseExact(ws.Cells[i, 8].Value.ToString(), "M/dd/yyyy", null) &gt;= DateTime.Now.AddDays(-5).Date)   //If analysis ran in last 5 days skip
                {
                    i++;
                    continue;
                }
            }

            var UPC = ws.Cells[i, 1].Value;
            var cPrice = ws.Cells[i, 2].Value;

            ws.Cells[i, 8].Value = DateTime.Now.Date.ToShortDateString();

            //Console.WriteLine("UPC is {0}, Current Price is {1}, and Lowest Price known is {2}", UPC, cPrice, lPrice);
            //Console.WriteLine("Checking for price differences");

            try
            {
                xmlstr = wc.DownloadString(@"http://awebsiteservice/prices?SearchString=" + UPC);
            }
            catch
            {
                continue;
            }

            xml.LoadXml(xmlstr);

            XmlNodeList refList = xml.GetElementsByTagName("Store");
            List&lt;string&gt; pricelist = new List&lt;string&gt;();

            GetTrustedPrices(refList, pricelist);
            UpdatePrices(ws, pricelist, i, cPrice);

            i++;
            Console.WriteLine(i);

        }
        while (looping &amp;&amp; i &lt; maxval);
    }


The functions GetTrustedPrices and UpdatePrices are below

private static void GetTrustedPrices(XmlNodeList refList, List&lt;string&gt; pricelist)
    {
        foreach (XmlNode node in refList)
        {
            if (node.ChildNodes[2].InnerText == "Trusted")
            {
                pricelist.Add(node.ChildNodes[4].ChildNodes[2].InnerText);
            }
        }
    }

private static void UpdatePrices(ExcelWorksheet ws, List&lt;string&gt; pricelist, int i, object cPrice)
    {
        List&lt;double&gt; result = pricelist.Select(x =&gt; double.Parse(x)).ToList();

        if (result.Count &lt;= 0) return;
        result = result.Where(itm =&gt; itm &gt;= 1).ToList();
        if (result.Count &lt;= 0) return;

        var lowest = result.Min();
        if (!((double) cPrice &gt; lowest)) return;
        ws.Cells[i, 6].Value = lowest;
        ws.Cells[i, 7].Value = lowest - (double) cPrice;
    }
Well you got a lot going on so we will take it one step at a time.  The improve the performance you will need to use the TPL.  This should allow things to start to process in parallel. Word of warning I didn't test all this code as I don't have the Excel sheet or website so there might be minor bugs in it

first you have a lot going on for one method we should break them out to each one does a specific thing.  Also to make things easier we going to create a class to instantiate so we can keep some state. 

Lets create a couple small classes, I'd nest them in the main class, to hold some state for passing around.

First one is to hold the ExcelData we care about

private class ExcelDataSink
{
    public int ExcelPosition { get; set; }
    public string UPC { get; set; }
    public double CurrentPrice { get; set; }
}


This will be better for anyone coming after you.  Like them I had to assume cPrice is the current price.  Here it's self explanatory what it is.

Next is a class to hold the Pricing Data we get back from the WebSite plus the info we need to update the Excel Document.

private class PricingDataSink
{
    public int ExcelPosition { get; set; }
    public IList&lt;double&gt; Prices { get; set; }
    public double CurrentPrice { get; set; }
}


Since we are going to try processing in parallel we will want to put a rate limiter on it.  We don't want to flood a website with over 80 request a second.  I'm going to make a constant that you can test with and tinker to see what works best for your situation. Also going to switch to the HttpClient instead of the WebClient and hard code the URL in a constant as well. 

public class Pricing
{
    private readonly HttpClient _httpClient = new HttpClient();
    private const string URL = @"http://awebsiteservice/prices?SearchString=";
    private const int InFlight= 5; // How many request do we want to process at once


We need to read from the Excel file and get use data.  That should be it's own method. Excel is single threaded so no point trying to parallel this code

/// &lt;summary&gt;
/// check if the price hasn't been updated in over 5 days and if not reads the current price and upc
/// &lt;/summary&gt;
/// &lt;param name="worksheet"&gt;Wrk Sheet to use&lt;/param&gt;
/// &lt;param name="maxValue"&gt;row to end with in Excel Worksheet&lt;/param&gt;
/// &lt;returns&gt;&lt;/returns&gt;
private IEnumerable&lt;ExcelDataSink&gt; GetExcelData(ExcelWorksheet worksheet, int maxValue)
{
    for (var i = 2; i &lt; maxValue; i++)
    {
        if (worksheet.Cells[1, 8].Value == null ||
            DateTime.ParseExact(worksheet.Cells[i, 8].Value.ToString(), "M/dd/yyyy", null) &lt;
            DateTime.Now.AddDays(-5).Date) //If analysis ran in last 5 days skip
        {
            yield return new ExcelDataSink()
            {
                ExcelPosition = i,
                CurrentPrice = double.Parse(worksheet.Cells[i, 2].Value),
                UPC = worksheet.Cells[i, 1].Value
            };
        }
    }
}


Going to drop out the console stuff as the TPL standard way to cancel is with a CancellationToken  Plus this class should only care about what it needs to do and not about displaying progress or reading keyboard input.  If you need to show progress I would recommending accepting the IProgress Interface and using that for reporting. 

Now the TPL DataFlow Blocks are good at making a mesh for this kind of thing so I'm going to use that.  There are other options I just find this easier to understand. 

Going to use the BufferBlock to put the data we read from Excel in.  You can kind of think of this as the queue that holds the data. To load the data into the Block we will create another method and mark the buffer complete when we are done reading from Excel 

/// &lt;summary&gt;
/// Loads data from Excel into the target Block
/// &lt;/summary&gt;
/// &lt;param name="sourceBlock"&gt;&lt;/param&gt;
/// &lt;param name="workSheet"&gt;&lt;/param&gt;
/// &lt;param name="maxValue"&gt;&lt;/param&gt;
/// &lt;param name="token"&gt;&lt;/param&gt;
/// &lt;returns&gt;&lt;/returns&gt;
private async Task LoadData(ITargetBlock&lt;ExcelDataSink&gt; sourceBlock, ExcelWorksheet workSheet, int maxValue,
    CancellationToken token)
{
    foreach (var sink in GetExcelData(workSheet, maxValue))
    {
        await sourceBlock.SendAsync(sink, token);
    }

    sourceBlock.Complete();
} 


Now we need to change the ExcelDataSink to the PricingDataSink and make the web call

/// &lt;summary&gt;
/// Retreives new pricing from website 
/// &lt;/summary&gt;
/// &lt;param name="excelDataSink"&gt;&lt;/param&gt;
/// &lt;returns&gt;&lt;/returns&gt;
private async Task&lt;PricingDataSink&gt; GetWebPricing(ExcelDataSink excelDataSink)
{
    var content = await _httpClient.GetStringAsync(URL + excelDataSink.UPC);

    var xml = new XmlDocument();
    xml.LoadXml(content);

    return new PricingDataSink()
    {
        CurrentPrice = excelDataSink.CurrentPrice,
        ExcelPosition = excelDataSink.ExcelPosition,
        Prices = GetTrustedPrices(xml).ToList()
    };
}

private IEnumerable&lt;double&gt; GetTrustedPrices(XmlDocument xmlDocument)
{
    return xmlDocument.GetElementsByTagName("Store").Cast&lt;XmlNode&gt;()
        .Where(node =&gt; node.ChildNodes[2].InnerText == "Trusted")
        .Select(node =&gt; XmlConvert.ToDouble(node.ChildNodes[4].ChildNodes[2].InnerText));
}


Now we need to update the prices in the Excel File.

/// &lt;summary&gt;
/// Update the Excel file with the new lower price
/// &lt;/summary&gt;
/// &lt;param name="pricingSink"&gt;&lt;/param&gt;
/// &lt;param name="worksheet"&gt;&lt;/param&gt;
private static void UpdatePrices(PricingDataSink pricingSink, ExcelWorksheet worksheet)
{
    var lowest = pricingSink.Prices.Where(p =&gt; p &gt;= 1).DefaultIfEmpty().Min();
    if (lowest &gt;= 1 &amp;&amp; lowest &lt; pricingSink.CurrentPrice)
    {
        worksheet.Cells[pricingSink.ExcelPosition, 6].Value = lowest;
        worksheet.Cells[pricingSink.ExcelPosition, 7].Value = lowest - pricingSink.CurrentPrice;
        worksheet.Cells[pricingSink.ExcelPosition, 8].Value = DateTime.Now.Date.ToShortDateString();
    }
}


Now we just need to wire everything up.  Going to make a method called UpdatePrice for the main entry point. 

public async Task UpdatePrice(ExcelWorksheet workSheet, int maxValue, CancellationToken token)
{
    // This acts like our queue
    var buffer = new BufferBlock&lt;ExcelDataSink&gt;(new DataflowBlockOptions()
    {
        BoundedCapacity = InFlight, // put a constraint to not dump all data in at once
        CancellationToken = token
    });

    // marking our consumer of the queue to process one items at a time
    var executionOptions = new ExecutionDataflowBlockOptions()
    {
        BoundedCapacity = 1,
        CancellationToken = token
    };

    // List of task running
    var inFlight = new List&lt;Task&gt;();
    var linkOption = new DataflowLinkOptions()
    {
        PropagateCompletion = true
    };

    // create a consumer for the number we want inflight
    for (var i = 0; i &lt; InFlight; i++)
    {
        // Call our code to transform the Excel into the pricing data
        var transform = new TransformBlock&lt;ExcelDataSink, PricingDataSink&gt;(
            async e =&gt; await GetWebPricing(e), executionOptions);

        // Call the code to update the sheet
        var updateExcel = new ActionBlock&lt;PricingDataSink&gt;(p =&gt; UpdatePrices(p, workSheet), executionOptions);

        // wire the mesh together
        transform.LinkTo(updateExcel, linkOption);
        buffer.LinkTo(transform, linkOption);
        inFlight.Add(updateExcel.Completion);
    }

    // Start the load data task
    inFlight.Add(LoadData(buffer, workSheet, maxValue, token));

    // Wait for everything to be completed 
    await Task.WhenAll(inFlight);
}


I would highly recommend reading up on TPL and the TPL DataFlow blocks to understand what is all happening.   To run the code you will need to create a CancellationTokenSource and if you want to cancel it you cancel that the token source.