Grimpi IT Blog

abril 3, 2008

Soluciones para actualizar un registro si existe, sino insertar en SQL Server.

Filed under: SQL Server, T-SQL — grimpi @ 2:06 am

Muchas veces cuando trabajamos con ABMs o algún proceso de escritura en la base de datos, al actualizar los registros, debemos establecer si vamos a efectuar un INSERT o un UPDATE. O sea, tenemos que determinar si el registro existe o no, para saber que operación se va a efectuar en la base de datos.

Generalmente se suele encapsular toda esta lógica dentro de un SP, algo que considero una muy buena practica, ya que nos desentendemos del lado de la aplicación, si se va a efectuar una operación de inserción o de modificación.

Primera solución:

Ahora bien, dentro del Store Procedure, lo solemos hacer para determinar la operación, es el famoso IF EXISTS.
Ejemplo:

IF EXISTS(SELECT ID FROM TABLA WHERE ID = @ID)
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)
ELSE
UPDATE
TABLA SET Campo1 = @Valor WHERE ID = @ID

No es un mal enfoque, es muy claro. Sin embargo esta solución tiene dos inconvenientes:
1) Estamos pagando el costo de ejecutar un Query. Por mas que la consulta este indexada, tiene un costo.
2) No es 100% segura. En entornos muy demandantes, con alta concurrencia, puede darse el caso de que justo luego de ejecutar el IF EXISTS, otro proceso inserte en la tabla un registro con la misma PK y no tendríamos forma de darnos cuenta, generando un error de duplicate key.
Por lo tanto, esta opción que es la más común, tiene serios inconvenientes.

Segunda solución:

Una segunda opción podría ser esta:

UPDATE TABLA SET Campo1 = @Valor WHERE ID = @ID
IF @@ROWCOUNT = 0
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)

En caso, se eliminaría el tener que ejecutar una query con el EXISTS. Aunque en caso de que no exista el registro, se ejecuta el UPDATE innecesariamente.
Si la mayoría de las operaciones van a ser del tipo INSERT, en realidad no se ganaría performance, pero por el contrario, si la mayoría de las operaciones seria del tipo UPDATE, podría llegar a ser mas performante.
De todas maneras esta solución sigue teniendo el problema de que otro proceso podría insertar un registro con la misma PK en la tabla y no tendríamos forma de darnos cuenta.

Tercera solución:

En el segundo caso ganamos un poco de performance (no siempre), pero seguimos con el mismo problema de concurrencia.
Pero ahora veamos este ejemplo de código:

BEGIN TRY
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)
END TRY
BEGIN CATCH
UPDATE TABLA SET Campo1 = @Valor WHERE ID = @ID
END CATCH

A nivel perfomance, es similar a las otras soluciones, pero si tenemos muchas más operaciones de inserción que de actualización, vamos a ganar velocidad.
Sin embargo, en este caso no tendríamos el inconveniente de concurrencia que sucede en los 2 casos anteriores!!. Lo cual lo hace ideal para situaciones de alta demanda.

Cuarta solución (Solo en SQL Server 2008):

SQL Server 2008, incorpora el comando MERGE (que ya teníamos en Oracle y otros motores), que sirve para resolver de una manera muy eficiente, exactamente este problema.

MERGE TABLA
USING (SELECT @ID AS ID) AS SRC ON SRC.ID = TABLA.ID
WHEN MATCHED THEN
UPDATE SET Campo1 = @Valor
WHEN NOT MATCHED THEN
INSERT (Campo1,ID) VALUES (@Valor,@ID)

Con este método, también solucionamos el problema de concurrencia, y además evitar tener que ejecutar consultas innecesarias. Por lo cual, podríamos decir que es la optima solución resolver este problema, aunque lamentablemente debemos esperar hasta mitad de año, cuando Microsoft libere SQL Server 2008.

Conclusión:

Vimos como un problema en apariencia tonto y trivial, puede causar serios problemas de performance y peor aun, crear errores de concurrencia y comportamientos no deseados.
Por las pruebas que hicimos en un entorno de TEST, la diferencia de performance que hicimos no son demasiadas. Pero en situaciones de alta concurrencia, las 2 primeras soluciones son definitivamente incorrectas.

Recomiendo ver estos links, que explican como funcionan los lockeos, en cada una de las distintas soluciones:

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx
http://weblogs.sqlteam.com/mladenp/archive/2007/08/03/60277.aspx

Y estas soluciones alternativas al mismo problema, tal vez sean un poco más complejas, pero en algunos escenarios pueden ser útiles:

http://www.samsaffron.com/blog/archive/2007/04/04/14.aspx
http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

