Replace XML Path Stuff Query

Question

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

Assuming you're on a recent (supported) version of SQL Server then use `STRING_AGG`. — Thom A, Sep 08 '21 at 18:00
If not, I didn't but you need those `DISTINCT`s in there; I suspect you have a problem with your joins (either the explicit or lateral). — Thom A, Sep 08 '21 at 18:01
Your outer query has a `DISTINCT` and a `GROUP BY` too; a sure sign of a problem. Your data will already be in distinct sets, so the `DISTINCT` is either redundant (and just results in performance degradation) or your `GROUP BY` is wrong. I would suggest the former. — Thom A, Sep 08 '21 at 18:08
What version (`select @@version`) of SQL Server are you using? — Stu, Sep 08 '21 at 19:25
@Larnu I will be making the changes and I will come back with the result. — Jose Navarro, Sep 08 '21 at 20:37
@Stu Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) - 14.0.3391.2 (X64) — Jose Navarro, Sep 08 '21 at 20:38
Then you're in luck, SQL Server 2017 supports `String_Agg()`, it is much more performant than using `for xml` — Stu, Sep 08 '21 at 20:45

Charlieface · Accepted Answer · 2021-09-09T17:44:33.473

0

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;

edited Sep 09 '21 at 17:44

answered Sep 08 '21 at 23:20

Charlieface

52,284
6
19
43

The difference in performance is abysmal but I don't get the same values. Old: NULL NULL NULL | GEN01 Generic Generic | FUG03 FuggleUK Fuggle, United Kingdom | FUG03 FuggleUK Fuggle, United Kingdom | CTZ01 CTZ CTZ – Jose Navarro Sep 09 '21 at 16:45
New: (There are no more nulls) GEN01 Generic Generic | FUG03,FUG03 FuggleUK,FuggleUK Fuggle, United Kingdom,Fuggle, United Kingdom | CTZ01,CTZ01,CTZ01 CTZ,CTZ,CTZ CTZ,CTZ,CTZ | – Jose Navarro Sep 09 '21 at 16:49
You can try left-joining instead, have edited – Charlieface Sep 09 '21 at 17:44

Replace XML Path Stuff Query

1 Answers1