Improve SimpleMembership query performance?

Question

I have a sample database with 8 million users where the manage account page takes 8 seconds to render. It boils down to the method GetUserId calling Membership GetUser.

The GetUser sql looks like this:

SELECT [UserId] FROM [Users] WHERE (UPPER([UserName]) = @0)

When I run the following questions in the query analyzer I get the following results

SELECT [UserId] FROM [Users] WHERE [UserName] = 'CARL'
-- This question takes 11 milliseconds on my dev machine

SELECT [UserId] FROM [Users] WHERE UPPER([UserName]) = 'CARL'
-- This question takes 3.5 seconds on my dev machine

The UserName column has the following index:

CREATE NONCLUSTERED INDEX IX_Users_UserName ON dbo.Users (UserName)

Can the sql query be changed? Can the query performance be improved in any other way?

Answer 1

Can the sql query be changed?

No, the SQL query is burnt into the code of the simple membership provider. Checkout with reflector the code of the WebMatrix.WebData.SimpleMembershipProvider.GetUserId method which looks like this:

internal static int GetUserId(IDatabase db, string userTableName, string userNameColumn, string userIdColumn, string userName)
{
    object obj2 = db.QueryValue("SELECT " + userIdColumn + " FROM " + userTableName + " WHERE (UPPER(" + userNameColumn + ") = @0)", new object[] { userName.ToUpperInvariant() });
    if (<GetUserId>o__SiteContainer5.<>p__Site6 == null)
    {
        <GetUserId>o__SiteContainer5.<>p__Site6 = CallSite<Func<CallSite, object, bool>>.Create(Binder.UnaryOperation(CSharpBinderFlags.None, ExpressionType.IsTrue, typeof(SimpleMembershipProvider), new CSharpArgumentInfo[] { CSharpArgumentInfo.Create(CSharpArgumentInfoFlags.None, null) }));
    }
    if (<GetUserId>o__SiteContainer5.<>p__Site7 == null)
    {
        <GetUserId>o__SiteContainer5.<>p__Site7 = CallSite<Func<CallSite, object, object, object>>.Create(Binder.BinaryOperation(CSharpBinderFlags.None, ExpressionType.NotEqual, typeof(SimpleMembershipProvider), new CSharpArgumentInfo[] { CSharpArgumentInfo.Create(CSharpArgumentInfoFlags.None, null), CSharpArgumentInfo.Create(CSharpArgumentInfoFlags.Constant, null) }));
    }
    if (!<GetUserId>o__SiteContainer5.<>p__Site6.Target(<GetUserId>o__SiteContainer5.<>p__Site6, <GetUserId>o__SiteContainer5.<>p__Site7.Target(<GetUserId>o__SiteContainer5.<>p__Site7, obj2, null)))
    {
        return -1;
    }
    if (<GetUserId>o__SiteContainer5.<>p__Site8 == null)
    {
        <GetUserId>o__SiteContainer5.<>p__Site8 = CallSite<Func<CallSite, object, int>>.Create(Binder.Convert(CSharpBinderFlags.ConvertExplicit, typeof(int), typeof(SimpleMembershipProvider)));
    }
    return <GetUserId>o__SiteContainer5.<>p__Site8.Target(<GetUserId>o__SiteContainer5.<>p__Site8, obj2);
}

You will have to write a custom membership provider if you want to change this behavior.