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

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