How to parallelize database query using ThreadPool?

Question

Hi there,

I'm bugfixing someone else's code where it takes ages to return the complete dataset in the following code:

DataTable dt = someLib.GetDataTable("EXEC [dbo].[CMS_Content_GetAllContents]");
// Copy the DataTable data to list.
foreach (DataRow dr in dt.Rows)
{
        ContentInfo aContentDetail = new ContentInfo(
            (int)dr["ID"],
            (string)dr["ContentName"],
            getCategories((int)dr["ID"]),
            null,
            (string)dr["Publisher"],
            (string)dr["Price"],
            false);
        contentInfoList.Add(aContentDetail); ;
}

private string getCategories(int ContentID)
{
    String categories = String.Empty;
    String query = String.Format("EXEC [dbo].[CMS_Content_GetContentCategories] @ID = {0}", ContentID);
    DataTable dt = clsGlobal.GetDataTable(query);
    foreach (DataRow dr in dt.Rows)
    {
        categories = String.Concat((string)dr["ShortDescription"] + ", ");                
    }
    if (categories.EndsWith(", "))
        categories = categories.TrimEnd(new char[] { ',', ' ' });
    return categories;
}

It is pathetic as there are over 1,000 rows for the DataTable dt and I cannot deal with it from the store proc level!

I'm just wondering if I can bundle the call getCategories(int) into Threadpool.QueueUserWorkItem() such that it can go parallel but dunno how to return the string back to the caller?

Or, is it a bad idea to use Threadpool because I heard that the Threadpool's workers threads are not designed for long running queries such as DB calls as the IOCompletion ports thread may not return in time hence workers are likely to get blocked because of that?

Any helps appreciated.

Answer 1

I'm afraid that threading isn't the answer in this case (on or off the threadpool). Fixing the 1000 calls to the database is. You might optimize the speed of your response for a small number of users, but the response time would still be pretty excruciating, and your app is going to fall over at a very low load.

I don't know how much control you have over the database, so here are some ideas that span a few layers:

1. Pull the categories with the content. You can either create a func to string delimit them, or use a proc that returns multiple datasets in one call.
2. Load all the categories up at once, and put the data together in memory
3. Change your UI so that you aren't using 1000 CMS items at once. Paging or something.

Good luck.

Answer 2

Though, the question has been answer. But this is just so it might help.

You can "move" your code inside a worker thread and let it run. You can also create add an event and as soon as the thread finishes the work, it would let you know. This way you can continue(or move the progress bar to the end, if you have any?) with the rest of your work.

1. Here is how you work with threads.
2. This is what I love, to date.

As a side note, I believe this can be done in the SP(if you can share the query); therefore I would create an SP that fetches the content detail, and for each ContentDetailID, I would prepare a comma separated list of categories and then return with my result.

So, your call will just be:

DataTable dt = someLib.GetDataTable("EXEC [dbo].[CMS_Content_GetAllContents_Ex]");//Note the _EX in the sp name (0: