Slow rendering of components with a large tree structure

Products

• IT Management Suite
• Client Management Suite

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

 

Environment

ITMS 8.x

Cause

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

 

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.

 

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