program mssql exportar example performance sql-server-2008 bulkinsert tvp

performance - mssql - Rendimiento de bcp/BULK INSERT frente a los parámetros con valores de tabla



mssql server bcp (4)

¡Las tablas de etapas son buenas! Realmente no quisiera hacerlo de otra manera. ¿Por qué? Debido a que las importaciones de datos pueden cambiar inesperadamente (y a menudo en formas que no puede prever, como el momento en que las columnas todavía se llamaban nombre y apellido pero tenían los datos del primer nombre en la columna del apellido, por ejemplo, para elegir un ejemplo que al azar.) Fácil de investigar el problema con una tabla de etapas para que pueda ver exactamente qué datos estaban en las columnas manejadas por la importación. Más difícil de encontrar, creo que cuando utilizas una tabla en la memoria. Conozco a mucha gente que hace importaciones para ganarse la vida como yo y todos ellos recomiendan usar tablas de etapas. Sospecho que hay una razón para esto.

Además, la fijación de un pequeño cambio de esquema a un proceso de trabajo es más fácil y requiere menos tiempo que el rediseño del proceso. Si está funcionando y nadie está dispuesto a pagar durante horas para cambiarlo, solo arregle lo que se debe corregir debido al cambio de esquema. Al cambiar todo el proceso, introduce muchos más posibles nuevos errores que realizando un pequeño cambio en un proceso de trabajo existente y probado.

¿Y cómo vas a terminar con todas las tareas de limpieza de datos? Puede estar haciéndolos de manera diferente, pero aún deben hacerse. De nuevo, cambiar el proceso de la manera que describes es muy arriesgado.

Personalmente, me parece que te ofende usar técnicas más antiguas en lugar de tener la oportunidad de jugar con juguetes nuevos. Parece que no tiene ninguna base real para querer cambiar, salvo que la inserción masiva es tan 2000.

Estoy a punto de tener que volver a escribir un código bastante antiguo utilizando el comando BULK INSERT SQL Server porque el esquema ha cambiado, y se me ocurrió que tal vez debería pensar en cambiar a un procedimiento almacenado con un TVP, pero me pregunto qué efecto podría tener en el rendimiento.

Algunos antecedentes que podrían ayudar a explicar por qué hago esta pregunta:

  • Los datos realmente llegan a través de un servicio web. El servicio web escribe un archivo de texto en una carpeta compartida en el servidor de la base de datos que a su vez realiza un BULK INSERT . Este proceso se implementó originalmente en SQL Server 2000, y en ese momento no había otra alternativa que lanzar unas pocas declaraciones INSERT en el servidor, que en realidad era el proceso original y era un desastre de rendimiento.

  • Los datos se insertan a granel en una tabla de etapas permanente y luego se combinan en una tabla mucho más grande (después de lo cual se elimina de la tabla de etapas).

  • La cantidad de datos para insertar es "grande", pero no "enorme", generalmente unos cientos de filas, quizás 5-10k filas en raras ocasiones. Por lo tanto, mi intuición es que BULK INSERT es una operación no registrada que no hará una gran diferencia (pero por supuesto no estoy seguro, de ahí la pregunta).

  • La inserción es en realidad parte de un proceso por lotes canalizado mucho más grande y debe suceder muchas veces en sucesión; por lo tanto, el rendimiento es crítico.

Las razones por las que me gustaría reemplazar el BULK INSERT con un TVP son:

  • Escribir el archivo de texto en NetBIOS probablemente ya esté costando tiempo, y es bastante horrible desde una perspectiva arquitectónica.

  • Creo que la tabla de etapas puede (y debe) eliminarse. La razón principal por la que está allí es que los datos insertados deben usarse para un par de otras actualizaciones al mismo tiempo de la inserción, y es mucho más costoso intentar la actualización desde la tabla de producción masiva que utilizar una puesta en escena casi vacía. mesa. Con un TVP, el parámetro básicamente es la tabla de etapas, puedo hacer cualquier cosa que quiera con ella antes / después de la inserción principal.

  • Podría deshacerme de la verificación de dupe, el código de limpieza y toda la sobrecarga asociada con las inserciones en bloque.

  • No hay necesidad de preocuparse por la contención de bloqueo en la tabla de etapas o tempdb si el servidor recibe algunas de estas transacciones a la vez (tratamos de evitarlo, pero sucede).

Obviamente voy a hacer un perfil de esto antes de poner algo en producción, pero pensé que sería una buena idea preguntar primero antes de pasar todo ese tiempo, ver si alguien tiene advertencias severas acerca del uso de TVP para este propósito.

Entonces, para cualquiera que sea lo suficientemente acogedor con SQL Server 2008 para haber intentado o al menos haber investigado esto, ¿cuál es el veredicto? Para inserciones de, digamos, algunos cientos o miles de filas, que ocurren con bastante frecuencia, ¿los TVP cortan la mostaza? ¿Hay una diferencia significativa en el rendimiento en comparación con los insertos a granel?

Actualización: ¡ahora con 92% menos signos de interrogación!

(AKA: resultados de la prueba)

