Newbie : Inventory report taking very long to execute

[aruntechie123]

Hello,
(This is not being posted to SQL newsgroup perhaps because it requires a
knowledge of table design, 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 an INNER
JOIN instead of the sub query. But for some reason, it takes forever to
execute - often times out.

I simplified it 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 about information where the
problem could be? Thanks.

[Sherry Kissinger [MVP]]

As you guessed, it may be that you need to have an index created.

As an Configmgr operator, you may not have the rights to implement this.
Perhaps just suggest it to your ConfigMgr engineers to run sql profiler and
see if this applies.

http://myitforum.com/cs2/blogs/jnelson/archive/2009/12/09/143643.aspx

The blog entry was about slow DDR processing, but you'll notice that one
part of the solution was to index a specific table--the same one your view is
likely referencing for that report.

fyi, this newsgroup is pretty quiet. It's for SMS2003 mostly, and quite
honestly I don't think too many people check the newsgroup too much. For
Configmgr issues, you'd be better off either posting your question on the
Technet forum:

http://social.technet.microsoft.com/Forums/en-US/category/configurationmanager

or at the Myitforum ConfigMgr:

http://www.myitforum.com/forums/forumid_144/tt.htm

And if you'd like a different opinion, you might want to post your question
in one or both of those forums anyway.

[aruntechie123]

Thanks for helping.
If the indexing is the solution, then I will have to live with the issue. I
have posted the to the correct forum you advised anyway...just in case.