Improving ASP.Net Framework Membership Performance

I’ll admit this post would have been more valuable fifteen years ago, but those still using the ASP.Net Membership provider tables and stored procs that came with .Net Framework 2.0, 3.0, or 4.8 my like the performance boost you can get from these changes. The Membership tables were designed with two major flaws, in hindsight. One, the primary keys are guids, which provide terrible performance, and two, they assumed most installations would have multiple applications using the same membership table, which I think they do not. But Microsoft made the application ID, a guid, part of every key in every query. If your application only has a single application ID, you can change the stored procs to not consider the application ID as part of the query, and change the indexes to remove application ID as well. The change scripts are provided below:

alter PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
    @ApplicationName  nvarchar(256),
    @Email            nvarchar(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  
               
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  
                
                u.UserId = m.UserId AND
                @Email = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END
GO

alter PROCEDURE dbo.aspnet_Membership_FindUsersByEmail  
    @ApplicationName       nvarchar(256),  
    @EmailToMatch          nvarchar(256),  
    @PageIndex             int,  
    @PageSize              int  
AS  
BEGIN  
 
    -- Set the page bounds  
    DECLARE @PageLowerBound int  
    DECLARE @PageUpperBound int  
    DECLARE @TotalRecords   int  
    SET @PageLowerBound = @PageSize * @PageIndex  
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound  
  
    -- Create a temp table TO store the select results  
    CREATE TABLE #PageIndexForUsers  
    (  
        IndexId int IDENTITY (0, 1) NOT NULL,  
        UserId uniqueidentifier  
    )  
  
    -- Insert into our temp table  
    IF( @EmailToMatch IS NULL )  
        INSERT INTO #PageIndexForUsers (UserId)  
            SELECT u.UserId  
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m  
            WHERE  m.UserId = u.UserId AND m.Email IS NULL  
            ORDER BY m.LoweredEmail  
    ELSE  
        INSERT INTO #PageIndexForUsers (UserId)  
            SELECT u.UserId  
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m  
            WHERE  m.UserId = u.UserId AND m.LoweredEmail LIKE @EmailToMatch 
            ORDER BY m.LoweredEmail  
  
    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,  
            m.CreateDate,  
            m.LastLoginDate,  
            u.LastActivityDate,  
            m.LastPasswordChangedDate,  
            u.UserId, m.IsLockedOut,  
            m.LastLockoutDate  
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p  
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND  
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound  
    ORDER BY m.LoweredEmail  
  
    SELECT  @TotalRecords = COUNT(*)  
    FROM    #PageIndexForUsers  
    RETURN @TotalRecords  
END  
go

ALTER PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
    @ApplicationName       nvarchar(256),
    @UserNameToMatch       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT u.UserId
        FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE m.UserId = u.UserId AND u.LoweredUserName LIKE @UserNameToMatch
        ORDER BY u.UserName


    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END
GO




ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN
        -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM     dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   
                @UserName = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1

        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId

        SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut, m.LastLockoutDate
        FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM     dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   
                @UserName = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    RETURN 0
END
GO


alter PROCEDURE [dbo].[aspnet_Roles_RoleExists]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE @RoleName = LoweredRoleName  ))
        RETURN(1)
    ELSE
        RETURN(0)
END
GO

drop index aspnet_Membership_index on [dbo].[aspnet_Membership]
go
CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership]
(
	[LoweredEmail] ASC
)WITH (FillFactor=95) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ApplicationId] ON [dbo].[aspnet_Membership]
(
	[ApplicationId] ASC
)WITH (FillFactor=95)

GO

drop index aspnet_Users_index on [dbo].[aspnet_Users]
go
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users]
(
	[LoweredUserName] ASC
)WITH (FillFactor=95)

GO
drop index aspnet_Users_index2 on [dbo].[aspnet_Users]
go
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users]
(
	[LastActivityDate] ASC
)WITH (FillFactor=95)

CREATE NONCLUSTERED INDEX [IX_AspNet_Users_ApplicationID] ON [dbo].[aspnet_Users]
(
	[ApplicationId] ASC
)WITH (FillFactor=95)

GO