El resultado final ahora está en producción después de lo que parece un proceso de implementación de 36 etapas. Ambas soluciones fueron ampliamente probadas:

  • SqlBulkCopy del código de la carpeta compartida y uso de la clase SqlBulkCopy directamente;
  • Cambiar a un procedimiento almacenado con TVP.

Para que los lectores puedan hacerse una idea de qué se probó exactamente, para disipar cualquier duda sobre la confiabilidad de estos datos, aquí hay una explicación más detallada de lo que realmente hace este proceso de importación:

  1. Comience con una secuencia de datos temporales que normalmente tiene alrededor de 20-50 puntos de datos (aunque a veces puede ser de unos cientos);

  2. Haga un montón de procesamiento loco que es en su mayoría independiente de la base de datos. Este proceso está paralelizado, por lo que aproximadamente 8-10 de las secuencias en (1) se están procesando al mismo tiempo. Cada proceso paralelo genera 3 secuencias adicionales.

  3. Tome las 3 secuencias y la secuencia original y combínelas en un lote.

  4. Combine los lotes de todas las 8-10 tareas de procesamiento finalizadas en un gran súper lote.

  5. Importe utilizando la estrategia BULK INSERT (consulte el paso siguiente) o la estrategia TVP (vaya al paso 8).

  6. Use la clase SqlBulkCopy para volcar todo el súper lote en 4 tablas permanentes.

  7. Ejecute un Procedimiento almacenado que (a) realice una serie de pasos de agregación en 2 de las tablas, incluidas varias condiciones JOIN , y luego (b) realice una MERGE en 6 tablas de producción utilizando tanto los datos agregados como los no agregados. (Terminado)

    O

  8. Genera 4 objetos DataTable que contienen los datos que se fusionarán; Tres de ellos contienen tipos de CLR que, desafortunadamente, no son compatibles con ADO.NET TVP, por lo que deben incluirse como representaciones de cadenas, lo que perjudica el rendimiento un poco.

  9. Alimente los TVP a un Procedimiento almacenado, que básicamente hace el mismo procesamiento que (7), pero directamente con las tablas recibidas. (Terminado)

Los resultados fueron razonablemente cercanos, pero el enfoque de TVP finalmente tuvo un mejor desempeño en promedio, incluso cuando los datos excedieron las 1000 filas por una pequeña cantidad.

Tenga en cuenta que este proceso de importación se ejecuta miles de veces seguidas, por lo que era muy fácil obtener un tiempo promedio simplemente contando cuántas horas (sí, horas) le llevó terminar todas las fusiones.

Originalmente, una fusión promedio tomó casi exactamente 8 segundos para completarse (bajo carga normal). La eliminación de NetBIOS kludge y el cambio a SqlBulkCopy redujo el tiempo a casi exactamente 7 segundos. El cambio a TVP redujo aún más el tiempo a 5.2 segundos por lote. Eso es una mejora del 35% en el rendimiento de un proceso cuyo tiempo de ejecución se mide en horas, por lo que no está nada mal. También es una mejora del ~ 25% sobre SqlBulkCopy .

De hecho, estoy bastante seguro de que la verdadera mejora fue significativamente más que esto. Durante las pruebas, se hizo evidente que la fusión final ya no era la ruta crítica; en cambio, el servicio web que estaba haciendo todo el procesamiento de datos empezaba a ceder ante la cantidad de solicitudes que ingresaban. Ni la CPU ni la E / S de la base de datos estaban realmente al máximo, y no había una actividad de bloqueo significativa. En algunos casos, vimos una brecha de unos pocos segundos inactivos entre fusiones sucesivas. Hubo un pequeño espacio, pero mucho más pequeño (medio segundo o menos) al usar SqlBulkCopy . Pero supongo que se convertirá en una historia para otro día.

Conclusión: Los parámetros con valores de tabla realmente funcionan mejor que las operaciones BULK INSERT para procesos complejos de importación + transformación que operan en conjuntos de datos medianos.

Me gustaría agregar otro punto, solo para mitigar cualquier aprensión de parte de la gente que está pro-etapas-tablas. En cierto modo, todo este servicio es un proceso de puesta en escena gigante. Cada paso del proceso está fuertemente auditado, por lo que no necesitamos una tabla de etapas para determinar por qué fracasó una fusión en particular (aunque en la práctica casi nunca sucede). Todo lo que tenemos que hacer es establecer un indicador de depuración en el servicio y se dividirá en el depurador o volcar sus datos a un archivo en lugar de a la base de datos.

En otras palabras, ya tenemos información más que suficiente sobre el proceso y no necesitamos la seguridad de una tabla de etapas; la única razón por la que teníamos la tabla de etapas en primer lugar era para evitar complicar todas las INSERT y UPDATE que de otro modo hubiéramos tenido que usar. En el proceso original, los datos de estadificación solo vivieron en la tabla de etapas durante fracciones de segundo de todos modos, por lo que no agregó ningún valor en términos de mantenimiento / mantenimiento.

