What is the correct way to deal with procedures that take a "long time" to complete?

Question

I have a Winforms application created in Visual Studio 2005 Pro, it connects to an SQL Server 2005 database using the SqlConnection / SqlCommand / SqlDataAdapter classes to extract data. I have stored procedures in my database to return the data to me.

What is the best way to handle queries that take a "long time" to complete? (i.e long enough that the user starts to think something is wrong). Currently my application locks up until the query is complete, or the query times out. Obviously this is unnacceptable.

I'd at least like a progress meter with a "stop" button on it. The progress meter doesn't even have to do anything useful, being a hint to sit patiently and wait would be enough.

Even better would be a warning that said something like "This will return 140,000 rows of data. Do you want to continue?"

I know this probably requires threads, but how?

Answer 1

To implement your warning, you could first issue a COUNT() query, which would not only return the number of rows the user's about to get, but may start to cache the data.

Answer 2

You may want to create another table to log the execution time of the query. That way you can say "Last time this process took X amount of time." This will provide them with a rough estimate of how long it will take. Otherwise you would have to run a query to get a COUNT which will probably take long itself.

Answer 3

Ok, first, I'd suggest trying to make the queries faster. The first thing to check is to make sure you have appropriate indexes. There are tons of articles online about using indexes properly.

If after all this, your queries are still too slow, you may have to switch to using a database cursor.

With a cursor, you read results one at a time, and pull them. This will give you the chance to display a progress bar.

If you stick to your current query model and simply use threads to draw progress bars, your bars will have no relation to the work being done and will not look right.

Answer 4

You will want to take advantage of Threading. When the user signals to fetch the data, you can spawn a new thread to fetch the data while making a throbber visible on the main form. When the thread returns, you can react and show results.

Answer 5

Making sure that the queries are optimized is the first step, but afterwards, you have to use threading, and .NET has the BackgroundWorker component for these cases.

Here's a complete example

Answer 6

Simple C# 2.0 example:

    private void Form_Load(object sender, EventArgs e)
    {
        BackgroundWorker bw = new BackgroundWorker();
        bw.DoWork += new DoWorkEventHandler(bw_DoWork);
        bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bw_RunWorkerCompleted);
        //change UI to reflect we're doing this
        bw.RunWorkerAsync();
    }

    void bw_DoWork(object sender, DoWorkEventArgs e)
    {
        //SQL Work
    }

    void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        //Let the user know we're done
    }

Answer 7

I use RunWorker and ProgressBar when creating long files.

One trick I use is to find a proxy the length of the file to use when setting up the ProgressBar. For example, the ProgressBar may be based on the number lines in the file the last time the function was run (kept in a long file, perhaps). The ProgressBar, or similar, does not have to be precise.

Answer 8

For your progress bar, look into the Marquee style. If it's not practical for some reason to get an actual progress report (that is, if using CURSOR as dicroce suggested turns out to be untenable/too slow/whatever), you can use a Marquee progress bar to show that your UI is still responding, and you haven't forgotten about the user.

Remember Jakob Nielsen's rule: if you take more than 250 ms (?) to return to the user, they're going to worry something is wrong. Generally, your button should run whatever validation is necessary (quickly!), then show something that tells the user "I'm working" and return control to the UI loop.