In our recent endeavors, we have come across two specific
instances of DotNetNuke sites which for were exhibiting some very long delays
in opening some of the administrative tabs- with the largest delay occurring on
the Site Settings tab.
After looking in to the problem, and running some diagnostics on the site one
major problem was identified. Something in the history of the site, whether it
was an upgrade from an older version of DotNetNuke, or a third party module
installation caused a huge number of duplicate records to be generated within
the FolderPermission table.
The FolderPermission table is queried upon every request on the Site Settings
tab, to determine the Read and Write access for the folder for the roles
assigned to the currently logged in user. Typically only two records would be
returned for the Administrative user - because only 2 unique records should
have existed for that role on the folder that was requested.
One of the sites we were analyzing returned nearly 2,000 records for each request.
Since the site had nearly 15 skins, each skin folder was checked for
permissions and these requests were returning in two to three seconds instead
of a fraction of a second.
One look at the FolderPermission table said it all - the table had over 73,400
records within it, of which only 192 were truly unique. So the correction to
the issue was actually really simple and could be handled by executing a quick
SQL script with the Host SQL tab.
If you are having a similar issue - go ahead and try this script on for size.
Note, this script does not include the owner or qualifier of the database, so
if your database required either - modify the script to work with your specific
installation.
--* CREATE THE TEMPORARY TABLE
create table #FolderPermission
( FolderID int,
PermissionID int,
RoleID int,
AllowAccess bit )
--* POPULATE THE TEMPORARY TABLE WITH THE UNIQUE RECORDS
INSERT INTO #FolderPermission
SELECT DISTINCT
folderid,
permissionid,
roleid,
allowaccess
FROM FolderPemission
--* TRUNCATE THE OLD TABLE
TRUNCATE TABLE FolderPermission
--* REPOPULATE THE OLD TABLE
INSERT INTO FolderPermission
(
FolderID,
PermissionID,
RoleID,
AllowAccess
)
SELECT
FolderID,
PermissionID,
RoleID,
AllowAccess
FROM #FolderPermission
--** CLEAN UP
DROP TABLE #FolderPermission