How can I improve the run time of a query that groups / display same date data in one row?

Question

I currently have a view that contains about 1k rows of user information. currently i have a query that groups all the same user information by date into one row. Currently this query takes between 20 to 30 minutes to complete its execution. is there any way to enhance the query? or cerate a new one that improves its execution time?

Declare @YourTable Table ([AccountNo] int,[RunKey] int,[RunDate] date,[Address] varchar(50),[Salary] int,[PromotionDate] date)
    Insert Into @YourTable Values 
     (12345,2,'06/20/2017','123 Main Street',60000,'01/15/2017')
    ,(12345,3,'06/21/2017','123 Main Street',60000,'01/15/2017')
    ,(12345,4,'06/22/2017','123 Main Street',65000,'06/21/2017');
    
    with ct as
    (
    select  A.AccountNo
           ,A.RunKey
           ,A.RunDate
           ,B.Item
           ,B.Value
    from   @YourTable A
    cross apply (values  ('Address'      ,cast(A.[Address] as varchar(max)))
                        ,('Salary'       ,cast(A.[Salary]  as varchar(max)))
                        ,('PromotionDate',cast(A.[PromotionDate] as varchar(max)))
                     ) B (Item,Value)
    )
    select  a.AccountNo
           ,a.RunKey
           ,a.RunDate
           ,stuff ((select (',' + Item) from ct b 
                    where b.AccountNo=a.AccountNo 
                          and b.RunKey=a.RunKey 
                          and b.RunDate=a.RunDate
                          for xml path(''), type ).value('.', 'nvarchar(MAX)'),1,1,'') Item
            ,stuff ((select (',' + Value) from ct b 
                    where b.AccountNo=a.AccountNo 
                          and b.RunKey=a.RunKey 
                          and b.RunDate=a.RunDate
                          for xml path(''), type ).value('.', 'nvarchar(MAX)'),1,1,'') Value
    from   ct a
    group by a.AccountNo, a.RunKey, a.RunDate

Answer 1

I don't totally get the purpose of it, but from checking at your query, it looks like you're moving Address, Salary and PromotionDate to different rows, and then you're merging all together. One option is to avoid that expansion and later merge by doing all together in the same operation:

SELECT a.AccountNo
        ,a.RunKey
        ,a.RunDate
        ,Item = STUFF((SELECT ',' + 'Address,Salary,PromotionDate'
            FROM @YourTable b 
            WHERE a.AccountNo = b.AccountNo AND a.RunKey = b.RunKey AND a.RunDate = b.RunDate
            FOR XML PATH('')), 1, 1, '')
        ,Value = STUFF((SELECT ',' + b.Address + ',' + CAST(b.Salary AS VARCHAR(10)) + ',' + CAST(b.PromotionDate AS VARCHAR(10))
            FROM @YourTable b 
            WHERE a.AccountNo = b.AccountNo AND a.RunKey = b.RunKey AND a.RunDate = b.RunDate
            FOR XML PATH('')), 1, 1, '')
FROM @YourTable AS a
GROUP BY a.AccountNo
        ,a.RunKey
        ,a.RunDate

Or even easier if you're using SQL 2017 or later (there might be some issues with this version though, if the output of the Item or Value columns is too long):

SELECT a.AccountNo
        ,a.RunKey
        ,a.RunDate
        ,Item = STRING_AGG('Address,Salary,PromotionDate', ',')
        ,Value = STRING_AGG(a.Address + ',' + CAST(a.Salary AS VARCHAR(10)) + ',' + CAST(a.PromotionDate AS VARCHAR(10)), ',')
FROM @YourTable AS a
GROUP BY a.AccountNo
        ,a.RunKey
        ,a.RunDate

Also, make sure there's a clustered index on AccountNo, RunKey, RunDate or at least one covering all the columns included in the query.