Professional Website Design, Development and Hosting for DNN / WordPress

Blog

SIG's Blog

rss

Technology, internet, rants and raves.


Pruning the DNN user tables

The odds are that if you've had user registration open for any great length of time that you'll have some users that haven't logged in for for years, or others who logged in once and never came back. A recent project to optimize an installation of DNN involved doing an inventory of the DNN user tables to see if there were opportunities for a performance improvement by winnowing out inactive or rogue user accounts from the installation. The site had collected 111,751 users in the five years of operation, not an unruly number at all but still worth investigating. Another factor at play was the sheer volume of traffic. On average, there were 1.5 million page views per month being generated by 275,000 users. After running various queries against the tables and reviewing the user-centric areas of the site, a surprising 72,963 users were identified as being candidates for removal.

Depending on the configuration of DNN - user uploads, extensive user profiles, community photo galleries, etc. - an individual user can potentially have a healthy footprint and leave behind their fair share of associated data and files. If not careful, an over aggressive campaign will potentially break existing features. And if not thorough enough, legacy files can remain on the site that will continue to drag on performance. So it becomes important to not only remove the user but also their associated files that might be resident on the server.

The easiest way to delete users beyond the occasional manual delete through the administration interface is to run an update query against the user tables and mark users for deletion. The most important aspect then is to accurately identify the users to delete.

To look for users that haven't logged in for a while you can run the following query. Change the date to suit your needs. Notice the last line that excludes users with an address from a particular domain.

SELECT
Users.userid, Users.username, Users.email, UserPortals.IsDeleted
FROM UserPortals
RIGHT OUTER JOIN Users on UserPortals.UserId = Users.UserId
INNER JOIN aspnet_Users ON Users.Username = aspnet_Users.UserName
INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId
WHERE aspnet_Membership.LastLoginDate < '6/1/2014'
and Users.email not like '%@mydomain.com'

Once you have the SELECT query getting you the proper results, wrap it in an UPDATE statement to mark the users for deletion. What I like about doing it this way is that there is zero confusion on who you're targeting. You can run your SELECT query all day long, then drop it into the UPDATE query in a snap.

UPDATE UserPortals
SET isDeleted = 1
WHERE UserID IN (
-- put your final SELECT query in here
)

The only change needed is to only retrieve Users.userid in the SELECT stattement by commenting out the rest of the select. So in this case, we execute the follwoing query that will soft delete the users.

UPDATE UserPortals 
SELECT
Users.userid --, Users.username, Users.email, UserPortals.IsDeleted FROM UserPortals
RIGHT OUTER JOIN Users on UserPortals.UserId = Users.UserId
INNER JOIN aspnet_Users ON Users.Username = aspnet_Users.UserName
INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId
WHERE aspnet_Membership.LastLoginDate < '6/1/2014'
and Users.email not like '%@mydomain.com'

You could also identify the users you want to protect, deleting those that fall outside of your collection. Start by identifying those you want to keep.

SELECT UserID FROM  Users 
WHERE isSuperuser = 1 OR UserName Like 'Admin' or CreatedOnDate < 2014-07-01

And as in the first example, wrap it in an UPDATE statement. But this time, change the logic to "NOT IN" like so.

UPDATE UserPortals
SET isDeleted = 1
WHERE UserID NOT IN (
SELECT UserID FROM Users
WHERE isSuperuser = 1 OR UserName Like 'Admin' or CreatedOnDate < 2014-07-01
)

And you can even tack on an exclusion for those users that are in a particular role.

UPDATE UserPortals
SET isDeleted = 1
WHERE UserID NOT IN (
SELECT UserID FROM Users
WHERE isSuperuser = 1 OR UserName Like 'Admin' or CreatedOnDate < 2014-07-01
)
AND UserID NOT IN (SELECT UserID FROM [vw_UserRoles]
WHERE RoleName Like 'verifiedUsers')

If you've come this far and executed an UPDATE query, you're almost there. You've "soft deleted" the users. That means that they are deactivated on the site yet still present in the database. The next step is to actually delete the users and their user files from the system. Thankfully, Bruce Chapman released a tool that does just that - DNN Bulk Uaser Delete. It searches for all soft deleted users and hard deletes them, along with removing their user directory and any user files from the server. You install a helper extension to your web site thriough the DNN Extensions installer, then execute a batch utility locally from your desktop. It will chug away without overloading the site and can process any number of records. And for those wondering, it takes care of the aspnet_Membership and related tables that also stores some of the user data.

Once it's done, uninstall the helper extension from your web site and resync your file system.

In the case of the site referenced in this post, we used this and a few other other optimizations to decrease the average page load time by a substantial 25% - a worthy improvement!




Comments are closed.
Showing 0 Comment


Terms Of Use  |  Privacy Statement
Copyright (C) 2017 Swanzey Internet Group LLC. All Rights Reserved.