How to improve performance of FOR XML PATH with large data set

Question

I am attempting to use the FOR XML PATH technique of concatenating strings from a result set, but am running into some performance issues. I'm wondering if there's any way to improve this type of query.

I am writing a query for a report that is used to check inventory balances for all 15000 rows of a Part table. These parts can be used on one or many pieces of equipment stored in an Equipment table, which also has around 12000 rows. The clients would like the report to have a comma separated field of the pieces of equipment on which each part is used.

I started by creating a query that returns the full join of parts and equipment

SELECT PartName, Balance, EquipmentName as UsedOn 
FROM Part p
    LEFT OUTER JOIN EquipmentPart ep ON p.PartID = ep.PartID
    LEFT INNER JOIN Equipment e ON ep.EquipmentID = e.EquipmentID

This returns a result set of 49000 rows in 1 second (according to SQL Server Management Studio) but wasn't accomplishing the end-goal.

I then tried to just focus on getting the comma separated field by doing something like the following

SELECT PartName, Balance, 
      STUFF((SELECT ',' + EquipmentName
        FROM Equipment e JOIN EquipmentPart ep ON e.EquipmentID = ep.EquipmentID
        WHERE ep.PartID = p.PartID
        FOR XML PATH('')) as UsedOn
FROM Part p

This took over 8 minutes before I finally killed it; it had only made it through 8000 of the 15000 rows.

My current solution combines the first query with the FOR XML PATH sub-query

WITH PartBalances AS
(SELECT PartID, PartName, Balance, EquipmentName as UsedOn 
FROM Part p
    LEFT OUTER JOIN EquipmentPart ep ON p.PartID = ep.PartID
    LEFT INNER JOIN Equipment e ON ep.EquipmentID = e.EquipmentID
)
SELECT PartName, Balance,
       STUFF ((SELECT ',' + UsedOn
        FROM PartBalances p2
        WHERE p1.PartID = p2.PartID
        FOR XML PATH(''))
FROM PartBalances p1
GROUP BY PartID, PartName, Balance

This does seem to return what I want, but it takes a minute to do. One minute is sort of acceptable, but I was hoping for something a lot better.

Is there anything I'm doing wrong or stupidly? Is there a way to avoid making 15000 sub-queries?

i think slow performance due to left outer join you should not use left outer join — Khurram Ali, Mar 10 '15 at 16:10
Sorry, the left outer join in the long-running query was a copy-paste error. I'm simplifying the query from my actual environment. — Matt J, Mar 10 '15 at 16:37

score 1 · Answer 1 · answered Mar 10 '15 at 16:19

WITH PartBalances AS
(SELECT PartID, PartName, Balance, EquipmentName as UsedOn 
FROM Part p
    JOIN EquipmentPart ep ON p.PartID = ep.PartID
    JOIN Equipment e ON ep.EquipmentID = e.EquipmentID
)
SELECT PartName, Balance,
       STUFF ((SELECT ',' + UsedOn
        FROM PartBalances p2
        WHERE p1.PartID = p2.PartID
        FOR XML PATH(''))
FROM PartBalances p1
GROUP BY PartID, PartName, Balance

score 0 · Answer 2 · answered Mar 10 '15 at 16:10

0

Can you try running this version of the query?

SELECT PartName, Balance, 
       STUFF((SELECT ',' + EquipmentName
              FROM EquipmentPart ep JOIN
                   Equipment e
                   ON e.EquipmentID = ep.EquipmentID
              WHERE ep.PartID = p.PartID
              FOR XML PATH('')
             ), 1, 1, '') as UsedOn
FROM Part p;

The LEFT JOIN could be confusing the optimizer.

answered Mar 10 '15 at 16:10

Gordon Linoff

1,242,037
58
646
786

Sorry, the LEFT JOIN in the sub-query was a copy-paste issue from me trying to simplify my real-world query. My actual, super slow query does use just JOIN. – Matt J Mar 10 '15 at 16:41
@MattJ . . . Create an index on `EquipmentPart(PartId, EquipmentId)`. I am guessing you already have an index on `EquipmentPart(EquipmentId, PartId)`. – Gordon Linoff Mar 10 '15 at 22:45

How to improve performance of FOR XML PATH with large data set

2 Answers2