Newbie : Inventory report taking very long to execute

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.



"Sherry Kissinger [MVP]" wrote:

> 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/jnels...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/...urationmanager
>
> 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" wrote:
>
> > 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.NormalizedPubl isher as [Publisher],
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVers ion 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.Resource ID) as [Instance Count],
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as [Software ID],
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareProper tiesHash0 as [Software
> > Properties Hash],
> > @CollectionID as [Collection ID]
> > from v_GS_INSTALLED_SOFTWARE_CATEGORIZED
> > where (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPub lisher = @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.NormalizedPubl isher,
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVers ion,
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName,
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName,
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID,
> > v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareProper tiesHash0
> > 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.

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.NormalizedPubl isher as [Publisher],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVers ion 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.Resource ID) as [Instance Count],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as [Software ID],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareProper tiesHash0 as [Software
Properties Hash],
@CollectionID as [Collection ID]
from v_GS_INSTALLED_SOFTWARE_CATEGORIZED
where (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPub lisher = @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.NormalizedPubl isher,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVers ion,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareProper tiesHash0
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.

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/jnels...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/...urationmanager

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" wrote:

> 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.NormalizedPubl isher as [Publisher],
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVers ion 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.Resource ID) as [Instance Count],
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as [Software ID],
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareProper tiesHash0 as [Software
> Properties Hash],
> @CollectionID as [Collection ID]
> from v_GS_INSTALLED_SOFTWARE_CATEGORIZED
> where (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPub lisher = @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.NormalizedPubl isher,
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVers ion,
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName,
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName,
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID,
> v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareProper tiesHash0
> 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.