I took a look through the code and stored procedures to see why performance is such an issue.
The primary issue is categories, the more categories you setup, the slower the stored procedures run
I have added some indexes that I think will improve the module's performance with a large number of categories
First it would help me if you could do some baseline timings so that we can see how effective the indexes are
1) back up your databases (especially if it is a production site)
2) log in using the Host account
3) go to the Host -> SQL Page
4) paste the sql script below into the text box
5) check the "Run as script" checkbox
6) Click Execute
Now go back and re-run your timings and compare them to the baseline
Let me know if you see an improvement, and if the performance is now acceptable
/* SQL SCRIPT STARTS HERE*/
/* grmRepositoryCategories */
if exists (select * from dbo.sysindexes
where name = 'IX_{objectQualifier}grmRepositoryCategories')
DROP INDEX {databaseOwner}[{objectQualifier}grmRepositoryCategories].[IX_{objectQualifier}grmRepositoryCategories]
CREATE INDEX [IX_{objectQualifier}grmRepositoryCategories]
ON {databaseOwner}[{objectQualifier}grmRepositoryCategories]([ModuleID])
/* grmRepositoryCategories_1 */
if exists (select * from dbo.sysindexes
where name = 'IX_{objectQualifier}grmRepositoryCategories_1')
DROP INDEX {databaseOwner}[{objectQualifier}grmRepositoryCategories].[IX_{objectQualifier}grmRepositoryCategories_1]
CREATE INDEX [IX_{objectQualifier}grmRepositoryCategories_1]
ON {databaseOwner}[{objectQualifier}grmRepositoryCategories]([Parent])
/* grmRepositoryObjectCategories */
if exists (select * from dbo.sysindexes
where name = 'IX_{objectQualifier}grmRepositoryObjectCategories')
DROP INDEX {databaseOwner}[{objectQualifier}grmRepositoryObjectCategories].[IX_{objectQualifier}grmRepositoryObjectCategories]
CREATE INDEX [IX_{objectQualifier}grmRepositoryObjectCategories]
ON {databaseOwner}[{objectQualifier}grmRepositoryObjectCategories]([CategoryID])
/* grmRepositoryCategories_1 */
if exists (select * from dbo.sysindexes
where name = 'IX_{objectQualifier}grmRepositoryObjectCategories_1')
DROP INDEX {databaseOwner}[{objectQualifier}grmRepositoryObjectCategories].[IX_{objectQualifier}grmRepositoryObjectCategories_1]
CREATE INDEX [IX_{objectQualifier}grmRepositoryObjectCategories_1]
ON {databaseOwner}[{objectQualifier}grmRepositoryObjectCategories]([ObjectID])
/* grmRepositoryObjects_1 */
if exists (select * from dbo.sysindexes
where name = 'IX_{objectQualifier}grmRepositoryObjects_1')
DROP INDEX {databaseOwner}[{objectQualifier}grmRepositoryObjects].[IX_{objectQualifier}grmRepositoryObjects_1]
CREATE INDEX [IX_{objectQualifier}grmRepositoryObjects_1]
ON {databaseOwner}[{objectQualifier}grmRepositoryObjects]([Approved])
/* END OF SCRIPT */