sqlt-sqlfor-xml-pathstuff

Replace XML Path Stuff Query


I would like to replace the following query as I have performance problems. The execution plan is giving me a lot of redisual due to Table Spool operators (Lazy Spool)

SELECT DISTINCT LOT_0, 
VarCode = STUFF(
    (
    SELECT DISTINCT ', ' + IT6.ID_0
    FROM  TOLOT S
        INNER JOIN  ETMM I ON I.ITMREF_0=S.ITMREF_0
        INNER JOIN  SICOD6 IT6 ON IT6.ID_0=I.TSICOD_6
    WHERE IT6.LNGDES_0 <>'' AND LOT_0 <> '' AND TSICOD_0 = 'OP'
    AND S.LOT_0=S2.LOT_0
    FOR XML PATH (''), TYPE).value ('.[1]', 'nvarchar(max)'), 1, 2, ''
    ),
VarShort = STUFF(
    (
    SELECT DISTINCT ', ' + IT6.SHODES_0
    FROM  TOLOT S
        INNER JOIN  ETMM I ON I.ITMREF_0=S.ITMREF_0
        INNER JOIN  SICOD6 IT6 ON IT6.ID_0=I.TSICOD_6
    WHERE IT6.LNGDES_0 <>'' AND LOT_0 <> '' AND TSICOD_0 = 'OP'
    AND S.LOT_0=S2.LOT_0
    FOR XML PATH (''), TYPE).value ('.[1]', 'nvarchar(max)'), 1, 2, ''
    )
,VarLong = STUFF(
    (
    SELECT DISTINCT ', ' + IT6.LNGDES_0
    FROM  TOLOT S
        INNER JOIN  ETMM I ON I.ITMREF_0=S.ITMREF_0
        INNER JOIN  SICOD6 IT6 ON IT6.ID_0=I.TSICOD_6
    WHERE IT6.LNGDES_0 <>'' AND LOT_0 <> '' AND TSICOD_0 = 'OP'
        AND S.LOT_0=S2.LOT_0
    FOR XML PATH (''), TYPE).value ('.[1]', 'nvarchar(max)'), 1, 2, ''
    )
FROM  TOLOT AS S2
GROUP BY LOT_0

I would appreciate your ideas


Solution

  • Because you are on SQL Server 2017, you can simply use STRING_AGG

    SELECT
      S.LOT_0, 
      VarCode  = STRING_AGG(IT6.ID_0, ', '),
      VarShort = STRING_AGG(IT6.SHODES_0, ', '),
      VarLong  = STRING_AGG(IT6.LNGDES_0, ', ')
    FROM TOLOT AS S
    LEFT JOIN ETMM I
        (INNER JOIN SICOD6 IT6 ON IT6.ID_0 = I.TSICOD_6)
      ON I.ITMREF_0 = S.ITMREF_0
      AND IT6.LNGDES_0 <> '' AND TSICOD_0 = 'OP'
    WHERE S.LOT_0 <> ''
    GROUP BY
      S.LOT_0;