Need find the way to improve existing query run time-CodePudding

I have 3 tables (Contact, ContactBridge, ContactLabel).

ContactLabel table has all Label Names associated with Contact (can be 1 label, can be 50 labels). To access them I have to go through ContactBridge table that stores LabelId.

Goal was to retrieve all Label Names in one row for each Contact.

I was able to figured out the query, however it executes super long (~1000 records takes almost 2 minutes).

declare @Contact table
(ContactId INT, ContactName VARCHAR(100))

INSERT INTO @Contact
VALUES
(78561, 'Tom Cruise'),
(54721, 'Iron Man')


declare @ContactBridge table
(BridgeId INT, ContactId INT, LabelId INT)

INSERT INTO @ContactBridge
VALUES
(1, 78561, 12),
(2, 54721, 34),
(3, 78561, 23),
(4, 54721, 67),
(5, 54721, 78),
(6, 78561, 34),
(7, 78561, 45),
(8, 54721, 56)


declare @ContactLabel table
(LabelId INT, LabelName VARCHAR(100))

INSERT INTO @ContactLabel
VALUES
(12, 'Actor'),
(23, 'Los Angeles'),
(34, 'Rich'),
(45, 'Married'),
(56, 'Single'),
(67, 'New York'),
(78, 'Superhero')


SELECT * FROM @Contact as c

--- Contact Labels in 1 single row

OUTER APPLY (
    SELECT STUFF((
        (SELECT ', '   labels.LabelName
        FROM @ContactBridge AS bridge
            JOIN @ContactLabel AS labels
            ON labels.LabelId = bridge.LabelId
            WHERE bridge.ContactId = c.ContactId
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')),1,1,''
        ) AS ContactLabels
    ) AS cl

Is there a way to make query run faster?

CodePudding user response：

Use string_agg as follows

SELECT C.ContactId,ContactName,
    string_agg(LabelName,',') AS ContactLabels
FROM @Contact C
JOIN @ContactBridge CB ON CB.ContactId = C.ContactId
JOIN @ContactLabel CL ON CL.LabelId = CB.LabelId
GROUP BY c.ContactId,ContactName
ORDER BY ContactName DESC, string_agg(LabelName,',') ASC