NS.Nightly schedule to associate Software component to software product is taking hours to complete

book

Article ID: 170713

calendar_today

Updated On:

Products

Software Management Solution

Issue/Introduction

NS.Nightly schedule to associate Software component to software product is taking hours to complete.

Cause

This is a defect.

Environment

8.1 - 8.1 RU4

Resolution

This issue is fixed in 8.1 RU5.

For previous versions of 8.1 please run through the following steps:

  1. Open SQL Management Studio and run the following query against the database:
    ALTER PROC [dbo].spInvGetCountOfSoftwareComponentsApplicableForAssociation
      @count INT OUTPUT    
    
    AS
    BEGIN
    
    set @count=(select count (distinct component.Guid)
    from vRM_Software_Component_Item component
    Left Join ResourceAssociation ra On ra.ParentResourceGuid = component.Guid  And
    ra.ResourceAssociationTypeGuid = '292dbd81-1526-423a-ae6d-f44eb46c5b16'
    --Software Component To Company
    Left Join vCompany company on company._ResourceGuid = ra.ChildResourceGuid
    Left Join Inv_Software_Component isc On component.Guid = isc._ResourceGuid 
    INNER JOIN Inv_InstalledSoftware isw WITH (NOLOCK) on
    component.Guid=isw._SoftwareComponentGuid 
    Where (component.Attributes & 1 <> 1)
    and component.Guid NOT IN  (SELECT ChildResourceGuid FROM
    dbo.ResourceAssociation  WHERE ResourceAssociationTypeGuid =
    '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483')  -- Software component to product
    and (isc.PMImport IS NULL or isc.PMImport=0)
    and isw.InstallFlag=1)
    
    return @count
    
    END
  2. Now run the following query against the database:
    ALTER PROC [dbo].spInvGetAllSoftwareComponentsAssociations
    @filter AS varchar(10) -- filter guids by first leters f.e 'A%', '23%'
    AS
    BEGIN
    
    create TABLE #temp (Guid UNIQUEIDENTIFIER, [FilterString] nvarchar(250))
    
    insert into #temp
    select distinct component.Guid, '{{'+LOWER
    (component.Name)+'}}{('+LOWER(ISNULL(company.Name,
    ''))+')}{<'+LOWER(ISNULL(isc.Version, ''))+'>}'
    from vRM_Software_Component_Item component
    Left Join ResourceAssociation ra On ra.ParentResourceGuid = component.Guid  And
    ra.ResourceAssociationTypeGuid = '292dbd81-1526-423a-ae6d-f44eb46c5b16'
    --Software Component To Company
    Left Join vCompany company on company._ResourceGuid = ra.ChildResourceGuid
    Left Join Inv_Software_Component isc On component.Guid = isc._ResourceGuid 
    INNER JOIN Inv_InstalledSoftware isw WITH (NOLOCK) on
    component.Guid=isw._SoftwareComponentGuid 
    Where (component.Attributes & 1 <> 1)
    and component.Guid NOT IN  (SELECT ChildResourceGuid FROM
    dbo.ResourceAssociation  WHERE ResourceAssociationTypeGuid =
    '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483')  -- Software component to product
    and (isc.PMImport IS NULL or isc.PMImport=0)
    and isw.InstallFlag=1
    and component.Guid like @filter
    
    Select spf._ResourceGuid as ProductGuid, component.Guid as ComponentGuid, ISNULL
    (sps.IsManaged, 0) as IsManaged
    From #temp component 
    join Inv_NormalizedSoftwareProductFilters spf on
    (spf.filter1 is NULL or (spf.filter1 like '!%' and component.FilterString not
    like substring (spf.filter1, 2, 128)) or component.FilterString like
    spf.filter1) and
    (spf.filter2 is NULL or (spf.filter2 like '!%' and component.FilterString not
    like substring (spf.filter2, 2, 128)) or component.FilterString like
    spf.filter2) and
    (spf.filter3 is NULL or (spf.filter3 like '!%' and component.FilterString not
    like substring (spf.filter3, 2, 128)) or component.FilterString like
    spf.filter3) and
    (spf.filter4 is NULL or (spf.filter4 like '!%' and component.FilterString not
    like substring (spf.filter4, 2, 128)) or component.FilterString like
    spf.filter4) and
    (spf.filter5 is NULL or (spf.filter5 like '!%' and component.FilterString not
    like substring (spf.filter5, 2, 128)) or component.FilterString like
    spf.filter5) and
    (spf.filter6 is NULL or (spf.filter6 like '!%' and component.FilterString not
    like substring (spf.filter6, 2, 128)) or component.FilterString like
    spf.filter6) and
    (spf.filter7 is NULL or (spf.filter7 like '!%' and component.FilterString not
    like substring (spf.filter7, 2, 128)) or component.FilterString like
    spf.filter7) and
    (spf.filter8 is NULL or (spf.filter8 like '!%' and component.FilterString not
    like substring (spf.filter8, 2, 128)) or component.FilterString like
    spf.filter8) and
    (spf.filter9 is NULL or (spf.filter9 like '!%' and component.FilterString not
    like substring (spf.filter9, 2, 128)) or component.FilterString like
    spf.filter9) and
    (spf.filter10 is NULL or (spf.filter10 like '!%' and component.FilterString not
    like substring (spf.filter10, 2, 128)) or component.FilterString like spf.filter10) 
    left join Inv_Software_Product_State sps on sps._ResourceGuid=spf._ResourceGuid
    
    drop table #temp
    END