Query Performance Tuning

Question

Hello All,

I have a temp table inside my procedure and its taking lot of time.
Could anyone suggest how to optimize this..

Below is my piece of code.

    declare @User table
    (
        UserName varchar(200),
        InstalledDate varchar(max)

    )

    insert into @User
    select main.UserName,
            left(main.installs,len(main.installs)-1) as "Installs"
        From
        (
        select distinct ins2.UserName,
            (
                select convert(varchar(200),ins.FirstSeenDate)+', ' as [text()]
                from @installs ins
                where ins.UserName=ins2.UserName
                order by ins.Username
                for XML PATH('')
            ) [installs]
            from @installs ins2
        )[Main]

Answer 1

Check two alternatives:

insert into @User (UserName, InstalledDate)
select UserName, string_agg( FirstSeenDate, ', ') within group (order by FirstSeenDate)
from @Installs
group by UserName

and

insert into @User (UserName, InstalledDate)
select D.UserName,
    stuff(( select concat(', ', FirstSeenDate) from @Installs where UserName = D.UserName order by FirstSeenDate for xml path('')), 1, 2, '')
from ( select distinct UserName from @Installs ) D

Answer 2

Hi @Learner ,

Please also check:

CREATE FUNCTION fn_hb(@UserName VARCHAR(100))  
RETURNS VARCHAR(8000)  
AS   
BEGIN  
DECLARE @Rst VARCHAR(8000)  
SET @Rst=''  
SELECT @Rst=@Rst+FirstSeenDate+',' FROM @Installs   
                                   WHERE UserName=@UserName                                     
SET @Rst=Left(@Rst,LEN(@RSt)-1)  
RETURN @Rst  
END  
GO  
  
INSERT INTO @User (UserName, InstalledDate)  
SELECT UserName,dbo.fn_hb(UserName) AS FirstSeenDate  
FROM @Installs  
GROUP BY UserName;  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Or:

CREATE FUNCTION fn_test(@UserName VARCHAR(100))  
RETURNS varchar(8000)  
AS  
BEGIN  
DECLARE @date_str VARCHAR(8000)  
SELECT @date_str = ISNULL(@date_str + ',','') + CONVERT(varchar(200),FirstSeenDate)  
FROM @Installs  
WHERE UserName= @UserName  
RETURN @date_str  
END  
GO  
  
DECLARE @User TABLE  
(UserName varchar(200),  
InstalledDate varchar(max)  
);  
INSERT INTO @User (UserName, InstalledDate)  
SELECT UserName,dbo.fn_test(UserName)   
FROM @Installs  
GROUP BY UserName;  

Regards,
Echo