20 comentarios »

  1. Es parte SQL:2003 así que supongo que no tardará en llegar al resto. Pero acá están las alternativas no estándar:

    – MySql:
    http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

    – PostgreSQL (es un hack, pero bueh):
    http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
    (el punto 38-1)

    Saludos,
    Mauro.

    Comentario por Mauro — abril 4, 2008 @ 5:15 pm

  2. en realidad hay un problema mas general que es el de UPSERTEAR varios registros a la vez y ocurre muy a menudo en escenarios de datawarehouse.

    La opción que yo uso en SQL2005 es
    update tabla1
    … = ….
    from tabla2
    where tabla2.pk in (select pk from tabla1)

    insert into tabla1 (…)
    select xxx from tabla2
    where tabla2.pk not in (select pk from tabla1)

    Comentario por vqp — octubre 1, 2008 @ 9:45 pm

  3. Precisamente, para evitar esto que estas haciendo, en SQL Server 2008, pusieron la instruccion MERGE, que esta pensada para estos escenarios de datawarehouse.

    Comentario por Esteban — octubre 2, 2008 @ 12:53 am

  4. Ok la solucion proporcionada es eficiente, pero sólo funciona en los casos en que poseemos el pk para insertar, en los casos que en mi experiencia son mas comunes en que solo buscamos coincidencias de campos para realizar update o insert el ejemplo proporcionado no funciona.

    Comentario por David — noviembre 28, 2008 @ 3:20 pm

  5. Muy bueno el articulo

    Comentario por Lester Herrera — enero 17, 2009 @ 4:22 pm

  6. […] si hacer un UPDATE o un INSERT en latabla. El famoso IF EXISTS… (tema que ya habíamos visto acá y que es un poquito más complicado de lo que parece a simple […]

    Pingback por Lo nuevo de SQL Server 2008: MERGE « Grimpi IT Blog — febrero 24, 2009 @ 11:22 pm

  7. se puede usar BEGIN TRY en MySql?

    Comentario por julio — diciembre 30, 2009 @ 7:19 pm

  8. después de probar los métodos que se exponen en mysql creo que lo mas practico es utilizar remplace que es una función nativa y elimina el original y reescribe de nuevo la linea en la base de datos. El unico problema que he encontrado al experimentar esto es que el ID también cambia, así como el resto de datos “automáticos”.

    Comentario por antares500 — enero 25, 2010 @ 5:10 pm

  9. yo quisiera que me dején un ejemplo para no insertar registros repetidos en tablas donde el PrimaryKey sea autonumérico. Necesito hacer esto para evitar insertar registros que tienen otros 10 campos de la tabla repetidos (la tabla tiene 13 registros incluyendo la primary key).

    Comentario por Eduardo — septiembre 28, 2010 @ 7:47 pm

  10. Muy buenas las soluciones… pero en mi caso tengo un SQL 2000, así q la solución q puedo usar es la segunda; ahora, esta solución no soluciona mi problema ya q siempre estaría haciendo modificación en los registros q existen, y en lo q necesito modificar solo los registros diferentes ya q es la sincronización de 2 BD q se encuentran en orígenes distintos… q solución me pueden dar?

    Comentario por Juan Ibrahin — noviembre 9, 2010 @ 5:30 pm

  11. pero en oracle

    Comentario por leysan — noviembre 15, 2010 @ 1:31 am

  12. me gustaria solucionar un pequeño problema que me surgió
    en una consulta SQL con postgre es como insertar un campo
    autoincremental..

    Comentario por padilla — marzo 6, 2011 @ 5:29 pm

  13. El tercer ejemplo tiene el problema de la concurrencia si hacen un DELETE entre en el INSERT y el UPDATE, la unica manera segura en versiones 2005 para atrás es bloqueando la tabla pero el costo de eso es una pérdida de rendimiento

    Comentario por GeekZero — julio 28, 2011 @ 8:05 pm

  14. Excelente post me aclaro muchas dudas sobre un sp que necesitaba realizar gracias por el aporte a la comunidad informática saludos……

    Comentario por Nicolas — octubre 23, 2012 @ 2:19 pm

  15. excelente…muchas dudas fueron aclaradas..

    Comentario por Anónimo — noviembre 6, 2013 @ 9:15 pm

  16. xrumer

    Comentario por XrumerVIP — diciembre 19, 2013 @ 8:43 am

  17. 1 Program you’r planting of crops to coincide whenever you can log onto your farm to reap them. edfebdafdfak

    Comentario por Johnc629 — septiembre 20, 2014 @ 7:13 pm

  18. Tengo un problema con el executenonquery me dice que no se han especificado valores para algunis de los parámetros requeridos

    Comentario por efrain — diciembre 27, 2014 @ 6:36 pm

  19. hola, yo tengo un inconveniente, yo tengo una base de datos que están trabajando en dos servidores diferentes, ahora yo deseo insertar los valores no existentes de una de las tablas hacia la otra, esto me podría ayudar?

    Comentario por marc — diciembre 15, 2015 @ 2:02 pm

  20. rak minimarket di yogyakarta

    Soluciones para actualizar un registro si existe, sino insertar en SQL Server. | Grimpi IT Blog

    Trackback por rak minimarket di yogyakarta — octubre 25, 2016 @ 2:13 am


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: