Reports that rely on vAsset* or v*Asset views are too slow to load

Products

• IT Management Suite

Issue/Introduction

You may have some default or custom reports that are taking longer to return results than usual that rely on Views like vAssetResource, vActiveAsset, and vRetiredAsset, either directly or indirectly because of other stored procedures or Views like "vFixedAssetResourceStatus" or "vResourceTypeHierarchy".

 

 

Environment

ITMS 8.7.1, 8.7.2

Cause

Known issue. vAsset* and v*Asset views needed some optimizations.

Resolution

This issue has been resolved and a fix for this is currently targeted for our ITMS 8.7.3 Release.

The current workaround for customers with ITMS 8.7.1 and 8.7.2 is to manually update these views by running the following query:

--Update to dbo.vAssetResource
DROP VIEW dbo.vAssetResource

go

 

CREATE VIEW dbo.vAssetResource AS

SELECT rth.[Guid], rth.[ResourceTypeGuid], rth.[IsManaged] 

FROM vRM_Asset rth

go

 

 

--Update to vActiveAsset
DROP VIEW dbo.vActiveAsset

go

 

CREATE VIEW dbo.vActiveAsset AS

SELECT DISTINCT rth.[Guid], rth.[ResourceTypeGuid], rth.[IsManaged]

FROM vRM_Asset rth

LEFT OUTER JOIN ResourceAssociation resAssoc 

ON rth.Guid = resAssoc.ParentResourceGuid

AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'

WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL)

go

--Update to vRetiredAsset

DROP VIEW dbo.vRetiredAsset

go

 

CREATE VIEW dbo.vRetiredAsset AS

SELECT DISTINCT rth.[Guid], rth.[ResourceTypeGuid], rth.[IsManaged]

FROM vRM_Asset rth

LEFT OUTER JOIN ResourceAssociation resAssoc 

ON rth.Guid = resAssoc.ParentResourceGuid

AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'

WHERE (resAssoc.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489')

go