Geek Answers Handbook

System.Web.Providers.DefaultMembershipProvider having performance problems / locks

We started using the updated System.Web.Providers provided in the Microsoft.AspNet.Providers.Core package from NuGet. We began to migrate our existing users and discovered a performance slowdown, and then deadlocks occurred. This was with less than 30,000 users (much less than 1,000,000 + we need to create). When we called the provider, it was from several flows on each server, and there were several servers performing the same process. This was to make it possible to create as quickly as possible all the users that we require and simulate the load that we expect to see when it lives.

The SQL Server logs generated for the stub contained the generated EF 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 executed the query manually, and the execution plan said that it scans the table, even though there is a base index. The reason for this is the use of LOWER([Extent1].[UserName]) .

We reviewed the provider code to make sure that we are doing something wrong or if there is a way to intercept or replace the database access code. We did not see any options for this, but we found the source of the LOWER problem, .ToLower() is called for both the column and the 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 how we change the behavior of the provider to not use .ToLower () to allow the use of the index?

+4
source share
2 answers

You can create an index below (username) on Sql Server: bottom function in indexed column

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

I also used the membership provider System.Web.Providers.DefaultMembershipProvider, but found that it was very slow. I switched to System.Web.Security.SqlMembershipProvider and found that it is much faster (5 times faster).

This tutorial shows how to set up an SQL database in which you need to use SqlMembershipProvider http://weblogs.asp.net/sukumarraju/archive/2009/10/02/installing-asp-net-membership-services-database- in-sql-server-expreess.aspx

This auto-generated database uses stored procedures, which may or may not be a problem for your database guys.

0
source

More articles:


All Articles