Slow UI performance when searching database, despite threading

Question

I am currently working on my project and the problem that I've been facing for a while is when you are searching a data in database there's a slow down in performance and there's a unresponsiveness.

I've already created a thread but it still giving me a headache

//to start the thread when textbox has change
private void textBox1_TextChanged(object sender, EventArgs e)
{
    ThreadStart thread2Start = new ThreadStart(searchMyData);
    Thread t2 = new Thread(thread2Start);
    t2.Start();
}

public void searchMyData()
{
    if (radGridView1.InvokeRequired)
    {
        radGridView1.Invoke(new Action(() =>
        {
            MySqlConnection connection = new MySqlConnection(MyConnectionString);
            MySqlCommand cmd;
            connection.Open();

            try
            {
                if(!(textBox1.Text=="Search Students")) 
                { 
                    cmd = connection.CreateCommand();
                    cmd.CommandText = "SELECT * from studenttable where studname like'" + textBox1.Text + "%' OR studnum like'" + textBox1.Text + "%' OR studcourse like'" + textBox1.Text + "%' OR studemail like'" + textBox1.Text + "%' OR studsec like'" + textBox1.Text + "%' OR studgender like'" + textBox1.Text + "%' ";

                    MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adap.Fill(ds);
                    radGridView1.DataSource = ds.Tables[0].DefaultView;
                    connection.Close();
                }
            }
        }
    }
}

So what could be the solution here to improve the performance of my program?

*As soon as you type new Thread(), it’s over; your project already has legacy code.* http://shop.oreilly.com/product/0636920030171.do — , Feb 16 '16 at 15:31

Nemo · Answer 1 · 2016-02-16T15:40:13.703

0

You are running the searchMyData() function in the UI thread, that's why its slow. Try updating code to following,

private void txtSearch_TextChanged(object sender, EventArgs e)
{
    if (txtSymbol.Text == "Search Students")
    {
        ParameterizedThreadStart pts = new ParameterizedThreadStart(searchMyData);
        Thread t = new Thread(pts);
        t.Start(txtSymbol.Text);
    }
 }

public void searchMyData(object state)
{
    try
    {
        string text = state.ToString();
        using (MySqlConnection connection = new MySqlConnection(MyConnectionString))
        {
            connection.Open();
            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT * from studenttable where studname like'" + text + "%' OR studnum like'" + text + "%' OR studcourse like'" + text + "%' OR studemail like'" + text + "%' OR studsec like'" + text + "%' OR studgender like'" + text + "%' ";

            MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adap.Fill(ds);
            radGridView1.Invoke(new Action(() => { radGridView1.DataSource = ds }));
        }
    }
    catch(Exception ex) { }
}

edited Feb 16 '16 at 15:40

answered Feb 16 '16 at 15:05

Nemo

3,285
1
28
22

Yes, but that thread is marshaling the call back to the UI thread via the radGridView1.Invoke() call. Let me post some code, so its more clear. – Nemo Feb 16 '16 at 15:10
*that's why its slow* I strongly argue that! I doubt that a specific thread makes an executed function/method slower or faster (except if you are dealing with prioritization). What you actually mean is that it blocks the UI thread, which then cannot handle any other ui rendering ... but that is not defined as *slow* – Feb 16 '16 at 15:26
I do see that in the question ... `radGridView1.InvokeRequired` and `radGridView1.Invoke(new Action(() =>` – Feb 16 '16 at 15:38
how can you be so certain that there is no other control the ui has to render? finally, *You are running the searchMyData() function in the UI thread* is technically not fully true. the op is running the (inner) body of the `searchMyData` method on the ui thread, as (s)he encapsulates it into a delegate/`Action`. – Feb 16 '16 at 15:44
The call is being synchronized to the UI thread, if the UI thread is busy, it will still get to it once its done with whatever it was doing. Try running the code for yourself, it will not block. – Nemo Feb 16 '16 at 15:57
@Nemo fair enough, i forgot about the queuing - nevertheless, *slow* is imho the wrong adjective here. – Feb 16 '16 at 16:02
@AndreasNiedermair In this case, I don't have a better word :) – Nemo Feb 16 '16 at 16:12

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

There's no need to introduce explicit threads for this kind of thing. The operations that take time in this code are opening the db connection, and filling the dataset. You can easily use tasks and async/await to do this in the background. I don't think the MySql connections and adapters have async methods, so you may have to wrap them in tasks, like so:

private void txtSearch_TextChanged(object sender, EventArgs e)
    {
        if (txtSymbol.Text == "Search Students")
        {
           radGridView1.DataSource = await SearchMyDataAsync(txtSymbol.Text);
        }
    }

    async Task<DataSet> SearchMyDataAsync(object state)
    {
        try
        {
            using (MySqlConnection connection = new MySqlConnection(MyConnectionString))
            {
                await Task.Run(() => connection.Open()); //Or possibly connection.OpenAsync() if it exists...
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * from studenttable where studname like'" + text + "%' OR studnum like'" +
                                  text + "%' OR studcourse like'" + text + "%' OR studemail like'" + text +
                                  "%' OR studsec like'" + text + "%' OR studgender like'" + text + "%' ";

                MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                await Task.Run(adap.Fill(ds));
                return ds;
            }
        }
        catch (Exception ex)
        {
            throw;
        }
    }

Also, be aware that you should think about reentrancy when you start async actions from UI events, regardless of the technique used. There are some good info on that here on stack overflow, but basically you will probably want to avoid doing several db-queries at once, when the user types some letters in rapid succession. More info here: Reentrancy in async/await?

edited May 23 '17 at 11:50

Community

1
1

answered Feb 16 '16 at 15:41

Mikael Nitell

1,069
6
16

Why the `await Task.Run(() => connection.Open());` ? And the filling of the dataset async as well? It is already running async. That looks like 'making-it-all-async-because-we-can'. – Maarten Feb 16 '16 at 15:44
Why make the method return the dataset, await it, and use that in the original `txtSearch_TextChanged` eventhandler method? – Maarten Feb 16 '16 at 15:44
I don't understand that first comment. The open is very much synchronous. I've changed the code to reflect the second comment now – Mikael Nitell Feb 16 '16 at 15:46
About the first comment, let me put it this way: what are you gaining by doing this: `await Task.Run(() => connection.Open());` instead of this: `connection.Open();` ? – Maarten Feb 16 '16 at 15:47
And this: `await Task.Run(adap.Fill(fs));` instead of this: `adap.Fill(fs);` ? – Maarten Feb 16 '16 at 15:48
@Maarten i am not fully sure, but if there is no `await` inside the method, how can we make it `async` so to leverage the await-magic on the caller-side? – Feb 16 '16 at 15:48
Fill and Open are synchronous, and will block the thread it runs on. So just calling adap.Fill(fs) will block the UI thread. This makes the UI unresponsive. So, we wrap the calls in Tasks, to run them on separate threads and return to the UI thread once they are finished. – Mikael Nitell Feb 16 '16 at 15:50
@MikaelNitell I understand, but why are breaking up the method in little bits that you are running async, instead of the whole method in a single async? I would do something like `return await Task.Run(() => { /* rest of the method */ });` – Maarten Feb 16 '16 at 15:53
Yeah, that's another way to do it – Mikael Nitell Feb 16 '16 at 15:54
@Maarten in fact you don't need `await` or `async` in this example at all - you could simply create a new task, and switch the context within `ContinueWith`. – Feb 16 '16 at 15:54
But now we're getting into details on how to best use the TPL. Remember the original post was using threads... so any style of task-based programming is probably preferable to the original code. And also, I think the bigger problem here is reentrancy... – Mikael Nitell Feb 16 '16 at 15:56
@MikaelNitell which you could simply come around with a `CancellationToken` :) but i do fully agree - this is more into the details than the OP might can handle ... – Feb 16 '16 at 16:00

Slow UI performance when searching database, despite threading

2 Answers2