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 Continue reading...