Cannot Use System While .Net Application Processes SQL Statements

Question

Hey-

I'm using a MySQL connector in VB .Net to execute a batch of SQL inserts. This is typically on the order of 5k statements and takes around 30 minutes to process. Unfortunately, when this process is running and I use a different application on the system, upon returning to the .net app it hangs and shows "not responding". In fact, as soon as I click anywhere else in the application (move to a different tab, for example) everything locks up.

Dim transaction As MySqlTransaction = sqlConnection.BeginTransaction()
For Each sqlCmd In (sqlCmdsCollection)
    sqlCommand = New MySqlCommand(sqlCmd, sqlConnection)
    Try
        sqlCommand.ExecuteNonQuery()
        logTxtBox.AppendText(". ")
    Catch ex As Exception
        transaction.Rollback()
        logTxtBox.AppendText(vbNewLine & "EXCEPTION: " & ex.Message & vbNewLine)
        logTxtBox.AppendText(sqlCmd & vbNewLine)
        logTxtBox.AppendText("INFO: No changes were made to the database!"& vbNewLine)
    End Try
Next
transaction.Commit()

Why is this happening?

Is there a more efficient way to execute these inserts?

Thanks-

Jonathan

Answer 1

Sounds like maybe by using another application your system is stealing processing time from a local mysql instance and your app which is ultimately causing mysql to timeout.

There are a couple of ways to "fix" this:

1. upgrade your local machine, maybe a dual or quad core with sufficient ram.
2. move the processing to a different machine for execution.
3. Evaluate your queries to determine why 5000 of them take 30 minutes, that is a really long time.

Answer 2

This is because you are calling this long running operation on the UI thread, which means it's blocking and you cannot use the UI whilst this is running.

For short running operations, you can create a delegate which handles the work and invoke that asynchronously and use Control.BeginInvoke to ensure you update the UI on the UI thread.

Anything longer than a few seconds, and as Adam points out, you're better off creating your own Thread and handling your work there.

Having said that, 5000 inserts should really NOT take 30 minutes to run. But that's another question in its own right.

Answer 3

Other answers are suggesting manually creating a new thread. I think you could just use SqlCommand.BeginExecuteNonQuery which does it all for you? It seems the MySQL driver supports BeginExecuteNonQuery.