Maneira mais rápida para inserir 30 mil linhas em uma tabela temporária no SQL Server com c #

Eu estou tentando descobrir como posso melhorar meu desempenho de inserção em uma tabela temporária no SQL Server usando c #. Algumas pessoas estão dizendo que eu deveria usar o SQLBulkCopy, mas devo estar fazendo algo errado, pois parece funcionar muito mais lento do que simplesmente construir uma string de inserção SQL.

Meu código para criar tabela usando o SQLBulkCopy está abaixo:

public void MakeTable(string tableName, List 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); } } 

Dessa forma, minhas inserções demoram muito para serem executadas. Eu consegui minhas inserções para trabalhar mais rápido de outra maneira:

Eu criei o bit inserções como uma string e juntei-o na minha declaração SQL create temp table:

Criação de sequência de inserções:

 public string prepareInserts(string tableName, List ids) { List inserts = new List(); var total = ids.Select(p => p).Count(); var size = 1000; var insert = 1; var skip = size * (insert - 1); var canPage = skip  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; } 

Usando-os na instrução SQL após criar a 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); ... 

Desde que vi pessoas me dizendo (em troca de pilha https://dba.stackexchange.com/questions/44217/fastest-way-to-insert-30-thousand-rows-in-sql-server/44222?noredirect= 1 # comment78137_44222 ) Eu deveria usar SQLBulkCopy e isso seria mais rápido, eu acredito que eu deveria melhorar o jeito que eu faço. Portanto, se alguém puder sugerir como posso melhorar meu código SQLBulkCopy OU informe se há uma instrução de inserção melhor que possa melhorar o desempenho de meu aplicativo, o que seria ótimo.

Seu problema pode estar em localTempTable.AcceptChanges(); Desde que comete suas alterações.
Se você fizer o próximo, acho que vai rodar mais 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); } 

Da MSDN – DataSet.AcceptChanges

Confirma todas as alterações feitas neste DataSet desde que foi carregado ou desde a última vez que AcceptChanges foi chamado.

Eu mesmo executo esse código com objects StopWatch para medir o tempo. É o AcceptChanges em cada iteração que faz ir devagar.

 public void MakeTable(string tableName, List 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 quando o AccpetChanges está dentro do loop foreach

insira a descrição da imagem aqui

E quando não é

insira a descrição da imagem aqui

A diferença é de 3 ordens de magnitude 🙂

Use IDataReader e ele será executado ainda mais rápido

em vez de cmd.ExecuteNonQuery(); Executar

 cmd.ExecuteReader()