c# - soporta - Mejorar INSERT INTO-FROM SELECT, SQL Query

optimizar consultas lentas mysql (10)

¿Has probado el estudio de gestión de servidor sql a sql para ver cuánto tiempo lleva realmente? Yo comenzaría allí. Puede mejorar el rendimiento de la selección. Y es posible que pueda mejorar el rendimiento con sugerencias de tablock sobre la mesa en la que se inserta.

Actualmente recibí este tipo de consulta generada por la programación (c #)

INSERT INTO TableName (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM TableName2

El problema es que SELECT puede tener un resultado de muchos registros (como un millón), por lo que es necesario muchas veces y el resultado es un tiempo de espera de conexión.

Además, si separe toda la inserción en una sola inserción (para este ejemplo, un millón de consultas de inserción), su ejecución tardará mucho tiempo ... pero funciona ...

¿Hay alguna manera de mejorar este tipo de consulta?

Yo uso MSSQl 2005

Gracias

Algunas buenas respuestas aquí.

Simplemente quiero agregar que si tiene índices en la tabla de destino, ralentizará la operación. Sin embargo, reconstruir el índice a veces puede llevar mucho tiempo si realiza la técnica de creación de gotas.

Si no desea soltar los índices, use un ORDER BY en su SELECT que coincida con el índice agrupado de destino , esto parece ayudar (probablemente ayude a minimizar las divisiones de página).

Bueno, si es una copia completa, me pregunto si no deberías buscar herramientas de carga masiva.

BULK INSERT (TSQL)
SqlBulkCopy (.NET)
bcp (línea de comando)
etc

Si tuviera una cláusula Where , verificaría que esté adecuadamente indexada ...

Adicionalmente:

tal vez soltar los índices y disparadores antes de hacer el INSERT (recrear luego)
~~¿Considera dejar caer toda la mesa y usar SELECT INTO?~~ (ver comentarios)

Descubrí que, si tiene muchas instrucciones INSERT que se ejecutan en secuencia, puede mejorar el rendimiento agregando una instrucción ''IR'' después de cada número xxxx de instrucciones de inserción:

... INSERT INTO Table ( ... ) VALUES ( ... ) INSERT INTO Table ( ... ) VALUES ( ... ) INSERT INTO Table ( ... ) VALUES ( ... ) GO INSERT INTO Table ( ... ) VALUES ( ... ) INSERT INTO Table ( ... ) VALUES ( ... ) ...

Otra posibilidad, tal vez, es asegurarse de que su consulta INSERT INTO .. SELECT FROM no inserte todo al mismo tiempo, en su lugar utilice algún tipo de técnica de paginación:

INSERT INTO Table ... SELECT ... FROM OtherTable WHERE Id > x and Id < y

En primer lugar, nunca intente insertar un millón de registros a través de C #. Nunca procese grandes grupos de registros de a uno por vez. Este es un trabajo que la base de datos debe hacer en la base de datos. Utilice la inserción masiva o SSIS o DTS para hacer esto. Y luego programarlo como un trabajo fuera de horas. Si aún toma demasiado tiempo, sugiero que lo ejecute en lotes de varios miles (tendrá que jugar con su propia base de datos para ver cuál es la mejor opción, ya que el número que puede procesar de manera segura depende en gran medida de las tablas, la indexación es rápido su servidor y cuántos usuarios también intentan trabajar contra las mismas tablas.

Establezca la propiedad CommandTimeout del SqlCommand que está utilizando con un valor razonable (10 minutos o algo así). Recuerde que CommandTimeout está en segundos.

No indica qué problema está resolviendo con este enfoque. Obviamente, un DONDE reduciría el conjunto de registros. Pero si el conjunto de resultados no se va a modificar en la Nueva tabla, ¿por qué replicar los datos? ¿Por qué no consultar directamente desde la fuente?

Ok, hay algunos problemas fundamentales.

I / O - Insertar en una tabla mientras se lee de otra tabla probablemente provocará una contención del disco si las tablas no están en discos separados. Coloque las tablas opuestas en husos físicamente diferentes.
Registro de transacciones: debe asegurarse de que su registro de transacciones se encuentre en su propio disco, o trabaje en transacciones más pequeñas (unos miles de filas a la vez) o utilice BCP / Bulk Insert que no esté registrado.
Índices agrupados: si está insertando todas estas filas en una tabla de destino, y su índice agrupado (los datos de la orden física se escriben en el disco) no se escribe de forma secuencial, los requisitos de IO del disco se van por las nubes debido a las divisiones de página y asignación. Una solución fácil puede ser crear un índice agrupado en la tabla de destinatarios que sea una clave secuencial secuencial. Esto generalmente asegurará que obtenga escrituras secuenciales en la tabla y casi siempre al final.
Expansión de archivos: asegúrese de tener SQL configurado para expandir sus archivos a una tasa decente, como 10% más o menos. De lo contrario, tendrá que redimensionar constantemente sus archivos y poner a cero el disco. Hay formas de evitar que también tenga que poner a cero el disco, como habilitar el permiso de Operación Masiva de Archivos en las políticas de su grupo para el usuario del Servicio Sql.

Francamente, aparte de eso y algunas de las otras sugerencias, es muy poco probable que haga un inserto con millones de filas en una transacción sea verdaderamente rápido. Si hicieras esto a través de Bulk Insert, sería drásticamente más rápido, aunque podría no ser lo que necesitas desde la perspectiva de una aplicación.

Otra forma que hemos utilizado en el pasado es crear una tabla temporal con las claves principales que queremos mover y usar al mismo tiempo. De esta manera, puede hacerlo de una manera muy parecida a la de un bloque para evitar la gran sobrecarga de las transacciones si cancela y tiene que retroceder.

Básicamente, lo que terminas haciendo es una inserción en el nombre de la tabla (...) selecciona (...) desde el nombre de la tabla donde está la clave principal (selecciona la clave superior de 10000 de temptable)

los 10000 mejores que desee en un conjunto de resultados secundario para que pueda eliminarlos de la tabla temporal para que no se vuelvan a procesar.

Otra forma sería usar cursores para reducir el número de registros que procesa a la vez.

Otro método de bucle sería hacer algo como esto en un ciclo while.

declarar @stop como int set @stop = (seleccionar count (primaryKey) desde tableName donde primaryKey no está en destinstiontable)

mientras (@stop> 0) comienza la transacción

inserte en la tabla de destino (...) seleccione (...) de la tabla fuente donde la clave primaria no está en (seleccione la clave primaria de la tabla de destino)

cometer

set @stop = (seleccionar count (primaryKey) desde tableName donde primaryKey no está en destinstiontable) end

No es el más eficiente, pero funcionaría y debería permitirle mantener el registro de transacciones. A menos que lo necesite también asegúrese de usar la palabra clave no lock para que no bloquee otras transacciones al hacer este movimiento grande (a menos que use BCP o DTS ya que son mucho más rápidos).

Sin embargo, algo de lo que se ha dicho es probablemente la mejor opción. Use BCP, DTS o alguna otra herramienta masiva. Si puede eliminar índices, hará que las cosas vayan mucho más rápido.

bien a granel cargue usando un archivo y luego bcp / BULK INSERT o agréguelo en lotes de 5K o menos