none
How Do You Monitor the Progress of a Database Operation? RRS feed

  • Question

  • Is there a way to get progress information on a Database operation like a query with a very large expected result set?

    I can start the query asynchronously (even though it returns the SqlDataReader almost immediately).  I can then start a DataTable.Load( reader ).  This takes a long time.  I can also cancel the operation from another thread.  But, I'd like to get an idea of the progress, even if that is only the number of records read thus far.

    Any ideas?  Here is some Proof of Concept code that I've started with...

        public class DbWithInterrupt : IDisposable
        {
            SqlConnection conn = null;
            SqlCommand sc;
            SqlDataReader sdr;
    
            public DbWithInterrupt()
            {
                conn = new SqlConnection(GetConnectString());
                conn.Open();
            }
            private string GetConnectString()
            {
                return @"Data Source=MYDBSERVER;" +
                    "Initial Catalog=MYDB;" +
                    "Integrated Security=SSPI;" +
                    "Asynchronous Processing=true;";
            }
    
            public void Stop()
            {
                sc.Cancel();
            }
    
            public void Start()
            {
                sc = conn.CreateCommand();
                sc.Connection = conn;
                sc.CommandText = "Select * from MY_TABLE";
                sc.CommandType = System.Data.CommandType.Text;
                
                IAsyncResult ar = sc.BeginExecuteReader(MyDbCallback, null);
            }
            private void MyDbCallback(IAsyncResult ar)
            {
                try
                {
                    using (sdr = sc.EndExecuteReader(ar))
                    {
                        DataTable dt = new DataTable();
                        dt.Load(sdr);
                        Console.WriteLine("Success");
                    }
                }
                catch (Exception)
                {
                    Console.WriteLine("Exception - probably cancelled");
                }
                sc.Dispose();
                sc = null;
            }
    
            public void Dispose()
            {
                if (conn != null)
                    conn.Dispose();
                conn = null;
            }
        }
    

    Les Potter, Xalnix Corporation, Yet Another C# Blog
    Thursday, June 25, 2009 6:59 PM

Answers

  • > But, I'd like to get an idea of the progress

    One thing to be careful of is that, depending on the database operation, it might be a while before any rows at all start streaming back.  For example, the database might need to perform a sort operation on a large number of rows.

    Once the rows start streaming back, the DataSet.RowChanged event will be raised as each row is updated in the DataSet.

    Things to watch out for:
    If you update the UI, you must perform the update on the main UI thread.  You also need to take care not to overwhelm the UI thread with too many updates.  A technique that I have found useful is to update a counter variable in the event handler.  Put a Timer on your Form that periodically checks that variable and updates the UI.

    (For users of DataAdapter, you can also look into the RowUpdated event on the DataAdapter.)

    Thursday, June 25, 2009 11:35 PM

All replies

  • > But, I'd like to get an idea of the progress

    One thing to be careful of is that, depending on the database operation, it might be a while before any rows at all start streaming back.  For example, the database might need to perform a sort operation on a large number of rows.

    Once the rows start streaming back, the DataSet.RowChanged event will be raised as each row is updated in the DataSet.

    Things to watch out for:
    If you update the UI, you must perform the update on the main UI thread.  You also need to take care not to overwhelm the UI thread with too many updates.  A technique that I have found useful is to update a counter variable in the event handler.  Put a Timer on your Form that periodically checks that variable and updates the UI.

    (For users of DataAdapter, you can also look into the RowUpdated event on the DataAdapter.)

    Thursday, June 25, 2009 11:35 PM
  • Thankyou.  I think that answers my question.  (By the way, a DataSet does not have a RowChanged event, but a DataTable does).

    I agree with you whole heartedly on properly updating the UI by not accessing it from the wrong thread and my not overloading it with update events.

    Furthermore, I noticed that handling the RowChanged event and doing nothing else but incrementing a counter (no UI update) costs about about 20% more time to execute.  I am trying to balance competing goals, that is, giving the user feedback that progress is taking place, and performance.

    So, if I simply expose the DataTable.Rows.Count, and poll it from the UI with a System.Windows.Forms.Timer every 100ms or so, the performance is back and I don't over load the UI.  This works because I don't need fine granular control of each row read, only to know periodically how many rows have been read.

    Les Potter, Xalnix Corporation, Yet Another C# Blog
    Friday, June 26, 2009 11:44 AM