Inserte 2 millones de filas en SQL Server rápidamente
Tengo que insertar unos 2 millones de filas de un archivo de texto.
Y al insertar tengo que crear algunas tablas maestras.
¿Cuál es la mejor y más rápida manera de insertar un conjunto tan grande de datos en SQL Server?
Creo que es mejor que leas los datos del archivo de texto en DataSet
Pruebe SqlBulkCopy : inserción masiva en SQL desde la aplicación C#
// connect to SQL using (SqlConnection connection = new SqlConnection(connString)) { // 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 = this.tableName; connection.Open(); // write the data in the "dataTable" bulkCopy.WriteToServer(dataTable); connection.Close(); } // reset this.dataTable.Clear();
o
después de hacer el paso 1 en la parte superior
- Crear XML a partir de un conjunto de datos
- Pase XML a la base de datos y realice una inserción masiva
Puede consultar este artículo para obtener más detalles: Inserción masiva de datos utilizando C# DataTable y la función OpenXML del servidor SQL.
Pero no se ha probado con 2 millones de registros, solo consumirá memoria en la máquina, ya que hay que cargar 2 millones de registros e insertarlos.
Puedes intentarlo con SqlBulkCopyclase.
Le permite cargar de forma masiva y eficiente una tabla de SQL Server con datos de otra fuente.
Hay una publicación de blog interesante sobre cómo usarlo.
Re la solución para SqlBulkCopy:
Utilicé StreamReader para convertir y procesar el archivo de texto. El resultado fue una lista de mi objeto.
Creé una clase que toma Datatableo List<T>un tamaño de búfer ( CommitBatchSize). Convertirá la lista en una tabla de datos usando una extensión (en la segunda clase).
Funciona muy rápido. En mi PC puedo insertar más de 10 millones de registros complicados en menos de 10 segundos.
Aquí está la clase:
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
public class BulkUploadToSql<T>
{
public IList<T> InternalStore { get; set; }
public string TableName { get; set; }
public int CommitBatchSize { get; set; }=1000;
public string ConnectionString { get; set; }
public void Commit()
{
if (InternalStore.Count>0)
{
DataTable dt;
int numberOfPages = (InternalStore.Count / CommitBatchSize) + (InternalStore.Count % CommitBatchSize == 0 ? 0 : 1);
for (int pageIndex = 0; pageIndex < numberOfPages; pageIndex++)
{
dt= InternalStore.Skip(pageIndex * CommitBatchSize).Take(CommitBatchSize).ToDataTable();
BulkInsert(dt);
}
}
}
public void BulkInsert(DataTable dt)
{
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 = TableName;
connection.Open();
// write the data in the "dataTable"
bulkCopy.WriteToServer(dt);
connection.Close();
}
// reset
//this.dataTable.Clear();
}
}
public static class BulkUploadToSqlHelper
{
public static DataTable ToDataTable<T>(this IEnumerable<T> data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
}
}
Aquí hay un ejemplo cuando quiero insertar una Lista de mi objeto personalizado List<PuckDetection>( ListDetections):
var objBulk = new BulkUploadToSql<PuckDetection>()
{
InternalStore = ListDetections,
TableName= "PuckDetections",
CommitBatchSize=1000,
ConnectionString="ENTER YOU CONNECTION STRING"
};
objBulk.Commit();
La BulkInsertclase se puede modificar para agregar mapeo de columnas si es necesario. Por ejemplo, tiene una clave de identidad como primera columna (esto supone que los nombres de las columnas en la tabla de datos son los mismos que los de la base de datos).
//ADD COLUMN MAPPING
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
Yo uso la utilidad bcp. (Programa de copia masiva) Cargo alrededor de 1,5 millones de registros de texto cada mes. Cada registro de texto tiene 800 caracteres de ancho. En mi servidor, se necesitan unos 30 segundos para agregar 1,5 millones de registros de texto a una tabla de SQL Server.
Las instrucciones para bcp se encuentran en http://msdn.microsoft.com/en-us/library/ms162802.aspx
Probé este método y redujo significativamente el tiempo de ejecución de la inserción de mi base de datos.
List<string> toinsert = new List<string>();
StringBuilder insertCmd = new StringBuilder("INSERT INTO tabblename (col1, col2, col3) VALUES ");
foreach (var row in rows)
{
// the point here is to keep values quoted and avoid SQL injection
var first = row.First.Replace("'", "''")
var second = row.Second.Replace("'", "''")
var third = row.Third.Replace("'", "''")
toinsert.Add(string.Format("( '{0}', '{1}', '{2}' )", first, second, third));
}
if (toinsert.Count != 0)
{
insertCmd.Append(string.Join(",", toinsert));
insertCmd.Append(";");
}
using (MySqlCommand myCmd = new MySqlCommand(insertCmd.ToString(), SQLconnectionObject))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
*Cree un objeto de conexión SQL y reemplácelo donde escribí SQLconnectionObject.
