Slow rendering of components with a large tree structure

book

Article ID: 170196

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

This can be manifest in several different ways:

  • When switching between tabs some load slower
  • A timeout error occurs 
    HTTP Request failed:  /Altiris/Console/tree.aspx?ViewGuid=a57fb0e9-0676-4e00-929a-6bb37dc1f888&&ConsoleGuid=1b22db4e-a898-443f-9b99-855b1653d3f5
  • Expanding trees in a tab can take longer than normal

 

NA

Cause

The cause is SQL parameter sniffing on the stored procedure spGetLocalisedItemStrings.

 

Environment

ITMS 8.x

Resolution

This issue has been reported to Symantec Development team.

Run the following SQL in Management Studio to update the problematic stored procedure in a way that will help to alleviate the problem.

This can also be found in https://support.symantec.com/en_US/article.tech250925.html?

 

ALTER PROC [dbo].[spGetLocalisedItemStrings]
      @BaseGuids  varchar(MAX),
      @StringRef  nvarchar(255),
      @Culture    varchar(10)
AS
BEGIN
    DECLARE      @GuidCnt int = 0
    CREATE TABLE #Guids ( BaseGuid UNIQUEIDENTIFIER PRIMARY KEY )
    INSERT INTO  #Guids ( BaseGuid )
        SELECT   DISTINCT ui FROM dbo.fnListToGuidTableDal2InLine( @BaseGuids )
    SET @GuidCnt = @@ROWCOUNT

    DECLARE      @Cults TABLE ( Culture varchar(10), Priority int )
    INSERT INTO  @Cults
        SELECT   Culture, Priority FROM dbo.fnGetBaseCultures( @Culture )

    DECLARE      @Ref int
    SELECT       @Ref = CASE WHEN @StringRef = 'item.name'        THEN 1
                             WHEN @StringRef = 'item.description' THEN 2
                                                                  ELSE 0 END

    IF EXISTS ( SELECT TOP 1 1 FROM @Cults WHERE Culture = 'en' )
       AND    ( @Ref > 0 )
       AND  ( ( SELECT   COUNT(*)
                    FROM Item   i
                    JOIN #Guids g ON g.BaseGuid = i.[Guid]
              ) = @GuidCnt
            )
    BEGIN
        SELECT   [Guid] AS [BaseGuid],
                 CASE WHEN @Ref = 1 THEN [Name] ELSE [Description] END AS [LocalizedString]  
            FROM Item   i
            JOIN #Guids g ON g.BaseGuid = i.[Guid]
    END
    ELSE
    BEGIN
        SELECT      BaseGuid,
                    String AS [LocalizedString]
            FROM  (
                    SELECT    s.BaseGuid, s.String, RANK() OVER(PARTITION BY s.BaseGuid ORDER BY c.Priority DESC) AS [StringRank]
                        FROM  #Guids g
                        JOIN  String s ON s.BaseGuid = g.BaseGuid AND s.StringRef = @StringRef
                        JOIN  @Cults c ON c.Culture = s.Culture
                        WHERE s.String <> ''
                  ) AS l
            WHERE  StringRank = 1
    END

    DROP TABLE #Guids
END

GO