Query Performance Tuning

Learner 226 Reputation points
2021-12-02T16:18:53.253+00:00

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]
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,312 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,522 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 110.1K Reputation points
    2021-12-02T17:59:33.967+00:00

    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
    
    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-12-03T02:22:53.647+00:00

    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".


  3. EchoLiu-MSFT 14,571 Reputation points
    2021-12-03T05:02:55.793+00:00

    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

    0 comments No comments