una - insertar, modificar, eliminar y consultar registros en sql desde c#

¿Cuál es la forma más rápida de insertar a granel una gran cantidad de datos en SQL Server(cliente C#)? (9)

Estoy llegando a algunos cuellos de botella de rendimiento con mi cliente C # insertando datos masivos en una base de datos de SQL Server 2005 y estoy buscando formas de acelerar el proceso.

Ya estoy usando SqlClient.SqlBulkCopy (que está basado en TDS) para acelerar la transferencia de datos a través del cable, lo que ayudó mucho, pero aún estoy buscando más.

Tengo una tabla simple que se ve así:

CREATE TABLE [BulkData]( [ContainerId] [int] NOT NULL, [BinId] [smallint] NOT NULL, [Sequence] [smallint] NOT NULL, [ItemId] [int] NOT NULL, [Left] [smallint] NOT NULL, [Top] [smallint] NOT NULL, [Right] [smallint] NOT NULL, [Bottom] [smallint] NOT NULL, CONSTRAINT [PKBulkData] PRIMARY KEY CLUSTERED ( [ContainerIdId] ASC, [BinId] ASC, [Sequence] ASC ))

Estoy insertando datos en fragmentos que promedian unas 300 filas donde ContainerId y BinId son constantes en cada fragmento y el valor de Sequence es 0-n y los valores se ordenan previamente en función de la clave principal.

El contador de rendimiento de% Disk time pasa mucho tiempo al 100%, por lo que está claro que el disco IO es el problema principal, pero las velocidades que obtengo son varios órdenes de magnitud por debajo de una copia de archivo sin formato.

¿Ayuda a alguno si yo:

Suelta la clave principal mientras realizo la inserción y recípala más tarde
Haga inserciones en una tabla temporal con el mismo esquema y transfiéralas periódicamente a la tabla principal para mantener el tamaño de la tabla donde las inserciones están sucediendo pequeñas
¿Algo más?

- Con base en las respuestas que he recibido, permítanme aclarar un poco:

Portman: estoy usando un índice agrupado porque cuando todos los datos sean importados tendré que acceder a los datos secuencialmente en ese orden. No necesito particularmente que el índice esté allí mientras se importan los datos. ¿Hay alguna ventaja de tener un índice PK no agrupado mientras se realizan las inserciones, en lugar de eliminar la restricción por completo para la importación?

Chopeen: Los datos se están generando de forma remota en muchas otras máquinas (mi servidor SQL solo puede manejar alrededor de 10 en la actualidad, pero me encantaría poder agregar más). No es práctico ejecutar todo el proceso en la máquina local porque tendría que procesar 50 veces más datos de entrada para generar la salida.

Jason: No estoy haciendo ninguna consulta concurrente en la mesa durante el proceso de importación, intentaré soltar la clave principal y ver si eso ayuda.

¿Has intentado usar transacciones?

Según lo que describes, haciendo que el servidor se comprometa 100% del tiempo en el disco, parece que estás enviando cada fila de datos en una sentencia SQL atómica, lo que obliga al servidor a confirmar (escribir en el disco) cada fila.

Si utilizó transacciones en su lugar, el servidor solo se comprometería una vez al final de la transacción.

Para obtener ayuda adicional: ¿Qué método está utilizando para insertar datos en el servidor? ¿Actualizando una DataTable usando un DataAdapter, o ejecutando cada oración usando una cadena?

¿Qué hay de aumentar la memoria asignada al servidor o el tamaño del búfer utilizado por el servidor, si es posible?

Creo que parece que esto podría hacerse utilizando paquetes de SSIS . Son similares a los paquetes DTS de SQL 2000. Los he usado para transformar con éxito todo, desde archivos CSV de texto plano, desde tablas SQL existentes, e incluso desde archivos XLS con filas de 6 dígitos distribuidos en varias hojas de trabajo. Puede usar C # para transformar los datos en un formato importable (CSV, XLS, etc.), luego haga que su servidor SQL ejecute un trabajo SSIS programado para importar los datos.

Es bastante fácil crear un paquete SSIS, hay un asistente incorporado en la herramienta Enterprise Manager de SQL Server (etiquetada como "Importar datos", creo), y al final del asistente, le da la opción de guardarlo como un paquete SSIS. También hay más información sobre Technet .

Sí, tus ideas te ayudarán.
Apóyate en la opción 1 si no hay lecturas mientras estás cargando.
Apóyate en la opción 2 si tu tabla de destino está siendo consultada durante tu procesamiento.

@Andrés
Pregunta. Su inserción en trozos de 300. ¿Cuál es la cantidad total de su inserción? El servidor SQL debería poder manejar 300 inserciones antiguas simples muy rápido.

Supongo que verás una mejora dramática si cambias ese índice para que no esté agrupado . Esto te deja con dos opciones:

Cambie el índice a no agrupado y déjelo como una tabla de almacenamiento dinámico, sin un índice agrupado
Cambie el índice a no agrupado, pero luego agregue una clave sustituta (como "id") y conviértalo en una identidad, clave principal e índice agrupado

Cualquiera de los dos acelerará sus insertos sin ralentizar notablemente sus lecturas.

Piénselo de esta manera: en este momento, le está diciendo a SQL que haga una inserción masiva, pero luego le está pidiendo a SQL que reordene la tabla completa en cada tabla que agregue algo. Con un índice no agrupado, agregará los registros en el orden en que ingresen y luego creará un índice separado que indique el orden que desean.

Ya está usando SqlBulkCopy , que es un buen comienzo.

Sin embargo, solo el uso de la clase SqlBulkCopy no significa necesariamente que SQL realizará una copia masiva. En particular, existen algunos requisitos que se deben cumplir para que SQL Server realice una inserción masiva eficiente.

Otras lecturas:

Por curiosidad, ¿por qué tu índice está configurado así? Parece que ContainerId / BinId / Sequence es mucho más adecuado para ser un índice no agrupado. ¿Hay alguna razón particular por la que desea que este índice se agrupe?

BCP : es un esfuerzo configurarlo, pero ha estado presente desde el comienzo de los DB y es muy rápido.

A menos que esté insertando datos en ese orden, el índice de 3 partes realmente ralentizará las cosas. Aplicarlo más tarde realmente también ralentizará las cosas, pero estará en un segundo paso.

Las claves compuestas en Sql son siempre bastante lentas, cuanto más grande es la tecla, más lento.

No soy realmente brillante y no tengo mucha experiencia con el método SqlClient.SqlBulkCopy, pero aquí están mis 2 centavos por lo que vale. Espero que te ayude a ti y a los demás (o al menos haga que la gente diga mi ignorancia).

Nunca igualará la velocidad de copia de un archivo sin formato, a menos que el archivo de datos de la base de datos (mdf) se encuentre en un disco físico separado del archivo de registro de transacciones (ldf). Además, cualquier índice agrupado también debería estar en un disco físico separado para una comparación más justa.

Su copia sin formato no está registrando o manteniendo un orden de selección de campos seleccionados (columnas) con fines de indexación.

Estoy de acuerdo con Portman en la creación de una semilla de identidad no agrupada y el cambio de su índice existente no agrupado a un índice agrupado.

En cuanto a qué construcción está utilizando en los clientes ... (adaptador de datos, conjunto de datos, tabla de datos, etc.). Si su disco io en el servidor está al 100%, no creo que su tiempo se dedique mejor a analizar las construcciones del cliente, ya que parecen ser más rápidas de lo que el servidor puede manejar actualmente.

Si sigues los enlaces de Portman sobre el registro mínimo, no pensaría que rodear tus copias masivas en las transacciones ayudaría mucho si hubiera alguno, pero he estado equivocado muchas veces en mi vida;)

