Improve Sitecore Membership provider performance 2-20 times

The Sitecore Membership provider is built on top of the default .NET membership provider. The .NET membership provider is not known to be the fastest provider available. Sure, if you have a few thousand users in your database it performs well, but if you have, say, 50.000 or even 500.000 users, the provider becomes painfully slow.

500.000 users in Sitecore

500.000 users in Sitecore

But before you start rewriting the whole thing, there is a couple of things you can do to dramatically improve performance.

I would like to thank Ulrich Kronvold, Kristian Magius, Sergey Marchenko, Ivan Sheyenko and everyone that have been involved in getting these SQL scripts. None of the scripts are my own, but I think they are too good to keep for myself.

MODIFY STORED PROCEDURE [dbo].[aspnet_Membership_GetAllUsers] 

This script modifies the [dbo].[aspnet_Membership_GetAllUsers] stored procedure to select values based on indexed fields:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

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

    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- 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
    /* DSZ
    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_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName
    */

    SELECT @TotalRecords = COUNT(u.UserId)
    FROM dbo.aspnet_Membership m (NOLOCK), dbo.aspnet_Users u (NOLOCK)
    WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId

    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
                (SELECT
                        ROW_NUMBER() OVER (ORDER BY u.UserName) AS IndexId,
                        u.UserName
                FROM dbo.aspnet_Membership m (NOLOCK), dbo.aspnet_Users u (NOLOCK)
                        WHERE u.UserId = m.UserId AND u.ApplicationId = @ApplicationId AND m.ApplicationId = @ApplicationId)
                AS p INNER JOIN dbo.aspnet_Users u ON u.UserName = p.UserName INNER JOIN dbo.aspnet_Membership m ON m.UserId = u.UserId
        WHERE
                IndexId >= @PageLowerBound AND IndexId <= @PageUpperBound
    --ORDER BY UserName
    RETURN @TotalRecords
END

MODIFY STORED PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]

This script modifies the stored procedure [dbo].[aspnet_Membership_GetUserByEmail] to select fields based on indexed values:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByEmail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

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

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

CREATE NEW INDEX [aspnet_Membership_index]

This script creates a clustered index on fields ApplicationID, LoweredEmail and UserID used by the 2 stored procedures above:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership]') AND name = N'aspnet_Membership_index')
DROP INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership] WITH ( ONLINE = OFF )
GO

CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership] 
(
	[ApplicationId] ASC,
	[LoweredEmail] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NEW INDEX [aspnet_Users_Index]

This script creates a new clustered index on fields ApplicationID, LoweredUserName and UserID used by the 2 stored procedures above:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users]') AND name = N'aspnet_Users_Index')
DROP INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] WITH ( ONLINE = OFF )
GO

CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] 
(
	[ApplicationId] ASC,
	[LoweredUserName] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

With the changes above I managed to get at least twice the performance. Before the changes, the user manager took about 20 seconds to go to next page. After the change, it took only 2 seconds AND I could start using the search field again.

In general the performance gain was 2-5 times. The scripts does not seem to impact the stability of Sitecore, but use them with caution, as they are not official, not supported by me or Sitecore.

EDIT:

These scripts failed in an installation where I used the “switching” provider as my membership provider. Once I reverted to the default “sitecore” provider, the scripts worked. So be careful if you are using a switching provider, and remember to backup the database, or at least the original indexes and stored procedures.

Advertisements

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in Sitecore, Sitecore 6, Sitecore 7 and tagged , , , . Bookmark the permalink.

6 Responses to Improve Sitecore Membership provider performance 2-20 times

  1. If you run SQL Performance Tuning wizard you will notice quite a few additional indexes can be created to aid performance (at the cost of disk space and additional maintenance). Use with caution since additional indexes will slow down writes, but this is generally not an issue with Sitecore (not for use on the Analytics DB of course).

  2. Michael West says:

    I’m using the Active Directory module with Sitecore. When I page through the users in the User Manager I receive an error: Invalid skip value in paging input

  3. Pingback: Measuring Sitecore performance with HighResTimer | Brian Pedersen's Sitecore and .NET Blog

  4. Pingback: Sitecore Virtual Users – authenticate users from external systems | Brian Pedersen's Sitecore and .NET Blog

  5. Jakub says:

    Hi Brian,
    I know it was a while ago, but I’m just wondering, what’s your experience with the amount of users you can comfortably manage with ASP Membership and tweak you described? I’m facing a similar challenge now and considering either writing my own authentication mechanism or trying to optimise membership DB.

  6. briancaos says:

    At the moment I have 800.000 users in my system. According to my Google searches, 750.000 users is the absolute max you should have (some even say that anything above 30.000 users is not advisable).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s