Hello, (This is not being posted to SQL newsgroup perhaps because it requires a knowledge of table design. Also this does not strictly fall under inventory category either, but I could be wrong) I want to modify the standard SCCM report 'Software 01A - Summary of installed software in a specific collection' to include multiple collections. Here is the standard MS query for ease of reference : ================================= Select TOP(convert(bigint, @NumberOfRows)) v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName as [Product Name], v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher as [Publisher], v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion as [Version], v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName as [Family Name], v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName as [Category Name], count(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID) as [Instance Count], v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as [Software ID], v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwarePropertiesHash0 as [Software Properties Hash], @CollectionID as [Collection ID] from v_GS_INSTALLED_SOFTWARE_CATEGORIZED where (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = @Publisher or @Publisher = '') and v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID IN (select distinct v_FullCollectionMembership.ResourceID from v_FullCollectionMembership inner join v_R_System_Valid ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID where CollectionID= @CollectionID ) group by v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwarePropertiesHash0 order by [Instance Count] desc, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName asc, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName asc The above report runs fast (< 30 seconds). I want the output to include CollectionID. Since it is not possible to output anything from a sub query, my obvious approach is to use a JOIN instead of the sub query. But for some reason, it takes forever to execute - often times out. I simplified it down to the following : //15 seconds select resourceid from v_GS_INSTALLED_SOFTWARE_CATEGORIZED t1 where resourceid IN (select resourceid from v_FullCollectionMembership t2 where t2.collectionid = 'CP00001D') //300 seconds select t1.resourceid from v_GS_INSTALLED_SOFTWARE_CATEGORIZED t1, v_FullCollectionMembership t2 where t2.collectionid = 'CP00001D' AND t1.resourceid = t2.resourceid My question is : Why does the INNER JOIN perform 20 times slower than a sub query? As per theory, the former should be faster. I am just an SCCM operator and a newbie trying to automate a report, hence I have no access to the SQL database to check the SQL analyser etc. or other information like indexes etc. v_GS_INSTALLED_SOFTWARE_CATEGORIZED contains about 100,000 rows v_FullCollectionMembership contains about 600,000 rows Is it possible for you to figure out from the above information where the likely problem could be? Thanks.