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
Rob Kraft's Software Development Blog 