Hi Peter,

I’m using DNN 6.1 with DMX 5.3.8 and have some performance problems.
For example, I always have to wait about 10 seconds after each click on a treenode until new files and subfolders are loaded.

So I tried to find a solution for this and finally end up in database functions. I found some queries in the msssql-monitor that takes long time. This queries looks like ‘reload-functions’ for new Data (Entries, Subfolders).
All this queries with a significant long execution time have an inner join with the following subquery:

This single subquery needs 2,4s for execution on the server. This explains to me the 10s response time in AJAX-Gui for loading subfolders, entries and details.
How can I improve the performance? Can I rewrite the SQL or change some database parameters?

Thanks

We continued looking for a solution for our problem. And we found one.

We changed the PK_UserRoles-index in table 'UserRoles' from 'CLUSTERED' to 'NONCLUSTERED'. Additionaly, we created a clusterd-index one the columns '[UserID] and [RoleID]'.
As a result of this, the execution time decreased from 2400ms to 180ms.

What do you think about that?

Thanks for the feedback. I'll make a note of this and send it on to DNN Corp. UserRoles is not a DMX table but a DNN table.

My guess is that you have many users and roles. That will explode the view that is used. DMX needs to use that view to get a current snapshot of who is a user in a portal. And then it needs to bind this to the permissions for the items.

Peter

Added here: http://support.dotnetnuke...px?id=23650&PROJID=2

One of my friends from DNN suggested the following might improve results even more:

CREATE NONCLUSTERED INDEX MyAwesomeIndex
ON dbo.UserRoles (ExpiryDate, EffectiveDate)
INCLUDE (UserID, RoleID)

Peter

PS how many users and roles do you have?

I understand that this is an old discussion, but I thought I would add some more detail for those interested.

I didn't think we had a lot of roles or users, but we have ~30 roles and ~200 users and that was enough to slow DMX to a crawl when loading pages.

The afforementioned query with inner join statement took 22s on our sql server- which meant that the pages loaded in about 30 seconds.
As mentioned, I changed the PK to nonclustered and added a clustered index on userid and roleid on the DNN table UserRoles. Immediately the query dropped to ~1s.







IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserRoles]') AND name = N'PK_UserRoles')
ALTER TABLE [dbo].[UserRoles] DROP CONSTRAINT [PK_UserRoles]
GO

ALTER TABLE [dbo].[UserRoles] ADD CONSTRAINT [PK_UserRoles] PRIMARY KEY NONCLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX DMX_IDROLE
ON dbo.UserRoles (UserID, RoleID)

Thanks Drew!

Sorry to post regarding an older issue.

We are experiencing very similar issues with performance when expanding the folder structures, once expanded all appears ok, we use AD SYnc for our main DNN internal portal, would the mentioned fix making a changes to Clustered/non clustered indexes cause any known issues with AD auto logins?

Thanks.

Paul

AFAIK it has not been tried in that scenario. You'll just have to try. I can't see how it could "hurt". All that is done is that an index is added on the users and their roles in the portals. This table plays an important part in DMX's permissions code.

Peter