insert data to db in loop performance

Question

Hello, I'm trying to write Windows app to get data from From Fox Pro DB, and inset it to Sql Server 2008 db.

I wrote code, but it works very slow. rowsCount more than 1 million My code below:

OleDbConnection cn = new OleDbConnection(foxProConnectionString);
SqlConnection cSql = new SqlConnection(sqlServerConnectionString);
cSql.Open();

OleDbCommand ocmd = new OleDbCommand("Select * from " + table, cn);
OleDbDataAdapter sda = new OleDbDataAdapter(ocmd);
DataTable dt = new DataTable();
sda.Fill(dt);
SqlCommand mySqlcmd = new SqlCommand();
mySqlcmd.Connection = cSql;

for (int i = 0; i < dt.Rows.Count; i++)
{
   mySqlcmd.CommandText = "INSERT INTO sqlTable (column1, column2, column3) VALUES ("+dt.Rows[i][dt.Columns.IndexOf("column1")] + ",'" 
                                           + DoOper1(dt.Rows[i]dt.Columns.IndexOf("column2")]) + "','"
                                           + dt.Rows[i][dt.Columns.IndexOf("column3")] + "')";

   mySqlcmd.ExecuteNonQuery();
}

I cant use bulk copy, database columns order may be different in some source tables. Also I get error:

The CLR has been unable to transition from COM context 0x54afe8 to COM context 0x54b158 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

how can I solve a set of my problem?

Answer 1

I think there are ways to get around your problems with Bulk Copy and I suggest you/we work through those rather than try to examine why a slow technology is slow. You say the reasons you can't use Bulk Copy are because the database column order may be different in some source tables. My response to that is, why does that matter? If you are using the SqlBulkCopy object, don't you have total control over how the columns in the source tables map to the columns in the destination tables? (Have you tried using the SqlBulkCopy object?) You also say that you can an error message about an unresponsive program. It looks like it's related to COM. I don't see why that would be a problem with Bulk Copy over something that takes even longer to execute. If necessary, you may have to perform Bulk Copy operations in smaller batches (perhaps completely finishing/committing batches before beginning the next?) in order for your program not to "lose control" over its execution. Does this help or provoke any further questions that may lead to an answer?

Edit: Can you do something like this?

System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy("...");

// Begin a loop to process managable-size batches of source data.

using (System.Data.DataTable dtTarget = new System.Data.DataTable("sqlTable"))
{

   // Populate dtTarget with the data as it should appear
   // on the SQL Server side.
   // If the mapping is simple, you may be able to use
   // bc.ColumnMappings instead of manually re-mapping.

   bc.DestinationTableName = "sqlTable";
   bc.WriteToServer(dtTarget);
}

// End loop.