Using ThreadPool threads with long running ADO.NET queries. Is this scalable?

Question

We are currently enhancing an ASP.NET app that performs quotes on a number of products.

At present the existing quote engine is basically a big stored procedure (2-3 secs per call) followed by a small amount of business logic that runs after the procedure call.

We are looking into multi-threading the call to each product in order to speed up a set of quotes.

Our current approach is to encapsulate each product quote work in a ThreadPool thread. This seems to perform much better, but I'm a little concerned that though it's performing well with a small number of users, will it scale well in a production environment?

Please note at present we are not using async ADO.NET methods.

Note: Our code that calls the ThreadPool has a throttle that queues requests so we can only use a configurable amount of threads from the ThreadPool at one time. We also don't need to wait for the quote results on the same page, we allow the user to progress and check for updates (a quote results page uses AJAX to check for results).

Further note: The preferred solution would be to use a message queue as the quote service is a one-way operation. However, the timescales for the project didn't provide us time to do this.

In the meantime we are going to revise the implementation to use the async methods of ADO.NET (as that is where all the long running aspect of the process is) saving the need to use ThreadPool threads.

Answer 1

Using ThreadPool threads with long running ADO.NET queries. Is this scalable?

The short answer is no, it's not scalable.

The reason is that ThreadPool threads are also used to process regular ASP.NET requests (the same is true for BeginInvoke). There are a limited number of those threads, and when they're used up, incoming HTTP requests will block until a thread becomes available.

Although you can increase the number of threads in the ASP.NET thread pool, there are other issues, such as the fact that the number of threads isn't fixed; it increases slowly in response to load. You can/should use async Pages, but that still leaves open the question of how you actually run your SP. It would be much better to switch to async ADO.NET methods for that part, if you can. They are also much lighter-weight than using a thread per request.

In case it helps, I cover this subject in detail in my book (Ultra-Fast ASP.NET).

Answer 2

ASP.NET has built-in async handlers that let you kick off a request, it runs on a non-handler thread, and may complete on a 3rd thread (differernt from the original request handler thread) all built-in. I've used it tons of times.

http://msdn.microsoft.com/en-us/magazine/cc163725.aspx

Write yourself a little helper method to hook it all up.

/// <summary>
/// On load event override
/// </summary>
/// <param name="e">arguments to the event</param>
protected override void OnLoad(EventArgs e)
{
    base.OnLoad(e);
    string query = this.Page.Request.QueryString["query"];
    if (!string.IsNullOrEmpty(query))
    {
        var pat = new PageAsyncTask(this.BeginAsync, this.EndAsync, this.TimeOut, query);
        this.Page.RegisterAsyncTask(pat);
    }
    string me = string.Format(Format, System.Threading.Thread.CurrentThread.ManagedThreadId, "Onload");
    Trace.Write(me);
}
protected override void Render(HtmlTextWriter writer)
{
    string me = string.Format(Format, System.Threading.Thread.CurrentThread.ManagedThreadId, "Render");
    Trace.Write(me);
    this.Icompleted.Text = DateTime.Now.ToString();
    base.Render(writer);
}
/// <summary>
/// start the async task
/// </summary>
/// <param name="sender">original caller</param>
/// <param name="e">unused arguments</param>
/// <param name="cb">call back routine</param>
/// <param name="state">saved stated</param>
/// <returns>IAsyncResult to signal ender</returns>
private IAsyncResult BeginAsync(object sender, EventArgs e, AsyncCallback cb, object state)
{
    this.bsc = new YourWebServiceReferenceGoesHere();
    return this.bsc.BeginGetResponseXml("1", (string)state, "10", "1", cb, state);
}

/// <summary>
/// when the task completes
/// </summary>
/// <param name="ar">the async result</param>
private void EndAsync(IAsyncResult ar)
{
    XmlResponse response = this.bsc.EndGetResponseXml(ar);
    this.bsc.Close();
    this.bsc = null;
    this.PostProcess(response);
}

private void TimeOut(IAsyncResult ar)
{
    // currently we do nothing here.
}

/// <summary>
/// 
/// </summary>
private void PostProcess(XmlResponse response )
{
    string me = string.Format(Format, System.Threading.Thread.CurrentThread.ManagedThreadId, "bingsearch");
    Trace.Write(me);
    var xds = new XmlDataSource 
    { 
        EnableCaching = false, 
        CacheDuration = 0, 
        Data = response.Xml, 
        Transform = this.RemoveNamespaces() 
    };
    this.some.DataSource = xds;
    this.some.DataBind();
}