¿Puedo optimizar esta declaración MERGE?

La guía paso a paso o código que verás en este artículo es la solución más eficiente y válida que encontramos a tu duda o dilema.

Solución:

Personalmente no me gusta MERGE porque hay muchos errores sin resolver:

• Esta publicación de blog (desplácese hacia abajo hasta “Otro MERGE cuestiones”)
• Esta respuesta de AlexKuznetsov
• Estas publicaciones en el blog de Paul White
• Publiqué un consejo de advertencia sobre MERGE aquí, incluidas referencias a varios errores que no se han abordado.
• Y es posible que esta lista ni siquiera sea exhaustiva; dada la cantidad de errores descubiertos por este pequeño grupo de usuarios, me pregunto cuántos aún no se han descubierto.

MERGE también da un false sensación de seguridad en términos de concurrencia optimista y condiciones de carrera. Consulte la publicación del blog de Dan Guzman para obtener más detalles.

No estoy tratando de ser un traficante de miedo aquí. Pero también encuentro la sintaxis poco intuitiva y desalentadora. Así que solo lo usaría en los casos en que realmente sea necesario y Puedo demostrar que no me afecta alguna de las cuestiones anteriores. No sé lo que podría ganar con su uso para una operación que solo podría terminar en una UPDATE de todas formas.

Así que así es como lo haría en su lugar, usando una sintaxis que me es mucho más familiar:

;WITH s AS 
(
  SELECT VisitorID, UserIpAddress FROM 
  (
    SELECT 
      VisitorID,
      UserIpAddress,
      rn = ROW_NUMBER() OVER (PARTITION BY VisitorID ORDER BY CreateDate DESC)
    FROM dbo.VisitorSession
    WHERE UserIpAddress IS NOT NULL
    AND CreateDate > @dt
  ) AS x
  WHERE rn = 1
)
UPDATE c
  SET c.UserIpAddress = s.UserIpAddress
  FROM dbo.ShoppingCart AS c
  INNER JOIN s
  ON c.VisitorID = s.VisitorID;

También puede dividir esta operación en partes para reducir el impacto en el registro de transacciones que, a su vez, puede reducir la duración general. Escribí sobre esto aquí.

Así es como manejaría ese enfoque:

DECLARE 
  @dt DATE = DATEADD(MONTH, -6, SYSDATETIME()), 
  @rc INT = 1;

WHILE @rc > 0
BEGIN

  BEGIN TRANSACTION;

  ;WITH s AS 
  (
    SELECT TOP (100000) VisitorID, UserIpAddress FROM
    (
      SELECT 
        VisitorID,
        UserIpAddress,
        rn = ROW_NUMBER() OVER (PARTITION BY VisitorID ORDER BY CreateDate DESC)
      FROM dbo.VisitorSession AS s
      WHERE UserIpAddress IS NOT NULL
      AND CreateDate > @dt
      AND EXISTS
      ( 
        SELECT 1 FROM dbo.ShoppingCart AS c
          WHERE c.VisitorID = s.VisitorID
          AND (c.UserIpAddress <> s.UserIpAddress
          OR c.UserIpAddress IS NULL)
      )
    ) AS x
    WHERE rn = 1
  )
  UPDATE c
    SET c.UserIpAddress = s.UserIpAddress
    FROM dbo.ShoppingCart AS c
    INNER JOIN s
    ON c.VisitorID = s.VisitorID;

  SET @rc = @@ROWCOUNT;

  COMMIT TRANSACTION;
END

Por supuesto, como ilustra la publicación del blog, puede ganar casi la misma cantidad de tiempo si se asegura de que su registro sea lo suficientemente grande para manejar toda la transacción sin tener que crecer; la mayor parte del retraso probablemente se deba a muchas, muchas operaciones de crecimiento automático que se adaptan a su gran transacción. Lamentablemente, hasta que haya realizado la operación una vez, puede ser muy difícil intentar adivinar cuánto registro de transacciones necesitará …

Ejecuté una prueba contra la expresión de tabla común (CTE) de Aaron Bertrand. El rendimiento fue casi idéntico (07:56 para el CTE versículos 07:55 para la combinación, solo probando una vez cada uno).

A continuación, intenté la declaración fragmentada de Aaron (para evitar el bloqueo). Sin embargo, me encontré con un problema al agrupar los registros en cada pasada. El rendimiento cayó a niveles inaceptables debido a esta restricción en una mesa tan grande. Por el lado positivo, su formulación me dio una idea sobre cómo reducir el tiempo de ejecución. y evite el uso de MERGE.

Cambiar a un CTE fue una elección fácil. Reduciendo el conjunto de resultados de la mesa de unión más pequeña primero (el que tiene 9.000.000 de registros), el total de registros necesarios para unirse, y actualización, reducido significativamente.

/* results from shared development server:
    run #1:  00:52 with 193,379 rows affected
    run #2:  00:56 with 193,379 rows affected
    run #3:  02:19 with 193,379 rows affected
    run #4:  00:15 with 193,379 rows affected
    run #5:  01:59 with 193,379 rows affected
    run #6:  01:14 with 193,379 rows affected
*/
declare @dt datetime = cast(dateadd(month, -6, sysdatetime()) as date);

;with cte as (
    select distinct  -- 00:26, with 179,160 rows
        visitorid
    from ShoppingCart (nolock) sc
    where CreateDate > @dt
)
update ShoppingCart
set UserIpAddress = desired.UserIPAddress
from ShoppingCart
join (
    select *  -- 02:36 with 179,016 rows
    from (
        select
            vs.visitorid
            ,vs.useripaddress
            ,row_number() over
            (partition by vs.visitorid order by vs.createdate desc) as rownumber
        from visitorsession (nolock) vs
        join cte on vs.VisitorID = cte.VisitorID
        where vs.CreateDate > @dt
        and vs.UserIPAddress is not null
    ) as subTbl
    where rownumber = 1
) as desired on ShoppingCart.VisitorID = desired.VisitorID
where ShoppingCart.CreateDate > @dt;

La introducción de un conjunto de resultados mucho más pequeño del CTE en la sub-selección para la ACTUALIZACIÓN aumentó radicalmente el rendimiento en la ACTUALIZACIÓN. Preservando el Plazo de 6 meses La restricción en cada paso también ayudó a mantener el conjunto de resultados más pequeño para que SQL Server optimizara el rendimiento. La consulta final suele durar aproximadamente 1 minuto en el entorno de desarrollo. En producción, lo más probable es que esto se ejecute en mucho menos tiempo, y no espero que ninguna solicitud se agote con nuestra configuración actual.

Nos encantaría que puedieras difundir esta reseña si te fue útil.

---

---

Utiliza Nuestro Buscador