How to improve my query performance SQL Server

Question

I have a temp table inside my procedure which joins with another temp table and it seems taking some time to run. Could anyone suggest how to speedup this.

Below is my piece of code:

    declare @installs table
    (
        UserName varchar(200),
        DeviceName varchar(500),
        FirstSeenDate datetime
    )

    insert into @installs
    SELECT  [Username] 
          ,[Device Name]
          ,min([First Seen]) as 'Install Date'
      FROM [DataCollection].[dbo].[iBoss_Installed_Users]
      where [Device Type] not like '%Server%'
      group by [Device Name], Username
      

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

    )

    insert into @installs_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

I would avoid using a table variable or temporary table, and instead use a common table expression. I'd also use GROUP BY rather than DISTINCT, so the optimiser knows it doesn't have to try de-duplicating your list of dates...

declare @installs_User table
(
    UserName varchar(200),
    InstalledDate varchar(max)
);

WITH
    installs AS
(
    SELECT [Username] 
          ,[Device Name]
          ,min([First Seen]) as 'Install Date'
    FROM [DataCollection].[dbo].[iBoss_Installed_Users]
    where [Device Type] not like '%Server%'
    group by [Device Name], Username
) 
insert into
    @installs_User
SELECT main.UserName
      ,left(main.installs,len(main.installs)-1) as "Installs"
From
(
    SELECT
        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
    GROUP BY
        ins2.UserName
)
    [Main]