SI EXISTE antes de INSERTAR, ACTUALIZAR, ELIMINAR para la optimización
A menudo hay una situación en la que necesita ejecutar la instrucción INSERT, UPDATE o DELETE basada en alguna condición. Y mi pregunta es si el efecto sobre el rendimiento de la consulta añadir SI EXISTE antes del comando.
Ejemplo
IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)
UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
¿Qué pasa con inserciones o eliminaciones?
12 answers
No estoy completamente seguro, pero tengo la impresión de que esta pregunta es realmente sobre upsert, que es la siguiente operación atómica:{[19]]}
- Si la fila existe tanto en el origen como en el destino,
UPDATE
el destino; - Si la fila solo existe en la fuente,
INSERT
la fila en el destino; - (Opcionalmente) Si la fila existe en el destino pero no la fuente,
DELETE
la fila del destino.
Los desarrolladores convertidos en DBA a menudo lo escriben ingenuamente fila por fila, así:
-- For each row in source
IF EXISTS(<target_expression>)
IF @delete_flag = 1
DELETE <target_expression>
ELSE
UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>
ELSE
INSERT target (<target_columns>)
VALUES (<source_values>)
Esto es casi lo peor que puedes hacer, por varias razones:{[19]]}
Tiene una condición de carrera. La fila puede desaparecer entre
IF EXISTS
y el siguienteDELETE
oUPDATE
.Es un desperdicio. Para cada transacción se realiza una operación adicional; tal vez sea trivial, pero eso depende completamente de lo bien que haya indexado.
Lo peor de todo-es seguir un modelo iterativo, pensando acerca de estos problemas en el nivel de una sola fila. Esto tendrá el mayor (peor) impacto de todos en el rendimiento general.
Una optimización muy menor (y enfatizo menor) es simplemente intentar el UPDATE
de todos modos; si la fila no existe, @@ROWCOUNT
será 0 y luego puede insertar "con seguridad":
-- For each row in source
BEGIN TRAN
UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>
IF (@@ROWCOUNT = 0)
INSERT target (<target_columns>)
VALUES (<source_values>)
COMMIT
En el peor de los casos, esto todavía realizará dos operaciones por cada transacción, pero al menos hay una posibilidad de realizar solo una, y también elimina la condición de carrera (tipo de).
Pero el verdadero problema es que esto todavía se está haciendo para cada fila en la fuente.
Antes de SQL Server 2008, tenía que usar un modelo incómodo de 3 etapas para lidiar con esto en el nivel establecido (aún mejor que fila por fila):
BEGIN TRAN
INSERT target (<target_columns>)
SELECT <source_columns> FROM source s
WHERE s.id NOT IN (SELECT id FROM target)
UPDATE t SET <target_columns> = <source_columns>
FROM target t
INNER JOIN source s ON t.d = s.id
DELETE t
FROM target t
WHERE t.id NOT IN (SELECT id FROM source)
COMMIT
Como dije, el rendimiento fue bastante pésimo en esto, pero aún así mucho mejor que el enfoque de una fila a la vez. SQL Server 2008, sin embargo, finalmente introdujo la sintaxis MERGE, por lo que ahora todo lo que tiene que hacer es esto:
MERGE target
USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE <target_columns> = <source_columns>
WHEN NOT MATCHED THEN INSERT (<target_columns>) VALUES (<source_columns>)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
Eso es todo. Una declaración. Si está utilizando SQL Server 2008 y necesita realizar cualquier secuencia de INSERT
, UPDATE
y DELETE
dependiendo de si la fila ya existe o no - incluso si es solo una fila - hay no excusa para no usar MERGE
.
Puede igualar OUTPUT
las filas afectadas por un MERGE
en una variable de tabla si necesita averiguar después lo que se hizo. Simple, rápido y sin riesgos. Haciéndolo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 18:37:17
Eso no es útil para una sola actualización/eliminación/inserción.
Posiblemente agrega rendimiento si varios operadores después de la condición if.
En el último caso es mejor escribir
update a set .. where ..
if @@rowcount > 0
begin
..
end
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 15:16:59
Usted no debe hacerlo para UPDATE
y DELETE
, como si hay impacto en el rendimiento, es no un positivo.
Para INSERT
puede haber situaciones en las que su INSERT
generará una excepción (UNIQUE CONSTRAINT
violación, etc.), en cuyo caso es posible que desee evitarlo con el IF EXISTS
y manejarlo con más gracia.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 15:21:27
Ni
UPDATE … IF (@@ROWCOUNT = 0) INSERT
Ni
IF EXISTS(...) UPDATE ELSE INSERT
Los patrones funcionan como se espera bajo alta concurrencia. Ambos pueden fallar. Ambos pueden fallar con mucha frecuencia. MERGE es el rey-se sostiene mucho mejor.Hagamos algunas pruebas de estrés y veamos por nosotros mismos.
Aquí está la tabla que usaremos:
CREATE TABLE dbo.TwoINTs
(
ID INT NOT NULL PRIMARY KEY,
i1 INT NOT NULL ,
i2 INT NOT NULL ,
version ROWVERSION
) ;
GO
INSERT INTO dbo.TwoINTs
( ID, i1, i2 )
VALUES ( 1, 0, 0 ) ;
SI EXISTE (...) ENTONCES pattern frecuentemente falla bajo alta concurrencia.
Insertemos o actualicemos filas en un bucle usando la siguiente lógica simple: si una fila con el ID dado existe, actualícelo, y de lo contrario inserte uno nuevo. El siguiente bucle implementa esta lógica. Córtalo y pégalo en dos pestañas, cambia al modo texto en ambas pestañas y ejecútalo simultáneamente.
-- hit Ctrl+T to execute in text mode
SET NOCOUNT ON ;
DECLARE @ID INT ;
SET @ID = 0 ;
WHILE @ID > -100000
BEGIN ;
SET @ID = ( SELECT MIN(ID)
FROM dbo.TwoINTs
) - 1 ;
BEGIN TRY ;
BEGIN TRANSACTION ;
IF EXISTS ( SELECT *
FROM dbo.TwoINTs
WHERE ID = @ID )
BEGIN ;
UPDATE dbo.TwoINTs
SET i1 = 1
WHERE ID = @ID ;
END ;
ELSE
BEGIN ;
INSERT INTO dbo.TwoINTs
( ID, i1, i2 )
VALUES ( @ID, 0, 0 ) ;
END ;
COMMIT ;
END TRY
BEGIN CATCH ;
ROLLBACK ;
SELECT error_message() ;
END CATCH ;
END ;
Cuando ejecutamos este script simultáneamente en dos pestañas, obtendremos inmediatamente una gran cantidad de violaciones de clave primaria en ambas pestañas. Esto demuestra lo poco fiable que es el patrón IF EXISTS cuando se ejecuta bajo alta concurrencia.
Nota: este ejemplo también demuestra que no es seguro usar SELECT MAX(ID)+1 o SELECT MIN(ID)-1 como el siguiente valor único disponible si lo hacemos bajo concurrencia.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 18:03:18
No deberías hacer esto en la mayoría de los casos. Dependiendo de su nivel de transacción, ha creado una condición de carrera, ahora en su ejemplo aquí no importaría mucho, pero los datos se pueden cambiar desde la primera selección hasta la actualización. Y todo lo que has hecho es forzar SQL a hacer más trabajo
La mejor manera de saber con certeza es probar las dos diferencias y ver cuál le da el rendimiento adecuado.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 15:14:35
IF EXISTS
básicamente hará un SELECT-el mismo que UPDATE haría.
Como tal, disminuirá el rendimiento - si no hay nada que actualizar, hizo la misma cantidad de trabajo (ACTUALIZAR habría consultado la misma falta de filas que su selección) y si hay algo que actualizar, juet hizo una selección no necesaria.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 19:38:11
El rendimiento de una declaración IF EXISTS
:
IF EXISTS(SELECT 1 FROM mytable WHERE someColumn = someValue)
Depende de los índices presentes para satisfacer la consulta.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 15:13:00
Hay un ligero efecto, ya que estás haciendo la misma comprobación dos veces, al menos en tu ejemplo:
IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)
Tiene que consultar, ver si hay alguna, si es verdadera entonces:
UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
Tiene que consultar, ver cuáles...el mismo cheque dos veces sin ninguna razón. Ahora, si la condición que está buscando está indexada, debería ser rápida, pero para tablas grandes podría ver algún retraso solo porque está ejecutando la selección.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 15:14:45
Esto repite en gran medida las anteriores (por tiempo) cinco (no, seis) (no, siete) respuestas, pero:
Sí, la estructura IF EXISTS que tiene duplicará el trabajo realizado por la base de datos. Si bien SI EXISTE se "detendrá" cuando encuentre la primera fila coincidente (no necesita encontrarlas todas), sigue siendo un esfuerzo adicional y en última instancia inútil for para actualizaciones y eliminaciones.
- Si no existe tal(s) fila (s), SI EXISTE, se realizará un análisis completo (tabla o índice) para determinar esto.
- Si una o más de estas filas existen, SI EXISTE leerá suficiente de la tabla/índice para encontrar la primera, y luego ACTUALIZAR o ELIMINAR volverá a leer la tabla para encontrarla de nuevo y procesarla -- y leerá "el resto" de la tabla para ver si hay más para procesar también. (Lo suficientemente rápido si está correctamente indexado, pero aún así.)
Así que de cualquier manera, terminarás leyendo toda la tabla o índice al menos una vez. Pero, ¿por qué molestarse con el SI EXISTE en primer lugar?
UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
O la ELIMINACIÓN similar funcionará bien ya sea que se encuentren o no filas para procesar. Sin filas, tabla escaneada, nada modificado, ya está; 1 + filas, tabla escaneada, todo lo que debe ser modificado, hecho de nuevo. Una pasada, sin problemas, sin problemas, sin tener que preocuparse por "¿cambió la base de datos otro usuario entre mi primera consulta y mi segunda consulta".
INSERTAR es la situación en la que podría ser útil check comprobar si la fila está presente antes de añadirla, para evitar Violaciónes Claves Primarias o Únicas. Por supuesto, usted tiene que preocuparse por la concurrencia? ¿qué pasa si alguien más está tratando de añadir esta fila al mismo tiempo que usted? Envolver todo esto en un solo INSERTO de manejar todo en una transacción implícita (recuerde sus propiedades ÁCIDO!):
INSERT Contacs (col1, col2, etc) values (val1, val2, etc) where not exists (select 1 from Contacs where col1 = val1)
IF @@rowcount = 0 then <didn't insert, process accordingly>
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 15:31:44
Sí, esto afectará el rendimiento (el grado en que el rendimiento se verá afectado se verá afectado por una serie de factores). Efectivamente, está haciendo la misma consulta "dos veces" (en su ejemplo). Pregúntese si necesita o no estar a la defensiva en su consulta y en qué situaciones la fila no estaría allí? Además, con una instrucción update las filas afectadas es probablemente una mejor manera de determinar si algo se ha actualizado.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-16 15:15:14
IF EXISTS....UPDATE
No lo hagas. Fuerza dos escaneos / búsquedas en lugar de uno.
Si update no encuentra una coincidencia en la cláusula WHERE, el costo de la instrucción update es solo un seek/scan.
Si encuentra una coincidencia, y si la precede w/ SI EXISTE, tiene que encontrar la misma coincidencia dos veces. Y en un entorno concurrente, lo que era cierto para el EXISTE puede no ser cierto por más tiempo para la ACTUALIZACIÓN.
Esta es precisamente la razón por la que las sentencias UPDATE/DELETE/INSERT permiten una cláusula WHERE. ¡Úsalo!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-22 02:20:43
Si estás usando MySQL, entonces puedes usar insert ... en duplicado .
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-30 16:24:08