La forma más rápida de insert 30 mil filas en una tabla temporal en SQL Server con C #

Estoy intentando descubrir cómo puedo mejorar el performance de mi inserción en una tabla temporal en SQL Server usando c #. Algunas personas dicen que debería usar SQLBulkCopy, pero debo estar haciendo algo mal, ya que parece funcionar mucho más lento que simplemente crear una cadena de inserción SQL.

Mi código para crear una tabla usando SQLBulkCopy está a continuación:

public void MakeTable(string tableName, List<string> ids, SqlConnection connection) { SqlCommand cmd = new SqlCommand("CREATE TABLE ##" + tableName + " (ID int)", connection); cmd.ExecuteNonQuery(); DataTable localTempTable = new DataTable(tableName); DataColumn id = new DataColumn(); id.DataType = System.Type.GetType("System.Int32"); id.ColumnName = "ID"; localTempTable.Columns.Add(id); foreach (var item in ids) { DataRow row = localTempTable.NewRow(); row[0] = item; localTempTable.Rows.Add(row); localTempTable.AcceptChanges(); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "##" + tableName; bulkCopy.WriteToServer(localTempTable); } } 

De esta forma, mis inserciones tardan mucho time en ejecutarse. Tengo mis insertos para trabajar más rápido de otra manera:

Creé el bit de inserciones como una cadena y me uní a él en mi statement de tabla de creación de SQL:

Creación de cadena de inserciones:

 public string prepareInserts(string tableName, List<string> ids) { List<string> inserts = new List<string>(); var total = ids.Select(p => p).Count(); var size = 1000; var insert = 1; var skip = size * (insert - 1); var canPage = skip < total; while (canPage) { inserts.Add(" insert into ##" + tableName + @" (ID) values " + String.Join(",", ids.Select(p => string.Format("({0})", p)) .Skip(skip) .Take(size) .ToArray())); insert++; skip = size * (insert - 1); canPage = skip < total; } string joinedInserts = String.Join("\r\n", inserts.ToArray()); return joinedInserts; } 

Utilizándolos en la statement SQL después de crear una consulta:

 inserts = prepareInserts(tableName, ids); var query = @"IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##" + tableName + @"') ) BEGIN DELETE FROM ##" + tableName + @" END ELSE BEGIN CREATE TABLE ##" + tableName + @" (ID int) END " + inserts; var command = new SqlCommand(query, sqlConnection); ... 

Como he visto gente diciéndome (en el intercambio de fichas https://dba.stackexchange.com/questions/44217/fastest-way-to-insert-30-thousand-rows-in-sql-server/44222?nonetworkingirect= 1 # comment78137_44222 ) Que debería usar SQLBulkCopy y que sería más rápido, creo que debería mejorar la forma en que lo hago. Entonces, si alguien puede sugerirme cómo puedo mejorar mi código de SQLBulkCopy O decirme si hay una mejor statement de inserción que pueda mejorar el performance de mi aplicación que sería genial.

Su problema puede estar en localTempTable.AcceptChanges(); Dado que compromete sus cambios.
Si lo haces el siguiente, creo que correrá más rápido

  foreach (var item in ids) { DataRow row = localTempTable.NewRow(); row[0] = item; localTempTable.Rows.Add(row); } localTempTable.AcceptChanges(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "##" + tableName; bulkCopy.WriteToServer(localTempTable); } 

De MSDN – DataSet.AcceptChanges

Confirma todos los cambios realizados en este DataSet desde que se cargó o desde la última vez que se llamó a AcceptChanges.

Ejecuto este código con objects StopWatch para medir el time. Es el AcceptChanges en cada iteración que hace que vaya lento.

 public void MakeTable(string tableName, List<string> ids, SqlConnection connection) { SqlCommand cmd = new SqlCommand("CREATE TABLE ##" + tableName + " (ID int)", connection); cmd.ExecuteNonQuery(); DataTable localTempTable = new DataTable(tableName); DataColumn id = new DataColumn(); id.DataType = System.Type.GetType("System.Int32"); id.ColumnName = "ID"; localTempTable.Columns.Add(id); System.Diagnostics.Stopwatch sw1 = new System.Diagnostics.Stopwatch(); sw1.Start(); foreach (var item in ids) { DataRow row = localTempTable.NewRow(); row[0] = item; localTempTable.Rows.Add(row); } localTempTable.AcceptChanges(); long temp1 = sw1.ElapsedMilliseconds; sw1.Reset(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "##" + tableName; bulkCopy.WriteToServer(localTempTable); } long temp2 = sw1.ElapsedMilliseconds; } 

Resultado cuando AccpetChanges está dentro de foreach loop

enter image description here

Y cuando no es

enter image description here

La diferencia es 3 órdenes de magnitud 🙂

Usa IDataReader y se ejecutará aún más rápido

en lugar de cmd.ExecuteNonQuery(); Ejecutar

 cmd.ExecuteReader()