simple multithreaded c# exe to test scalability of SQL stored proc

Question

I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level but all are abstracted by the stored proc call.

I'm trying to slam the DB to see what the breaking point would be by creating a simple multi-threaded app to call the proc multiple times and show a timestamp of the results in a grid. Then I just scroll down the grid to see the duration from the first result to the last to determine how many results i can serve per second.

I think this because SQL should be able to handle hundreds of these calls per second but the time stamps show only 4 per second. This seems way too long to select a value based on an ID within a clustered index column.

In a nutshell, I have a table such as this userId, newMsgCount with a clustered index on userId. SQL should be able to server hundreds of these responses per second. I think the laggard is my .NET app.

How can I make this a good test to achieve the test results based on SQL performance?

I have a textbox that takes a number of DB calls and a button to invoke the test, then a grid to show results.

private void btnSend_Click(object sender, EventArgs e)
{
    int count = Convert.ToInt32(txtThreadCount.Text);
    dtStatus = new DataTable();
    dtStatus.Columns.Add(new DataColumn("Thread No."));
    //dtStatus.Columns.Add("User Id");
    dtStatus.Columns.Add("Count");
    dtStatus.Columns.Add("time");

    for (int i = 0; i < count; i++)
    {
        ThreadPool.QueueUserWorkItem(GetMessageCount, i);
    }

    MessageBox.Show("Results retrieved successfully. \n Please see the result tab.");
    grdEmail.DataSource = dtStatus;
    grdEmail.Refresh();
}

private static void GetMessageCount(object threadContext)
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = new SqlConnection();
    cmd.Connection.ConnectionString = "Data Source=server1;Initial Catalog=emails;Persist Security Info=True;User ID=IS_User;Password=M1";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sel_Message_New_Count";
    cmd.Parameters.AddWithValue("@UserId", 4);
    SqlDataAdapter da = new SqlDataAdapter();
    DataSet ds = new DataSet();
    da.SelectCommand = cmd;
    da.Fill(ds);

    if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
    {

        DataRow dr = dtStatus.NewRow();
        dr[0] = dtStatus.Rows.Count + 1;
        //dr[1] = 1;
        dr[1] = ds.Tables[0].Rows[0][0];
        dr[2] = DateTime.Now.ToString("hh:mm.ss:ffff");
        dtStatus.Rows.Add(dr);
    }
}

score 0 · Answer 1 · answered Sep 15 '11 at 17:53

0

You may want to try changing the number of threads the ThreadPool is using. See the ThreadPool.SetMinThreads method.

answered Sep 15 '11 at 17:53

C. Dragon 76

9,882
9
34
41

Has absolutely nothing to do with the number of ThreadPool threads. – CommonSense Sep 15 '11 at 18:48
Are you sure? Don't you think that depends on things like the speed of the stored procedure and how many concurrent connections the database allows? – C. Dragon 76 Sep 17 '11 at 00:36

score 0 · Answer 2 · edited May 23 '17 at 10:24

You have massive memory leak issues. You need a using statement on SQLCommand, SQLConnection, SQLDataAdapter.
Don't use a DataSet, use a DataTable and dispose of it in a using statement.
Are you sure that DataTable.NewRow() is a thread safe construct whereby you can just slam it full of rows?
Why are you executing that code in a button click event? The button click event should spawn a background thread, which in turn executes the code you currently have in the button click event. The UI should then display a status bar indicating the progess of that background thread...

At the heart of your problem is the fact that you're not waiting for all those threadpool threads you spawn to finish. You start them, then immediately display a MessageBox.

You need to wait for each thread to finish.

How do you do that? Here's one solution - or - you could try using Parallel.For if you're using .NET 4 and the Task Parallel Library.

simple multithreaded c# exe to test scalability of SQL stored proc

2 Answers2