queryout - Rendimiento de inserción de SQL Server

queryout sql server ejemplos

Question

Tengo una consulta de inserción que se genera así

INSERT INTO InvoiceDetail (LegacyId,InvoiceId,DetailTypeId,Fee,FeeTax,Investigatorid,SalespersonId,CreateDate,CreatedById,IsChargeBack,Expense,RepoAgentId,PayeeName,ExpensePaymentId,AdjustDetailId)
VALUES(1,1,2,1500.0000,0.0000,163,1002,''11/30/2001 12:00:00 AM'',1116,0,550.0000,850,NULL,@ExpensePay1,NULL);
DECLARE @InvDetail1 INT; SET @InvDetail1 = (SELECT @@IDENTITY);

Esta consulta se genera solo para 110K filas.

Toma 30 minutos para que todas estas consultas se ejecuten.

Revisé el plan de consulta y los% nodos más grandes son

Una inserción de índice agrupado al 57% del costo de consulta que tiene un xml largo que no quiero publicar.

Un Table Spool que tiene un costo de consulta del 38%

<RelOp AvgRowSize="35" EstimateCPU="5.01038E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="80" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0466109">
<OutputList>
<ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvoiceId" />
<ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvestigatorId" />
<ColumnReference Column="Expr1054" />
<ColumnReference Column="Expr1055" />
</OutputList>
<Spool PrimaryNodeId="3" />
</RelOp>

Entonces, mi pregunta es: ¿qué puedo hacer para mejorar la velocidad de esto? Ya ejecuté ALTER TABLE TABLENAME NOCHECK RESTRINGE TODO antes de las consultas y luego ALTER TABLE TABLENAME NOCHECK RESTRINGE TODO después de las consultas.

Y eso no quitó nada del tiempo.

Saber que estoy ejecutando estas consultas en una aplicación .NET que usa un objeto SqlCommand para enviar la consulta.

Luego traté de enviar los comandos sql a un archivo y luego ejecutarlo utilizando sqlcmd, pero no recibí ninguna actualización sobre cómo estaba funcionando, así que renuncié a eso.

¿Alguna idea o consejo o ayuda?

ACTUALIZAR:

Ok, así que todos ustedes fueron muy útiles. En esta situación, me gustaría poder dar crédito a más de una respuesta.

La solución para arreglar esto fue doble.

El primero:

1) Deshabilité / volví a habilitar todas las claves foráneas (mucho más fácil que soltarlas)

ALTER TABLE TableName NOCHECK CONSTRAINT ALL
ALTER TABLE TableName CHECK CONSTRAINT ALL

2) Deshabilité / volví a habilitar los índices (de nuevo, mucho más fácil que soltar)

ALTER INDEX [IX_InvoiceDetail_1] ON [dbo].[InvoiceDetail] DISABLE
ALTER INDEX [IX_InvoiceDetail_1] ON [dbo].[InvoiceDetail] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

El segundo:

Envolví todas las declaraciones de inserción en una transacción. Al principio no sabía cómo hacer eso en .NET.

Realmente aprecio todos los comentarios que recibí.

Si alguna vez hago este tipo de traducción de DB a DB, definitivamente comenzaré por BULK INSERT. Parece mucho más flexible y más rápido.

Answer 1

¿Está ejecutando estas consultas una a la vez desde un cliente .Net (es decir, enviando 110,000 solicitudes de consulta separadas a SQL Server)?

En ese caso, es probable que se trate de la latencia de la red y otra sobrecarga de enviar estos INSERTs al SQL Server sin agruparlos, no al propio SQL Server.

Echa un vistazo a BULK INSERT.

Answer 2

Algunas sugerencias para aumentar el rendimiento del inserto:

• Aumentar ADO.NET BatchSize
• Elija el índice agrupado de la tabla de destino con prudencia, para que las inserciones no conduzcan a divisiones de nodo de índice agrupado (por ejemplo, columna autoinc)
• Primero inserte en una tabla de almacenamiento temporal, luego emita una declaración grande "insertar por selección" para insertar todos los datos de esa tabla de preparación en la tabla de destino real
• Aplicar SqlBulkCopy
• Coloque un bloqueo de tabla antes de insertar (si su escenario empresarial lo permite)

Tomado de Consejos para un rendimiento de inserción rápido como el rayo en SqlServer

Answer 3

