blah blah blah is here! blah blah » Close

0
dbiggied
0
375 views

Faster database INSERTs???

up0down
link

I'm working on some code in C# that will run a query against a remote Sybase database and then insert those records into a local Access database. It works just fine, the problem is that it is SLOW!...it takes 15 seconds to insert 150 records. I've tried using DataAdapter.Update, I've tried using commandBuilder or writing my own insert query, I've tried using dataReader, I even tried writing an insert query and call executeNonQuery for each record in a loop. They are all about the same. When I run the full process to insert thousands of records, the speed will be totally unacceptable. There has to be a better way! Help!!!

add comment
last answered one year ago

1 answers

Sort:
up0down
link

try using bulk insert:

public static void BulkInsert(DataTable dataTable)
        {
            string connectionString="DataSource=...;...";
            using (SqlConnection connection =
                    new SqlConnection(connectionString))
            {
                // make sure to enable triggers
                // more on triggers in next post
                SqlBulkCopy bulkCopy =
                    new SqlBulkCopy
                    (
                    connection,
                    SqlBulkCopyOptions.TableLock |
                    SqlBulkCopyOptions.FireTriggers |
                    SqlBulkCopyOptions.UseInternalTransaction,
                    null
                    );

                // set the destination table name
                bulkCopy.DestinationTableName = dataTable.TableName;
                connection.Open();

                // write the data in the "dataTable"
                bulkCopy.WriteToServer(dataTable);
                connection.Close();
            }
            // reset
            dataTable.Clear();

        }


if the id is auto increment, add a column to the table of the name of id field so autoincrement will be applied. Also set the datatable name to the name of the db table.

add comment
Feedback