sql - txt - ¿Por qué las inserciones/actualizaciones de lotes son más rápidas? ¿Cómo funcionan las actualizaciones por lotes?
optimizar cursor sql server (4)
¿Por qué los insertos de lotes son más rápidos? ¿Es porque la sobrecarga de conexión y configuración para insertar una sola fila es la misma para un conjunto de filas? ¿Qué otros factores hacen que las plaquitas de lotes sean más rápidas?
¿Cómo funcionan las actualizaciones por lotes? Suponiendo que la tabla no tiene restricciones de exclusividad, las instrucciones de inserción realmente no tienen ningún efecto en otras instrucciones de inserción en el lote. Sin embargo, durante las actualizaciones por lotes, una actualización puede alterar el estado de la tabla y, por lo tanto, puede afectar el resultado de otras consultas de actualización en el lote.
Sé que las consultas de inserción por lotes tienen una sintaxis en la que tiene todos los valores de inserción en una consulta grande. ¿Cómo se ven las consultas de actualización por lotes? Por ejemplo, si tengo consultas únicas de actualización del formulario:
update <table> set <column>=<expression> where <condition1>
update <table> set <column>=<expression> where <condition2>
update <table> set <column>=<expression> where <condition3>
update <table> set <column>=<expression> where <condition4>
Qué sucede cuando se usan en un lote. ¿Cómo se verá la consulta única?
¿Y las inserciones y actualizaciones por lotes forman parte del estándar SQL?
¿Por qué los insertos de lotes son más rápidos?
Por numerosas razones, pero las tres principales son estas:
- La consulta no necesita ser repasada.
- Los valores se transmiten en un viaje de ida y vuelta al servidor
- Los comandos están dentro de una sola transacción
¿Es porque la sobrecarga de conexión y configuración para insertar una sola fila es la misma para un conjunto de filas?
Parcialmente sí, mira arriba.
¿Cómo funcionan las actualizaciones por lotes?
Esto depende de RDBMS .
En Oracle puede transmitir todos los valores como una colección y usar esta colección como una tabla en un JOIN .
En PostgreSQL y MySQL , puede usar la siguiente sintaxis:
INSERT
INTO mytable
VALUES
(value1),
(value2),
…
También puede preparar una consulta una vez y llamarla en algún tipo de bucle. Por lo general, hay métodos para hacerlo en una biblioteca cliente.
Suponiendo que la tabla no tiene restricciones de exclusividad, las instrucciones de inserción realmente no tienen ningún efecto en otras instrucciones de inserción en el lote. Pero, durante las actualizaciones por lotes, una actualización puede alterar el estado de la tabla y, por lo tanto, puede afectar el resultado de otras consultas de actualización en el lote.
Sí, y usted puede beneficiarse o no de este comportamiento.
Sé que las consultas de inserción por lotes tienen una sintaxis en la que tiene todos los valores de inserción en una consulta grande. ¿Cómo se ven las consultas de actualización por lotes?
En Oracle , usa la colección en una unión:
MERGE
INTO mytable
USING TABLE(:mycol)
ON …
WHEN MATCHED THEN
UPDATE
SET …
En PostgreSQL :
UPDATE mytable
SET s.s_start = 1
FROM (
VALUES
(value1),
(value2),
…
) q
WHERE …
En las actualizaciones por lotes, la base de datos funciona en función de un conjunto de datos, en una fila por actualización de fila tiene que ejecutar el mismo comando tantas veces como filas. Entonces, si inserta un millón de filas en un lote, el comando se envía y procesa una vez y en una actualización fila por fila, se envía y procesa un millón de veces. Esta es también la razón por la que nunca desea utilizar un cursor en SQL Server o una subconsulta correlacionada.
un ejemplo de una actualización basada en conjunto en el servidor SQL:
update mytable
set myfield = ''test''
where myfield is null
Esto actualizaría todos los 1 millón de registros que son nulos en un solo paso. Una actualización de cursor (que es cómo se actualizarían un millón de filas de forma no por lotes) iteraría a través de cada fila una vez y la actualizaría.
El problema con un inserto de lote es el tamaño del lote. Si intenta actualizar demasiados registros a la vez, la base de datos puede bloquear la tabla durante la duración del proceso, bloqueando a todos los demás usuarios. Por lo tanto, es posible que deba realizar un ciclo que solo tome parte del lote por vez (pero casi cualquier número mayor a una fila a la vez será más rápido que una fila a la vez). Esto es más lento que actualizar o insertar o eliminar el todo el lote, pero más rápido que las operaciones fila por fila y puede ser necesario en un entorno de producción con muchos usuarios y poco tiempo de inactividad disponible cuando los usuarios no están tratando de ver y actualizar otros registros en la misma tabla. El tamaño del lote depende en gran medida de la estructura de la base de datos y de lo que está sucediendo exactamente (las tablas con desencadenantes y muchas restricciones son más lentas al igual que las tablas con muchos campos, por lo que requieren lotes más pequeños).
Estaba buscando una respuesta sobre el mismo tema, sobre la actualización "bulk / batch". La gente a menudo describe el problema comparándolo con la cláusula de inserción con conjuntos de valores múltiples (la parte "a granel").
INSERT INTO mytable (mykey, mytext, myint)
VALUES
(1, ''text1'', 11),
(2, ''text2'', 22),
...
La respuesta clara todavía me estaba evitando, pero encontré la solución aquí: http://www.postgresql.org/docs/9.1/static/sql-values.html
Para hacerlo claro:
UPDATE mytable
SET
mytext = myvalues.mytext,
myint = myvalues.myint
FROM (
VALUES
(1, ''textA'', 99),
(2, ''textB'', 88),
...
) AS myvalues (mykey, mytext, myint)
WHERE mytable.mykey = myvalues.mykey
Tiene la misma propiedad de ser "bulk" aka que contiene una gran cantidad de datos con una sola declaración.
Las otras publicaciones explican por qué las declaraciones masivas son más rápidas y cómo hacerlo con valores literales.
Creo que es importante saber cómo hacerlo con marcadores de posición. No usar marcadores de posición puede llevar a cadenas de comandos gigantescas, a errores de cotización / escape y, por lo tanto, a aplicaciones que son propensas a la inyección de SQL.
Inserción masiva con marcadores de posición en PostgreSQL> = 9.1
Para insertar un número arbitrario de filas en la tabla "mitabla", que consta de columnas "col1", col2 "y" col3 ", todo en uno tiene (una declaración, una transacción):
INSERT INTO mytable (col1, col2, col3)
VALUES (unnest(?), unnest(?), unnest(?))
Debe proporcionar tres argumentos a esta declaración. El primero debe contener todos los valores para la primera columna y así sucesivamente. En consecuencia, todos los argumentos tienen que ser listas / vectores / matrices de igual longitud.
Actualización masiva con marcadores de posición en PostgreSQL> = 9.1
Digamos que su tabla se llama "mytable". Consiste en las columnas "clave" y "valor".
update mytable
set value = data_table.new_value
from
(select unnest(?) as key, unnest(?) as new_value) as data_table
where mytable.key = data_table.key
Lo sé, esto no es fácil de entender. Parece que SQL ofuscado. Por otro lado: funciona, escala, funciona sin concatenación de cadenas, es seguro y es increíblemente rápido.
Debe proporcionar dos argumentos a esta declaración. El primero debe ser una lista / vector / matriz que contenga todos los valores para la columna "clave". Por supuesto, el segundo debe contener todos los valores para la columna "valor".
En caso de que alcance los límites de tamaño, es posible que tenga que mirar COPY INTO ... FROM STDIN (PostgreSQL).