Ejecutar INSERTs individuales siempre será la opción más lenta. Además, ¿cuál es el trato con @@ IDENTITY? No parece que tengas que hacer un seguimiento de aquellos que se encuentran en el medio.

Si no desea utilizar BULK INSERT desde un archivo o SSIS, hay una función SqlBulkCopy en ADO.NET que probablemente sea su mejor opción si tiene que hacerlo desde un programa .NET.

110k filas deberían tomar menos tiempo para importar que yo volver a encontrar y escribir esta respuesta.

Answer 4

Has etiquetado esta pregunta como "bulkinsert". Entonces, ¿por qué no usar el comando BULK INSERT ?

Si desea actualizaciones de progreso, puede dividir la inserción masiva en partes más pequeñas y actualizar el progreso después de que se complete cada pieza.

Answer 5

Hay varias cosas que puedes hacer:

1) Disable any triggers on this table
2) Drop all indexes
3) Drop all foreign keys
4) Disable any check constraints

Answer 6

Hm, déjalo correr, revisa los contadores de rendimiento. ¿que ves? ¿Qué diseño de disco tienes? Puedo insertar algunos millones de filas en 30 minutos, casi cien millones de filas, para ser exactos (información financiera en tiempo real, enlaces a otras 3 tablas). Apuesto bastante a que su diseño de IO es malo (es decir, mala estructura de disco, mala distribución de archivos)

Answer 7

Lo más probable es que esto se haga en espera de descarga. Si no envuelve los conjuntos de INSERT en una transacción gestionada explícitamente, cada INSERT es su propia transacción auto-confirmada. Lo que significa que cada INSERT emite automáticamente un compromiso, y un compromiso tiene que esperar hasta que el registro sea duradero (es decir, escrito en el disco). El lavado del registro después de cada inserción es extremadamente lento.

Por ejemplo, al intentar insertar 100k filas como la tuya en un estilo de confirmación de fila única:

set nocount on;
declare @start datetime = getutcdate();
declare @i int = 0;
while @i < 100000
begin
INSERT INTO InvoiceDetail (
LegacyId,InvoiceId,DetailTypeId,Fee,
FeeTax,Investigatorid,SalespersonId,
CreateDate,CreatedById,IsChargeBack,
Expense,RepoAgentId,PayeeName,ExpensePaymentId,
AdjustDetailId)
VALUES(1,1,2,1500.0000,0.0000,163,1002,
''11/30/2001 12:00:00 AM'',
1116,0,550.0000,850,NULL,1,NULL);
set @i = @i+1;
end
select datediff(ms, @start, getutcdate());

Esto se ejecuta en unos 12 segundos en mi servidor. Pero al agregar administración de transacciones y confirmar cada 1000 filas, la inserción de 100k filas solo dura unos 4s:

set nocount on;
declare @start datetime = getutcdate();
declare @i int = 0;
begin transaction
while @i < 100000
begin
INSERT INTO InvoiceDetail (
LegacyId,InvoiceId,DetailTypeId,
Fee,FeeTax,Investigatorid,
SalespersonId,CreateDate,CreatedById,
IsChargeBack,Expense,RepoAgentId,
PayeeName,ExpensePaymentId,AdjustDetailId)
VALUES(1,1,2,1500.0000,0.0000,163,1002,
''11/30/2001 12:00:00 AM'',
1116,0,550.0000,850,NULL,1,NULL);
set @i = @i+1;
if (@i%1000 = 0)
begin
commit
begin transaction
end
end
commit;
select datediff(ms, @start, getutcdate());

También dado que puedo insertar 100k filas en 12 segundos, incluso sin cometer el lote, mientras que usted necesita 30 minutos, vale la pena investigar 1) la velocidad de su subsistema IO (por ejemplo, qué Avg. Sec per Transaction que ve en las unidades) ) y 2) ¿qué más está haciendo el código del cliente entre recuperar la identidad @@ de una llamada e invocar la siguiente inserción? Podría ser que la mayor parte del tiempo esté en el lado del cliente de la pila. Una solución simple sería lanzar varias inserciones en paralelo (BeginExecuteNonQuery) para que alimente las inserciones de SQL Server constantemente.

Answer 8

Suena como que las inserciones están causando que SQL Server vuelva a calcular los índices. Una posible solución sería eliminar el índice, realizar la inserción y volver a agregar el índice. Con su intento de solución, incluso si le dice que ignore las restricciones, todavía deberá mantener el índice actualizado.