Esto no necesariamente lo ayudará en este momento, pero si descubre su problema actual, este próximo comentario podría ayudar con el próximo cuello de botella (rendimiento de la red), especialmente si se trata de Internet.

Chopeen también hizo una pregunta interesante. ¿Cómo determinó usar 300 fragmentos de recuento de registros para insertar? SQL Server tiene un tamaño de paquete predeterminado (creo que es 4096 bytes) y tendría sentido derivar el tamaño de sus registros y asegurarse de que está haciendo un uso eficiente de los paquetes que transmiten entre el cliente y el servidor. (Tenga en cuenta que puede cambiar el tamaño de su paquete en su código cliente en lugar de la opción del servidor que obviamente lo cambiaría para todas las comunicaciones del servidor, probablemente no sea una buena idea). Por ejemplo, si su tamaño de registro resulta en 300 lotes de registro que requieren 4500 bytes, enviará 2 paquetes con el segundo paquete desperdiciado en su mayoría. Si el recuento de registros por lotes se asignó arbitrariamente, podría tener sentido realizar cálculos matemáticos rápidos y fáciles.

Por lo que puedo decir (y recuerde acerca de los tamaños de tipo de datos), tiene exactamente 20 bytes para cada registro (si int = 4 bytes y smallint = 2 bytes). Si está utilizando 300 lotes de recuento de registros, entonces está tratando de enviar 300 x 20 = 6.000 bytes (además supongo que habrá un poco de sobrecarga para la conexión, etc.). Es posible que sea más eficiente enviarlos en 200 lotes de recuentos (200 x 20 = 4,000 + espacio para gastos generales) = 1 paquete. Por otra parte, su cuello de botella todavía parece ser el disco io del servidor.

Me doy cuenta de que está comparando una transferencia de datos en bruto a SqlBulkCopy con el mismo hardware / configuración, pero aquí es a donde iría también si el desafío fuera mío:

Es probable que esta publicación ya no lo ayude, ya que es bastante antigua, pero a continuación pregunto cuál es la configuración RAID de su disco y qué velocidad de disco está usando. Intente colocar el archivo de registro en una unidad que use RAID 10 con un RAID 5 (idealmente 1) en su archivo de datos. Esto puede ayudar a reducir un gran movimiento del eje a diferentes sectores en el disco y dar como resultado más tiempo de lectura / escritura en lugar del improductivo estado "en movimiento". Si ya separa sus archivos de datos y de registro, ¿tiene su índice en una unidad de disco física diferente de su archivo de datos (solo puede hacerlo con índices agrupados). Eso permitiría no solo actualizar al mismo tiempo la información de registro con la inserción de datos, sino que también permitiría que la inserción del índice (y cualquier operación costosa de la página del índice) ocurriera al mismo tiempo.

A continuación, le mostramos cómo puede deshabilitar / habilitar índices en SQL Server:

--Disable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users DISABLE GO --Enable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users REBUILD

Aquí hay algunos recursos para ayudarlo a encontrar una solución:

Algunas comparaciones de velocidad de carga masiva

Use SqlBulkCopy para cargar rápidamente datos de su cliente a SQL Server

Optimizar el rendimiento de copias masivas

Definitivamente mira en las opciones NOCHECK y TABLOCK:

Sugerencias de tabla (Transact-SQL)

INSERTAR (Transact-SQL)