También tenga en cuenta que no hemos reemplazado cada operación BULK INSERT con TVP. Varias operaciones que se ocupan de grandes cantidades de datos y / o no necesitan hacer nada especial con los datos que no sean tirarlos al DB todavía usan SqlBulkCopy . No estoy sugiriendo que los TVP sean una panacea de rendimiento, solo que tuvieron éxito sobre SqlBulkCopy en esta instancia específica que involucra varias transformaciones entre la puesta en escena inicial y la fusión final.

Entonces ahí lo tienes. Point va a TToni para encontrar el enlace más relevante, pero aprecio las otras respuestas también. ¡Gracias de nuevo!

Creo que todavía me quedaré con un enfoque de inserción masiva. Puede encontrar que tempdb aún recibe un golpe usando un TVP con un número razonable de filas. Esta es mi corazonada, no puedo decir que haya probado el rendimiento del uso de TVP (aunque estoy interesado en escuchar las opiniones de otros)

No mencionas si utilizas .NET, pero el enfoque que he tomado para optimizar las soluciones anteriores era hacer una carga masiva de datos usando la clase SqlBulkCopy ; no es necesario que escribas los datos en un archivo antes. cargando, simplemente dale a la clase SqlBulkCopy (por ej.) una DataTable - esa es la forma más rápida de insertar datos en la base de datos. 5-10K filas no es mucho, lo he usado para hasta 750K filas. Sospecho que, en general, con unos cientos de filas, no sería una gran diferencia usar un TVP. Pero escalar sería en mi humilde opinión limitada.

Tal vez la nueva funcionalidad MERGE en SQL 2008 podría beneficiarlo?

Además, si su tabla de etapas existente es una tabla única que se usa para cada instancia de este proceso y le preocupa la contención, etc., ¿ha considerado crear una nueva tabla de etapas "física" pero física cada vez, y luego descartarla cuando esté ¿acabado con?

Tenga en cuenta que puede optimizar la carga en esta tabla de etapas, rellenándola sin ningún índice. Luego, una vez rellenado, agregue los índices requeridos en ese punto (FILLFACTOR = 100 para un rendimiento de lectura óptimo, ya que en este punto no se actualizará).

El cuadro mencionado con respecto al enlace provisto en la respuesta de @ TToni debe tomarse en contexto. No estoy seguro de cuánta investigación real fue en esas recomendaciones (también tenga en cuenta que el gráfico parece estar disponible únicamente en las versiones 2008 R2 2008 y 2008 R2 de esa documentación).

Por otro lado, está este documento técnico del equipo de asesoramiento al cliente de SQL Server: Maximización del rendimiento con TVP

He estado usando TVP desde 2009 y he descubierto, al menos en mi experiencia, que para cualquier otra cosa que no sea simplemente insertar en una tabla de destino sin necesidad lógica adicional (lo que rara vez ocurre), los TVP suelen ser la mejor opción.

Tiendo a evitar tablas de etapas ya que la validación de datos debe hacerse en la capa de la aplicación. Mediante el uso de TVP, esto se acomoda fácilmente y la Variable de tabla TVP en el procedimiento almacenado es, por su propia naturaleza, una tabla de etapas localizada (por lo tanto no hay conflicto con otros procesos que se ejecutan al mismo tiempo como cuando se usa una tabla real para organizar )

Con respecto a las pruebas realizadas en la Pregunta, creo que se podría demostrar que es incluso más rápido que lo que se encontró originalmente:

  1. No debe utilizar una DataTable, a menos que su aplicación tenga uso fuera del envío de los valores al TVP. El uso de la IEnumerable<SqlDataRecord> es más rápido y utiliza menos memoria ya que no está duplicando la colección en la memoria solo para enviarla a la base de datos. Tengo esto documentado en los siguientes lugares:
  2. Los TVP son variables de tabla y, como tales, no mantienen estadísticas. Es decir, informan que solo tienen 1 fila para Query Optimizer. Entonces, en tu proceso, o bien:
    • Use la compilación de nivel de estado en cualquier consulta que use el TVP para cualquier otra cosa que no sea un simple SELECCIONAR: OPTION (RECOMPILE)
    • Cree una tabla temporal local (es decir, # único) y copie los contenidos de la TVP en la tabla temporal

En realidad, todavía no tengo experiencia con TVP, sin embargo, hay una buena tabla de comparación de rendimiento vs. BULK INSERT en MSDN here .

Dicen que BULK INSERT tiene un mayor costo de inicio, pero es más rápido a partir de entonces. En un escenario de cliente remoto dibujan la línea en alrededor de 1000 filas (para lógica de servidor "simple"). A juzgar por su descripción, diría que debería estar bien con el uso de TVP. El rendimiento alcanzado, si lo hay, es probablemente insignificante y los beneficios arquitectónicos parecen muy buenos.

Editar: en una nota lateral puede evitar el archivo local del servidor y aún usar copia masiva mediante el uso del objeto SqlBulkCopy. Complete un DataTable y aliméntelo en el método "WriteToServer" de una instancia de SqlBulkCopy. Fácil de usar, y muy rápido.