"All Resources Picker Report" in DataCenter Types > Services webpart is running too long and causing timeout at the end

Products

• IT Management Suite

Issue/Introduction

"All resources Picker report" is being used in Home>Service and Asset management>Manage Configuration Items>DataCenter Types>Services webpart
In large environments it may take up to 8 minute to run underlying SQL query "All Resources Picker Query"{41e81023-8451-4c08-9eef-5095acf01abf}

DECLARE @ResourceType uniqueidentifier
   SET @ResourceType = 'cacccabb-225a-4611-83ce-5c56bd8de562'
DECLARE @TrusteeScope nvarchar(max)
   SET @TrusteeScope = '{2E1F478A-4986-4223-9D1E-B5920A63AB41},{451A954A-7A74-
446B-BCEA-2D3165783531},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{B760E9A9-E4DB-
404C-A93F-AEA51754AA4F}'
DECLARE @Culture nvarchar(10)
   SET @Culture = 'pl-PL'
DECLARE @UnnamedResource nvarchar(max)
    SET @UnnamedResource = 
[dbo].fnLocalizeStringByGuid('text.UnnamedResource', cast(0x0 as 
uniqueidentifier), @Culture)
DECLARE @TrusteeScopeCollections table(Trustees nvarchar(max))
    INSERT INTO @TrusteeScopeCollections SELECT * FROM 
[dbo].fnGetTrusteeScopeCollections(@TrusteeScope) 
DECLARE @ResourceGuid table(ResourceGuid uniqueidentifier) 
    INSERT INTO @ResourceGuid select ResourceGuid from ScopeMembership 
    where ScopeCollectionGuid in (select ScopeCollectionGuid from 
@TrusteeScopeCollections)
DECLARE @ResourceTypeGuid table(ResourceTypeGuid uniqueidentifier) 
    INSERT INTO @ResourceTypeGuid select ResourceTypeGuid from 
ResourceTypeHierarchy 
    where BaseResourceTypeGuid = @ResourceType 
    
SELECT r.[Guid] AS [_ItemGuid],     
    rtl.String AS [Resource Type],
    case when (ril.String is null) or (ltrim(rtrim(ril.String))='')
    then @UnnamedResource else ril.String
    end as [Name],
    r.[ResourceTypeGuid] AS [_ResourceTypeGuid]  
FROM   [vRM_Resource_Item] r  
    outer apply 
fnGetLocalizedString(N'item.name',r.ResourceTypeGuid,@Culture) AS rtl
    outer apply fnGetLocalizedString(N'item.name',r.Guid,@Culture) AS ril
WHERE      
    r.Guid in (select ResourceGuid from @ResourceGuid)   
    and  ((r.ResourceTypeGuid in (select ResourceTypeGuid from 
@ResourceTypeGuid)) 
    or    (@ResourceType = '00000000-0000-0000-0000-000000000000'))  
ORDER BY     
    [Resource Type] ASC,     
    [Name] ASC

Altiris Profiler trace will show more than 5 minutes processing. It will finish successfully on SQL side, but time outs in console.

Resolution

There is a workaround to increase default timeout value for that webpart.
To do so, please open <Installpath\Altiris\CMDB\Web\AssetContractCommon\Web.config file in any text editor. On the config file locate following section:

<system.web>
    <!-- set aspnet execution timeout -->
    <httpCookies httpOnlyCookies="true" />
    <!-- Added For AJAX Support-->
    <!-- End of AJAX Support-->
    <httpRuntime executionTimeout="300" requestValidationMode="2.0" />
    <!--ensures that all web service protocols are enabled-->
    <webServices>

Change default executionTimeout value from 300 to 600 > 10 minutes. Save file, restart IIS, then check the webpart. It will run as long as SQL qeury runs but won't timeout at the end.

Improved SQL code to decrease report run time will be added in 7.6 HF4.