System.Web.Providers.DefaultMembershipProvider having performance issues/deadlocks

Question

We have started to use the updated System.Web.Providers provided in the Microsoft.AspNet.Providers.Core package from NuGet. We started to migrate our existing users and found performance slowing and then deadlocks occurring. This was with less than 30,000 users (much less than the 1,000,000+ we need to create). When we were calling the provider, it was from multiple threads on each server and there were multiple servers running this same process. This was to be able to create all the users we required as quickly as possible and to simulate the load we expect to see when it goes live.

The logs SQL Server generated for for a deadlock contained the EF generated sql below:

SELECT
    [Limit1].[UserId] AS [UserId]
  , [Limit1].[ApplicationId] AS [ApplicationId]
  , [Limit1].[UserName] AS [UserName]
  , [Limit1].[IsAnonymous] AS [IsAnonymous]
  , [Limit1].[LastActivityDate] AS [LastActivityDate]
FROM
    (SELECT TOP (1)
        [Extent1].[UserId] AS [UserId]
      , [Extent1].[ApplicationId] AS [ApplicationId]
      , [Extent1].[UserName] AS [UserName]
      , [Extent1].[IsAnonymous] AS [IsAnonymous]
      , [Extent1].[LastActivityDate] AS [LastActivityDate]
     FROM
        [dbo].[Users] AS [Extent1]
        INNER JOIN [dbo].[Applications] AS [Extent2] ON [Extent1].[ApplicationId] = [Extent2].[ApplicationId]
     WHERE
        ((LOWER([Extent2].[ApplicationName])) = (LOWER(@p__linq__0)))
        AND ((LOWER([Extent1].[UserName])) = (LOWER(@p__linq__1)))
    ) AS [Limit1]

We ran the query manually and the execution plan said that it was performing a table scan even though there was an underlying index. The reason for this is the use of LOWER([Extent1].[UserName]).

We looked at the provider code to see if we were doing something wrong or if there was a way to either intercept or replace the database access code. We didn't see any options to do this but we did find the source of the LOWER issue, .ToLower() is being called on both the column and parameter.

return (from u in ctx.Users
    join a in ctx.Applications on u.ApplicationId equals a.ApplicationId into a
    where (a.ApplicationName.ToLower() == applicationName.ToLower()) && (u.UserName.ToLower() == userName.ToLower())
    select u).FirstOrDefault<User>();

Does anyone know of a way that we change the behaviour of the provider to not use .ToLower() so allowing the index to be used?

Answer 1

You can create an index on lower(username) per Sql Server : Lower function on Indexed Column

ALTER TABLE dbo.users ADD LowerFieldName AS LOWER(username) PERSISTED
CREATE NONCLUSTERED INDEX IX_users_LowerFieldName_  ON dbo.users(LowerFieldName)