sql-server-2005 - servidor - tablas temporales sql server 2016
Cómo cambiar las inserciones parametrizadas lentas en una copia masiva rápida(incluso desde la memoria) (12)
Tenía algo así en mi código (.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();
Con cmdInsert.ExecuteNonQuery () comentado, este código se ejecuta en menos de 2 segundos. Con la ejecución de SQL, demora 1 m 20 seg. Hay alrededor de 0.5 millones de registros. La tabla está vacía antes. La tarea de flujo de datos de SSIS de funcionalidad similar demora alrededor de 20 segundos.
- La inserción masiva no era una opción (ver a continuación). Hice algunas cosas elegantes durante esta importación.
- Mi máquina de prueba es Core 2 Duo con 2 GB de RAM.
- Al buscar en el Administrador de tareas, la CPU no se utilizó por completo. IO tampoco parecía ser utilizado por completo.
- Schema es simple como el infierno: una tabla con AutoInt como índice principal y menos de 10 entradas, minúsculas y caracteres (10).
¡Después de algunas respuestas aquí encontré que es posible ejecutar copias masivas de la memoria ! Me estaba negando a usar copia masiva porque pensé que tenía que hacerse desde el archivo ...
Ahora uso esto y toma aproximadamente 20 segundos (como la tarea de SSIS)
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();
1 minuto suena bastante razonable para 0.5 millones de registros. Eso es un registro cada 0.00012 segundos.
¿La tabla tiene algún índice? Eliminarlos y volver a aplicarlos después de la inserción masiva mejoraría el rendimiento de los insertos, si esa es una opción.
En lugar de insertar cada registro individualmente, intente utilizar la clase SqlBulkCopy para insertar en bloque todos los registros a la vez.
Cree una DataTable y agregue todos sus registros a la DataTable, y luego use SqlBulkCopy . WriteToServer para insertar a granel todos los datos a la vez.
Esto podría lograrse mejor usando algo como el comando bcp. Si no está disponible, las sugerencias anteriores sobre el uso de BULK INSERT son su mejor opción. Realiza 500,000 viajes redondos a la base de datos y escribe 500,000 entradas en los archivos de registro, sin mencionar el espacio que debe asignarse al archivo de registro, la tabla y los índices.
Si está insertando en un orden que es diferente de su índice agrupado, también tiene que lidiar con el tiempo requerido para reorganizar los datos físicos en el disco. Aquí hay muchas variables que podrían hacer que su consulta se ejecute más lentamente de lo que le gustaría.
~ 10,000 transacciones por segundo no es terrible para las inserciones individuales que vienen de ida y vuelta del código /
No me parece irrazonable procesar 8,333 registros por segundo ... ¿qué tipo de rendimiento esperan?
Primero haga las cosas elegantes en los datos, en todos los registros. Luego, insértelas a granel.
(dado que no está haciendo selecciones después de un inserto ... no veo el problema de aplicar todas las operaciones en los datos antes del BulkInsert
Se requiere la transacción? El uso de transacciones necesita muchos más recursos que simples comandos.
Además, si está seguro de que los valores insertados son corect, puede usar un BulkInsert.
Si alguna forma de inserción masiva no es una opción, la otra forma sería múltiples hilos, cada uno con su propia conexión a la base de datos.
El problema con el sistema actual es que tiene 500,000 viajes redondos a la base de datos, y está esperando que se complete el primer viaje de ida y vuelta antes de comenzar el siguiente - cualquier tipo de latencia (es decir, una red entre las máquinas) significará que la mayoría de tu tiempo lo pasas esperando
Si puede dividir el trabajo, quizás usando algún tipo de configuración de productor / consumidor, puede encontrar que puede obtener una mayor utilización de todos los recursos.
Sin embargo, para hacer esto, tendrá que perder la gran transacción; de lo contrario, el primer hilo del escritor bloqueará a todos los demás hasta que se complete su transacción. Aún puede usar transacciones, pero deberá usar muchas pequeñas en lugar de una grande.
El SSIS será rápido porque está usando el método de inserción masiva: realice todo el procesamiento complicado primero, genere la lista final de datos para insertar y dé todo al mismo tiempo para insertar de forma masiva.
Si necesita una mejor velocidad, puede considerar implementar la inserción masiva:
Si tuviera que adivinar, lo primero que buscaría son demasiados o el tipo equivocado de índices en la tabla tbTrafficLogTTL. Sin mirar la definición de esquema para la tabla, realmente no puedo decirlo, pero he experimentado problemas similares de rendimiento cuando:
- La clave principal es un GUID y el índice principal está CLUSTERED.
- Hay algún tipo de índice ÚNICO en un conjunto de campos.
- Hay demasiados índices sobre la mesa.
Cuando comienza a indexar medio millón de filas de datos, el tiempo dedicado a crear y mantener índices se suma.
También observaré que si tiene alguna opción para convertir los campos Año, Mes, Día, Hora, Minuto, Segundo en un solo campo fecha y hora2 o marca de hora, debería hacerlo. Está agregando mucha complejidad a su arquitectura de datos, sin ganancia. La única razón por la que incluso consideraría utilizar una estructura de campos divididos como esa es si se trata de un esquema de base de datos preexistente que no se puede cambiar por ningún motivo. En cuyo caso, apesta ser tú.
Supongo que lo que está tomando aproximadamente los 58 segundos es la inserción física de 500,000 registros, por lo que está recibiendo alrededor de 10,000 inserciones por segundo. Sin conocer las especificaciones de la máquina del servidor de la base de datos (veo que está utilizando el host local, por lo que los retrasos en la red no deberían ser un problema), es difícil decir si esto es bueno, malo o abismal.
Me gustaría ver el esquema de su base de datos: ¿hay un montón de índices en la tabla que deben actualizarse después de cada inserción? Esto podría ser de otras tablas con claves externas que hacen referencia a la tabla en la que está trabajando. Existen herramientas de creación de perfiles SQL y funciones de supervisión del rendimiento integradas en SQL Server, pero nunca las he usado. Pero pueden aparecer problemas como bloqueos y cosas por el estilo.
Tuve un problema similar en mi último contrato. Estás haciendo 500,000 viajes a SQL para insertar tus datos. Para un aumento espectacular en el rendimiento, desea investigar el método BulkInsert en el espacio de nombres SQL. Tenía procesos de "recarga" que iban desde más de 2 horas para restaurar un par de docenas de tablas hasta 31 segundos una vez que implementé la importación masiva.