How to optimise a query with multiple subqueries for string aggregation?

Question

This is my query:

SELECT
    wp.WorkplanID,
    STUFF((SELECT ', ' + ISNULL(ul.FirstName + ' ', '') + ISNULL(ul.LastName, '')
           FROM UserLogin ul
           INNER JOIN Vendors v ON ul.UserLoginID = v.UserLoginID
           INNER JOIN dbo.WorkPlanVendors wpv ON wpv.VendorID = CAST(v.VendorID AS INT)
           WHERE wpv.WorkPlanID = wp.WorkPlanID
           FOR XML PATH('')), 1, 2, '') AS VendorName,
    STUFF((SELECT ', ' + v.PrimaryPhone
           FROM UserLogin ul
           INNER JOIN Vendors v ON ul.UserLoginID = v.UserLoginID
           INNER JOIN dbo.WorkPlanVendors wpv ON wpv.VendorID = CAST(v.VendorID AS INT)
           WHERE wpv.WorkPlanID = wp.WorkPlanID
           FOR XML PATH('')), 1, 2, '') AS PrimaryPhone,
    STUFF((SELECT ', ' + ul.Email
           FROM UserLogin ul
           INNER JOIN Vendors v ON ul.UserLoginID = v.UserLoginID
           INNER JOIN dbo.WorkPlanVendors wpv ON wpv.VendorID = CAST(v.VendorID AS INT)
           WHERE wpv.WorkPlanID = wp.WorkPlanID
           FOR XML PATH('')), 1, 2, '') AS Email
FROM WorkPlan wp

It is taking 5 seconds to get data but I want to make it faster.

Answer 1

   ;

WITH CTE
AS (
    SELECT wp.WorkplanID
        ,ISNULL(ul.FirstName + ' ', '') + ISNULL(ul.LastName, '') AS FULLNAME
        ,v.PrimaryPhone
        ,ul.Email
    FROM WorkPlan wp
    INNER JOIN WorkPlanVendors wpv ON wpv.WorkPlanID = wp.WorkPlanID
    INNER JOIN Vendors v ON wpv.VendorID = CAST(v.VendorID AS INT)
    INNER JOIN UserLogin ul ON ul.UserLoginID = v.UserLoginID
    )
SELECT C.WorkplanID
    ,STUFF((
            SELECT ', ' + FULLNAME
            FROM CTE CFN
            WHERE C.WorkPlanID = CFN.WorkPlanID
            FOR XML PATH('')
            ), 1, 2, '') AS FULLNAME
    ,STUFF((
            SELECT ', ' + PrimaryPhone
            FROM CTE CPP
            WHERE C.WorkPlanID = CPP.WorkPlanID
            FOR XML PATH('')
            ), 1, 2, '') AS PrimaryPhone
    ,STUFF((
            SELECT ', ' + Email
            FROM CTE CEM
            WHERE C.WorkPlanID = CEM.WorkPlanID
            FOR XML PATH('')
            ), 1, 2, '') AS Email
FROM CTE C

Answer 2

You used the same query for three columns. You can use a common table expression to join the tables once and use this reference in your query as below:

WITH CTE_WorkPlanVendors AS (
    SELECT wpv.WorkPlanID AS VendorWorkPlanID, ul.FirstName, ul.LastName, v.PrimaryPhone, ul.Email
    FROM UserLogin ul
    INNER JOIN Vendors v ON ul.UserLoginID = v.UserLoginID
    INNER JOIN dbo.WorkPlanVendors wpv ON wpv.VendorID = CAST(v.VendorID AS INT)
)
SELECT wp.WorkplanID,
       STUFF((SELECT ', ' + ISNULL(FirstName + ' ', '') + ISNULL(LastName, '')
              FROM CTE_WorkPlanVendors cte
              WHERE cte.VendorWorkPlanID = wp.WorkPlanID
              FOR XML PATH('')), 1, 2, '') AS VendorName,
       STUFF((SELECT ', ' + PrimaryPhone
              FROM CTE_WorkPlanVendors cte
              WHERE cte.VendorWorkPlanID = wp.WorkPlanID
              FOR XML PATH('')), 1, 2, '') AS PrimaryPhone,
       STUFF((SELECT ', ' + Email
              FROM CTE_WorkPlanVendors cte
              WHERE cte.VendorWorkPlanID = wp.WorkPlanID
              FOR XML PATH('')), 1, 2, '') AS Email
FROM WorkPlan wp;

Answer 3

The existing answers do not help as they all query the base table many times.

You can use the following solution to query the base table once and aggregate it up:

• In an APPLY, use FOR XML PATH('row') to aggregate up the entire dataset into one XML. Add in the commas.
• In the SELECT, query each column as follows:
  • .query('row/TheColumn/text()') this gets you all the text elements for that name.
  • .value('text()[1]', 'nvarchar(max)') this unescapes any XML encoding.
  • Use STUFF to strip off the leading comma.

SELECT
  wp.WorkplanID,
  STUFF(x.x.query('row/Name/text()'        ).value('text()[1]', 'nvarchar(max)'), 1, 2, '') AS VendorName,
  STUFF(x.x.query('row/PrimaryPhone/text()').value('text()[1]', 'nvarchar(max)'), 1, 2, '') AS PrimaryPhone,
  STUFF(x.x.query('row/Email/text()'       ).value('text()[1]', 'nvarchar(max)'), 1, 2, '') AS Email
FROM (
    SELECT wp.WorkplanID
    FROM WorkPlan wp
    GROUP BY wp.WorkplanID
) wp
CROSS APPLY (
    SELECT
      ',' + ISNULL(FirstName + ' ', '') + ISNULL(LastName, '') AS Name
      ',' + v.PrimaryPhone AS PrimaryPhone,
      ',' + ul.Email AS Email
    FROM UserLogin ul
    INNER JOIN Vendors v ON ul.UserLoginID = v.UserLoginID
    INNER JOIN dbo.WorkPlanVendors wpv ON wpv.VendorID = CAST(v.VendorID AS INT)
    WHERE wpv.WorkPlanID = wp.WorkPlanID
    FOR XML PATH('row')
) x(x);

Obviously in newer versions of SQL Server you don't need any of this, you can just use STRING_AGG.