How to improve my query performance SQL Server-CodePudding

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]

CodePudding user response：

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]

CodePudding user response：

First these are not temp tables, these are table variables and SQL Server has hard coded statics for them, so estimation is always way off and they suck in that matter.

So if you just use temp table instead and (maybe add index on them) will help a lot:

create table #installs
    (
        UserName varchar(200),
        DeviceName varchar(500),
        FirstSeenDate datetime
    )

insert into #installs
SELECT  [Username] 
....