Ok, I can probably do that, but I, too, have been waiting for the new module..  :)

If there is any clue past this particular sproc on the performance issues, the help is appreciated.  I'm even willing to give you host access to the site if you'd be willing to take a peek (not that it'd help, really).  My client keeps harping on the performance, my host keeps telling me it's DNN and the Repository, and I'm just confused because everything seems to be working properly.  Oh, well..  :)

Steve,

I have re-written one of the procedures, but it hasn't made it any faster.  I am going to run some index analysis once I get a chance.  Here it is with no warranty whatsoever!

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}{objectQualifier}grmGetRepositoryObjects') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}grmGetRepositoryObjects
GO

CREATE procedure {databaseOwner}{objectQualifier}grmGetRepositoryObjects
(
@ModuleID 	int,
@sFilter	nvarchar(256),
@sSort		nvarchar(100),
@Approved	int,
@CategoryId	int,
@Attributes	nvarchar(150),
@RowCount	int
)
AS

BEGIN
	set nocount on

	create table #keywords (keyword varchar(256))
	insert into #keywords
	select [value] as keyword from {databaseOwner}{objectQualifier}grmParseDelimitedStrings(@sFilter, '|')

	create table #objects (ItemId int)

	IF @CategoryId = -1
	BEGIN
		-- Filter by attributes only
		insert into #objects
		select a.ItemId
		FROM {databaseOwner}{objectQualifier}grmRepositoryObjects a
		WHERE a.ModuleID = @ModuleID
		AND a.Approved = @Approved
		AND (@Attributes = '' OR {databaseOwner}{objectQualifier}grmCheckAllAttributes(@Attributes,';', a.ItemID) = 1)
	END
	ELSE
	BEGIN
		-- Filter by category and attributes
		insert into #objects
		select a.ItemId
		FROM {databaseOwner}{objectQualifier}grmRepositoryObjects a
		INNER JOIN {databaseOwner}{objectQualifier}grmRepositoryObjectCategories b ON a.ItemID = b.ObjectID AND b.CategoryID = @CategoryId
		WHERE a.ModuleID = @ModuleID AND b.CategoryID = @CategoryId
		AND a.Approved = @Approved
		AND (@Attributes = '' OR {databaseOwner}{objectQualifier}grmCheckAllAttributes(@Attributes,';', a.ItemID) = 1)
	END

	IF @sFilter <> ''
	BEGIN
		-- Keyword search
		create table #objectsFiltered (ItemId Int)

		-- By Description
		insert into #objectsFiltered
		select distinct a.ItemId
		FROM #keywords k
		INNER JOIN {databaseOwner}{objectQualifier}grmRepositoryObjects a on a.Description like '%' + k.keyword + '%'
		INNER JOIN #objects o on a.ItemId = o.ItemId

		-- By Summary
		insert into #objectsFiltered
		select distinct a.ItemId
		FROM #keywords k
		INNER JOIN {databaseOwner}{objectQualifier}grmRepositoryObjects a on a.Summary like '%' + k.keyword + '%'
		INNER JOIN #objects o on a.ItemId = o.ItemId

		-- By Name
		insert into #objectsFiltered
		select distinct a.ItemId
		FROM #keywords k
		INNER JOIN {databaseOwner}{objectQualifier}grmRepositoryObjects a on a.name like '%' + k.keyword + '%'
		INNER JOIN #objects o on a.ItemId = o.ItemId

		-- By FileName
		insert into #objectsFiltered
		select distinct a.ItemId
		FROM #keywords k
		INNER JOIN {databaseOwner}{objectQualifier}grmRepositoryObjects a on a.FileName like '%' + k.keyword + '%'
		INNER JOIN #objects o on a.ItemId = o.ItemId

		-- By Author	
		insert into #objectsFiltered
		select distinct a.ItemId
		FROM #keywords k
		INNER JOIN {databaseOwner}{objectQualifier}grmRepositoryObjects a on a.Author like '%' + k.keyword + '%'
		INNER JOIN #objects o on a.ItemId = o.ItemId

		-- By Author Email
		insert into #objectsFiltered
		select distinct a.ItemId
		FROM #keywords k
		INNER JOIN {databaseOwner}{objectQualifier}grmRepositoryObjects a on a.AuthorEmail like '%' + k.keyword + '%'
		INNER JOIN #objects o on a.ItemId = o.ItemId

		-- Results
		IF @RowCount > 0
			SET ROWCOUNT @RowCount

		SELECT a.*, 
		(SELECT COUNT({databaseOwner}{objectQualifier}grmRepositoryComments.ItemID) FROM {databaseOwner}{objectQualifier}grmRepositoryComments WHERE {databaseOwner}{objectQualifier}grmRepositoryComments.ObjectID = a.ItemID) AS CommentCount
		from (select distinct ItemId from #objectsFiltered) o
		inner join {databaseOwner}{objectQualifier}grmRepositoryObjects a on o.ItemId = a.ItemId
		order by 
			case when @sSort = 'Name' then a.Name end asc,
			case when @sSort = 'Author' then a.Author end asc ,
			case when @sSort = 'Downloads' then a.Downloads end desc,
			case when @sSort = 'RatingAverage' then a.RatingAverage end desc,
			case when @sSort = 'UpdatedDate' then a.UpdatedDate end desc,
			case when @sSort = 'CreatedDate' then a.CreatedDate end desc
		
		drop table #objectsFiltered
	END
	ELSE
	BEGIN
		-- Results
		IF @RowCount > 0
			SET ROWCOUNT @RowCount

		SELECT a.*, 
		(SELECT COUNT({databaseOwner}{objectQualifier}grmRepositoryComments.ItemID) FROM {databaseOwner}{objectQualifier}grmRepositoryComments WHERE {databaseOwner}{objectQualifier}grmRepositoryComments.ObjectID = a.ItemID) AS CommentCount
		from #objects o
		inner join {databaseOwner}{objectQualifier}grmRepositoryObjects a on o.ItemId = a.ItemId
		order by 
			case when @sSort = 'Name' then a.Name end asc,
			case when @sSort = 'Author' then a.Author end asc ,
			case when @sSort = 'Downloads' then a.Downloads end desc,
			case when @sSort = 'RatingAverage' then a.RatingAverage end desc,
			case when @sSort = 'UpdatedDate' then a.UpdatedDate end desc,
			case when @sSort = 'CreatedDate' then a.CreatedDate end desc
	END

	set rowcount 0
	drop table #keywords
	drop table #objects
	set nocount off
END
GO