How To perform a SQL Query to DataTable Operation That Can Be Cancelled

Question

I tried to make the title as specific as possible. Basically what I have running inside a backgroundworker thread now is some code that looks like:

 SqlConnection conn = new SqlConnection(connstring);
                    SqlCommand cmd = new SqlCommand(query, conn);
                    conn.Open();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(Results);
                    conn.Close();
                    sda.Dispose();

Where query is a string representing a large, time consuming query, and conn is the connection object.

My problem now is I need a stop button. I've come to realize killing the backgroundworker would be worthless because I still want to keep what results are left over after the query is canceled. Plus it wouldn't be able to check the canceled state until after the query.

What I've come up with so far:

I've been trying to conceptualize how to handle this efficiently without taking too big of a performance hit.

My idea was to use a SqlDataReader to read the data from the query piece at a time so that I had a "loop" to check a flag I could set from the GUI via a button. The problem is as far as I know I can't use the Load() method of a datatable and still be able to cancel the sqlcommand. If I'm wrong please let me know because that would make cancelling slightly easier.

In light of what I discovered I came to the realization I may only be able to cancel the sqlcommand mid-query if I did something like the below (pseudo-code):

while(reader.Read())
{
 //check flag status
 //if it is set to 'kill' fire off the kill thread

 //otherwise populate the datatable with what was read
}

However, it would seem to me this would be highly ineffective and possibly costly. Is this the only way to kill a sqlcommand in progress that absolutely needs to be in a datatable? Any help would be appreciated!

Answer 1

There are really two stages where cancelling matters:

1. Cancelling the initial query execution before the first rows are returned
2. Aborting the process of reading the rows as they are served

Depending on the nature of the actual sql statement, either of these steps could be 99% of the time, so they both should be considered. For example, calling SELECT * on some table with a billion rows will take essentionally no time to execute but will take a very long time read. Conversely, requesting a super complicated join on poorly tuned tables and then wrapping it all in some aggregating clauses may take minutes to execute but negligible time to read the handful of rows once they are actually returned.

Well-tuned advanced database engines will also cache chunks of rows at a time for complicated queries, so you will see alternating pauses where the engine is executing the query on the next batch of rows and then fast bursts of data as it returns the next batch of results.

Cancelling the query execution

In order to be able to cancel a query while it is executing you can use one of the overloads of SqlCommand.BeginExecuteReader to start the query, and call SqlCommand.Cancel to abort it. Alternatively you can call ExecuteReader() syncronously in one thread and still call Cancel() from another. I'm not including code examples because there are plenty of them in the documentation.

Aborting the read operation

Here using a simple boolean flag is probably the easiest way. And remember it's really easy to fill a data table row using the Rows.Add() overload that takes an array of object, that is:

object[] buffer = new object[reader.FieldCount]
while(reader.Read()) {
    if(cancelFlag) break;
    reader.GetValues(buffer);
    dataTable.Rows.Add(buffer);
}

Cancelling blocking calls to Read()

A sort of mixed case occurs when, as mentioned earlier, a call to reader.Read() causes the database engine to do another batch of intensive processing. As noted in the MSDN documentation, calls to Read() can be blocking in this case even if the original query was executed with BeginExecuteReader. You can still get around this by calling Read() in one thread that's handling all the reading but calling Cancel() in another thread. The way you know if you reader is in a blocking Read call is to have another flag the the reader thread updates while the monitoring thread reads:

...
inRead = true
while(reader.Read()) {
    inRead = false
    ...
    inRead = true
}

// Somewhere else:
private void foo_onUITimerTick(...) {
   status.Text = inRead ? "Waiting for server" : "Reading";
}

Regarding performance of Reader vs Adapter

A DataReader is usually faster than using DataAdapter.Fill(). The whole point of a DataReader is to be really, really fast and responsive for reading. Checking some boolean flag once per row would not add a measurable difference in time even over millions of rows.

The limiting factor for a big database query is not the local CPU processing time but the size of the I/O pipe (your network connection for a remote database or your disk speed for a local one) or a combination of the db server's own disk speed and CPU processing time for a complex query. Both a DataAdapter and a DataReader will spend time (perhaps the majority of the time) just waiting for a few nanoseconds at a time for the next row to be served.

One convenience of DataAdapter.Fill() is that it does the magic of dynamically generating the DataTable columns to match the query results, but that's not difficult to do yourself (see SqlDataReader.GetSchemaTable()).