The vComputer view provides an easy access to managed computers, however in very large reporting server this view (built on many other abstration) is taking a lot of time to run and is not the most efficient base to built report. How could we provide similar information without the overhead?

The following SQL query will return the guid, name, IsManaged, server (based on OwnerNSGuid) from the database using 3 tables and 2 joins.

create view dbo.vComputer2 as
select ir.Guid, ir.IsManaged, im.Name, ns.name as 'Server'
  from ItemResource ir
  join Item im
    on ir.guid = im.guid
  join Item ns
    on im.ownernsguid = ns.guid
 where ResourceTypeGuid='493435F7-3B17-4C4C-B07F-C23E7AB7781F'

On a reporting server with 60,000+ managed computers this query ran in 12 seconds returning 62,921 rows. In comparison the vComputer query failed to return results on the same server due to the heavy use of the vComputer table (the process was a deadlock victim after 60~120s). This view built on the vItem, VComputerResource, vActiveAssets and vResourceHierarchy and also brings back data from the basic inventory which is not always used.