How to change slow parametrized inserts into fast bulk copy (even from memory)
bulk, copy, insert, performance, sql-server-2005
Solution
Instead of inserting each record individually, Try using the SqlBulkCopy class to bulk insert all the records at once.
Create a DataTable and add all your records to the DataTable, and then use SqlBulkCopy.WriteToServer to bulk insert all the data at once.
Problem
I had someting like this in my code (.Net 2.0, MS SQL) ``` SqlConnection connection = new SqlConnection(@"Data Source=localhost;Initial Catalog=DataBase;Integrated Security=True"); connection.Open(); SqlCommand cmdInsert = connection.CreateCommand(); SqlTransaction sqlTran = connection.BeginTransaction(); cmdInsert.Transaction = sqlTran; cmdInsert.CommandText = @"INSERT INTO MyDestinationTable" + "(Year, Month, Day, Hour, ...) " + "VALUES " + "(@Year, @Month, @Day, @Hour, ...) "; cmdInsert.Parameters.Add("@Year", SqlDbType.SmallInt); cmdInsert.Parameters.Add("@Month", SqlDbType.TinyInt); cmdInsert.Parameters.Add("@Day", SqlDbType.TinyInt); // more fields here cmdInsert.Prepare(); Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read); StreamReader reader = new StreamReader(stream); char[] delimeter = new char[] {' '}; String[] records; while (!reader.EndOfStream) { records = reader.ReadLine().Split(delimeter, StringSplitOptions.None); cmdInsert.Parameters["@Year"].Value = Int32.Parse(records[0].Substring(0, 4)); cmdInsert.Parameters["@Month"].Value = Int32.Parse(records[0].Substring(5, 2)); cmdInsert.Parameters["@Day"].Value = Int32.Parse(records[0].Substring(8, 2)); // more here complicated stuff here cmdInsert.ExecuteNonQuery() } sqlTran.Commit(); connection.Close(); ``` With cmdInsert.ExecuteNonQuery() commented out this code executes in less than 2 sec. With SQL execution it takes 1m 20 sec. There are around 0.5 milion records. Table is emptied before. SSIS data flow task of similar functionality takes around 20 sec. - Bulk Insert was not an option (see below). I did some fancy stuff during this import. - My test machine is Core 2 Duo with 2 GB RAM. - When looking in Task Manager CPU was not fully untilized. IO seemed also not to be fully utilized. - Schema is simple like hell: one table with AutoInt as primary index and less than 10 ints, tiny ints and chars(10). After some answers here I found that it is possible to execute bulk copy from memory! I was refusing to use bulk copy beacuse I thought it has to be done from file... Now I use this and it takes aroud 20 sec (like SSIS task) ``` DataTable dataTable = new DataTable(); dataTable.Columns.Add(new DataColumn("ixMyIndex", System.Type.GetType("System.Int32"))); dataTable.Columns.Add(new DataColumn("Year", System.Type.GetType("System.Int32"))); dataTable.Columns.Add(new DataColumn("Month", System.Type.GetType("System.Int32"))); dataTable.Columns.Add(new DataColumn("Day", System.Type.GetType("System.Int32"))); // ... and more to go DataRow dataRow; object[] objectRow = new object[dataTable.Columns.Count]; Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read); StreamReader reader = new StreamReader(stream); char[] delimeter = new char[] { ' ' }; String[] records; int recordCount = 0; while (!reader.EndOfStream) { records = reader.ReadLine().Split(delimeter, StringSplitOptions.None); dataRow = dataTable.NewRow(); objectRow[0] = null; objectRow[1] = Int32.Parse(records[0].Substring(0, 4)); objectRow[2] = Int32.Parse(records[0].Substring(5, 2)); objectRow[3] = Int32.Parse(records[0].Substring(8, 2)); // my fancy stuf goes here dataRow.ItemArray = objectRow; dataTable.Rows.Add(dataRow); recordCount++; } SqlBulkCopy bulkTask = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null); bulkTask.DestinationTableName = "MyDestinationTable"; bulkTask.BatchSize = dataTable.Rows.Count; bulkTask.WriteToServer(dataTable); bulkTask.Close(); ```