C# Application process hangs after some time

I implemented a simple C# application which inserts about 350000 records into the database. This used to work well and the process took approximately 20 minutes.

I created a progress bar which lets you know approximately the progress of the records insertion. When the progress bar reaches about 75% it stops progressing. I have to manually terminate the program as the process doesn't seem to complete. If I use less data (like 10000), the progress bar finishes and the process is completed. However when I try to insert all the records, this won't happen any more.

Note that if I wait longer to terminate the program manually, more records would have been inserted. For example, if I terminate the program after 15 minutes, 200000 records are 开发者_如何学Cinserted, whereas if I terminate the program after 20 minutes, 250000 records are inserted.

This program is using a single thread. In face I can't do anything else until the process is complete. Does this have anything to do with threading or processes?

Any feedback will be greatly appreciated.

Thanks.

It is surprising that your progress bar works at all. If you don't use a separate thread then your long running task will stop the message loop from running, causing your application to be unresponsive.

You should run this task using a BackgroundWorker. Put your long-running code inside a handler for the DoWork event. Use ReportProgess to update the progress bar. Don't access form controls directly from inside the DoWork handler.

There are some examples of how to do it on MSDN.

Also, make sure that you don't update the progress bar for every single change. If you have 100,000 records, only update the progress bar for every 100 or 1000 records, for example. Too many events can also cause the program to stop responding.

If you insert a lot of records, try to use bulk copy. It will dramatically raise the speed of your application. These functions are quite straightforward, you put all your records to be inserted in a datable (with the same schema as the destination table) and call the function with it.

To grab the datatable schema if you're lazy just make a query like "SELECT * FROM tableName WHERE 0=1", the resultset will only contain the tablename schema.

    private static void InsertTable(DataTable dt)
    {
        dt.AcceptChanges();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["MyDB"].ToString()))
            {
                //Destination Table is the same as the source.
                bulkCopy.DestinationTableName = dt.TableName;
                try
                {
                    // Write from the source to the destination.
                    bulkCopy.BulkCopyTimeout = 600;
                    bulkCopy.WriteToServer(dt);
                } 
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                }
            }


    }
    private static void InsertTableWithIdentity(DataTable dt)
    {
        dt.AcceptChanges();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["MyDB"].ToString(), SqlBulkCopyOptions.KeepIdentity))
        {
            //Destination Table is the same as the source.
            bulkCopy.DestinationTableName = dt.TableName;
            try
            {
                // Write from the source to the destination.
                bulkCopy.BulkCopyTimeout = 600;
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
        }
    }

As for why it slows down, it's simple, the time it takes for a query to execute increases exponentially with the number of records. Because it stores in memory the future state of the database and only write it after the commit (in your case the end of the transaction) so use bulkcopy of simply put some more commits.

How are you treating exceptions during the process of insertion?

What kind of data are you inserting? Could it be generating an exception?

You should run your insertion on a separate thread with the option to cancel the operation (rather than forcing shutdown). As suggested by @Mark using something like a BackgroundWorker or just create a separate thread and take a note of it. It seems as though the process is bottlenecking at somepoint you should perhaps look at doing some logging.

Facts:

• you state that it hangs while a few lines after that you state that the application is still processing something and the later you terminate it, the more items are actually processed before it is killed.

Not easy to tell what the problem is without any source code, but I would suspect a slow down due to a memory leak or other performance degradation factor.

A few guesses:

• Are you closing/disposing all your no longer needed database connections? Undisposed database connections can create huge memory leaks and hang the application.

• Have you tried running the application in a memory/performance profiler? (ANTS is great)

• Have you tried attaching a debugger to the application after some time to see where exactly it hangs and whether it hangs at all?

Not related to the progress thing, but are you commiting batches of inserts? This might speed up the process quite a lot (and reduce resources consumption).

First, create a function that changes the Progress Bar, then add a delegate for that function. Create another thread to update the progress bar. It should look like this when you're done.

private delegate void UpdateProgressBarDelegate();

private void UpdateProgressBar()
{
     if (this.progressBar1.InvokeRequired)
     {
         this.progressBar1.Invoke(new UpdateProgressBarDelegate(UpdateProgressBar));
     }
     else
     {
         //code to update progress bar
     }    
}

If you need to include any parameters, you would do so like this:

this.progressBar1.Invoke(new UpdateProgressBarDelegate(UpdateProgressBar), param1, param2);

I use threads for these kind of stuff.

somewhere in my code:

// Definition 
private static Thread TH; 

....

// When process starts
TH = new Thread(new ThreadStart(Splash_MyCallBack)); 
TH.Start();

....

// This method starts the form that shows progress and other data
static private void Splash_MyCallBack()
{
   frmLoading FL;

   FL = new frmLoading();

   FL.ShowDialog();

} /* Splash_MyCallBack*/

// Your process calls Splash_Stop when it is done.
static public void Splash_Stop()
{
   TH.Abort();
} /* Splash_Stop*/

frmLoading performs the visual stuff, while in the background I have a very processor-intensive task. My process reports to an interface its progress. frmLoading implements that interface so it is aware of it and can show whaever it is needed (2 progress bars in my case) Tha only catch is, frmLoading must have this in the constructor:

Control.CheckForIllegalCrossThreadCalls= false;

which may be risky in some scenarios (not my case).

Hope this helps, I can add more stuff if you like.

Regards,

There wasn't a problem after all. The problem was that I was using a virtual machine and therefore it was a bit slow. When I ran this on a Xeon server, the process got completed in about 10 minutes.