Filter Page Slow to Load in the Console

Products

• IT Management Suite

Issue/Introduction

Manage Filters slow to load.  Each time a user clicks on a filter it takes over 40 seconds to load.  All other pages load find and there appear to be no performance issues on the NS.

No errors noted in SMP logs.

A profiler traces indicates that each time a filter selected the stored procedure spGetParameterTypes is called three times taking over 14 seconds each.

Cause

 The performance of stored procedure spGetParameterTypes is bad, in one instance it was causing filters to take 40 seconds to load.  

Resolution

Change the stored procedure to use the String table instead of the StringCache view.

 

1. Open SQL Management Studio
2. Locate the existing stored procedure under Symantec_CMDB> Programability> Stored Procedures and rename it to spGetParameterTypes_old. 
3. Execute the following code in SQL to create a new stored procedure which will use the Sting table instead of the String Cache:

create proc dbo.spGetParameterTypes

               @interfaceguid uniqueidentifier,

               @culture nvarchar(255)

             as

             begin

                    SELECT DISTINCT vNonResourceItem.Guid, ISNULL(String.String, vNonResourceItem.Name) as Name

                    FROM vNonResourceItem

                    JOIN ClassInterface ci ON ci.ClassGuid = vNonResourceItem.ClassGuid

                    LEFT OUTER JOIN String ON String.BaseGuid = vNonResourceItem.Guid

                    AND String.StringRef = 'item.name'

                    AND String.Culture = @culture

                    WHERE ci.InterfaceGuid = @interfaceguid

                    AND (vNonResourceItem.Attributes & 1) != 1                               

             end

Applies To

 7.5 SP1 HF3