Menu
In my application, I use ASP.NET Identity. Every thing is work fine but by testing, I found that the following command produces many database accesses: SignInManager.PasswordSignInAsync:
var result = await SignInManager.PasswordSignInAsync(user.UserName, model.Password, model.RememberMe, shouldLockout: true);
I notice this problem when testing the application using performance test. The test shows that the login request needs huge time to get response. The following figure shows result of performance test for the login request:
.
Then I use SQL Server Profiler to check what happen when the command SignInManager.PasswordSignInAsync is executed. It shows that a lot of database accesses is generated. The following is taken from SQL Server Profiler:
exec sp_executesql N'SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[UserFullName] AS [UserFullName],
[Extent1].[UserId] AS [UserId],
[Extent1].[Online] AS [Online],
[Extent1].[LastOnlineDate] AS [LastOnlineDate],
[Extent1].[BrithDate] AS [BrithDate],
[Extent1].[Job] AS [Job],
[Extent1].[Gender] AS [Gender],
[Extent1].[CountryId] AS [CountryId],
[Extent1].[LivesIn] AS [LivesIn],
[Extent1].[RelationId] AS [RelationId],
[Extent1].[Religion] AS [Religion],
[Extent1].[FirstSchool] AS [FirstSchool],
[Extent1].[SecondSchool] AS [SecondSchool],
[Extent1].[University] AS [University],
[Extent1].[ContactInfo] AS [ContactInfo],
[Extent1].[Email] AS [Email],
[Extent1].[EmailConfirmed] AS [EmailConfirmed],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[SecurityStamp] AS [SecurityStamp],
[Extent1].[PhoneNumber] AS [PhoneNumber],
[Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Extent1].[LockoutEnabled] AS [LockoutEnabled],
[Extent1].[AccessFailedCount] AS [AccessFailedCount],
[Extent1].[UserName] AS [UserName]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE ((UPPER([Extent1].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent1].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'[email protected]'
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[ClaimType] AS [ClaimType],
[Extent1].[ClaimValue] AS [ClaimValue]
FROM [dbo].[AspNetUserClaims] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[ClaimType] AS [ClaimType],
[Extent1].[ClaimValue] AS [ClaimValue]
FROM [dbo].[AspNetUserClaims] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[LoginProvider] AS [LoginProvider],
[Extent1].[ProviderKey] AS [ProviderKey],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[AspNetUserLogins] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[UserId] AS [UserId],
[Extent1].[RoleId] AS [RoleId]
FROM [dbo].[AspNetUserRoles] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[UserFullName] AS [UserFullName],
[Extent1].[UserId] AS [UserId],
[Extent1].[Online] AS [Online],
[Extent1].[LastOnlineDate] AS [LastOnlineDate],
[Extent1].[BrithDate] AS [BrithDate],
[Extent1].[Job] AS [Job],
[Extent1].[Gender] AS [Gender],
[Extent1].[CountryId] AS [CountryId],
[Extent1].[LivesIn] AS [LivesIn],
[Extent1].[RelationId] AS [RelationId],
[Extent1].[Religion] AS [Religion],
[Extent1].[FirstSchool] AS [FirstSchool],
[Extent1].[SecondSchool] AS [SecondSchool],
[Extent1].[University] AS [University],
[Extent1].[ContactInfo] AS [ContactInfo],
[Extent1].[Email] AS [Email],
[Extent1].[EmailConfirmed] AS [EmailConfirmed],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[SecurityStamp] AS [SecurityStamp],
[Extent1].[PhoneNumber] AS [PhoneNumber],
[Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Extent1].[LockoutEnabled] AS [LockoutEnabled],
[Extent1].[AccessFailedCount] AS [AccessFailedCount],
[Extent1].[UserName] AS [UserName]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[ClaimType] AS [ClaimType],
[Extent1].[ClaimValue] AS [ClaimValue]
FROM [dbo].[AspNetUserClaims] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[LoginProvider] AS [LoginProvider],
[Extent1].[ProviderKey] AS [ProviderKey],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[AspNetUserLogins] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[UserId] AS [UserId],
[Extent1].[RoleId] AS [RoleId]
FROM [dbo].[AspNetUserRoles] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[UserFullName] AS [UserFullName],
[Extent1].[UserId] AS [UserId],
[Extent1].[Online] AS [Online],
[Extent1].[LastOnlineDate] AS [LastOnlineDate],
[Extent1].[BrithDate] AS [BrithDate],
[Extent1].[Job] AS [Job],
[Extent1].[Gender] AS [Gender],
[Extent1].[CountryId] AS [CountryId],
[Extent1].[LivesIn] AS [LivesIn],
[Extent1].[RelationId] AS [RelationId],
[Extent1].[Religion] AS [Religion],
[Extent1].[FirstSchool] AS [FirstSchool],
[Extent1].[SecondSchool] AS [SecondSchool],
[Extent1].[University] AS [University],
[Extent1].[ContactInfo] AS [ContactInfo],
[Extent1].[Email] AS [Email],
[Extent1].[EmailConfirmed] AS [EmailConfirmed],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[SecurityStamp] AS [SecurityStamp],
[Extent1].[PhoneNumber] AS [PhoneNumber],
[Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Extent1].[LockoutEnabled] AS [LockoutEnabled],
[Extent1].[AccessFailedCount] AS [AccessFailedCount],
[Extent1].[UserName] AS [UserName]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[ClaimType] AS [ClaimType],
[Extent1].[ClaimValue] AS [ClaimValue]
FROM [dbo].[AspNetUserClaims] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[LoginProvider] AS [LoginProvider],
[Extent1].[ProviderKey] AS [ProviderKey],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[AspNetUserLogins] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[UserId] AS [UserId],
[Extent1].[RoleId] AS [RoleId]
FROM [dbo].[AspNetUserRoles] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[UserFullName] AS [UserFullName],
[Extent1].[UserId] AS [UserId],
[Extent1].[Online] AS [Online],
[Extent1].[LastOnlineDate] AS [LastOnlineDate],
[Extent1].[BrithDate] AS [BrithDate],
[Extent1].[Job] AS [Job],
[Extent1].[Gender] AS [Gender],
[Extent1].[CountryId] AS [CountryId],
[Extent1].[LivesIn] AS [LivesIn],
[Extent1].[RelationId] AS [RelationId],
[Extent1].[Religion] AS [Religion],
[Extent1].[FirstSchool] AS [FirstSchool],
[Extent1].[SecondSchool] AS [SecondSchool],
[Extent1].[University] AS [University],
[Extent1].[ContactInfo] AS [ContactInfo],
[Extent1].[Email] AS [Email],
[Extent1].[EmailConfirmed] AS [EmailConfirmed],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[SecurityStamp] AS [SecurityStamp],
[Extent1].[PhoneNumber] AS [PhoneNumber],
[Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Extent1].[LockoutEnabled] AS [LockoutEnabled],
[Extent1].[AccessFailedCount] AS [AccessFailedCount],
[Extent1].[UserName] AS [UserName]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[ClaimType] AS [ClaimType],
[Extent1].[ClaimValue] AS [ClaimValue]
FROM [dbo].[AspNetUserClaims] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
The above queries are just a part of the result. The same queries are executed several times. Is this the normal case or there is a problem ?
If it is a problem, how it can be solved.
393
I'm going to assume you're using the Identity setup 'as it comes'. In which case you will also probably be using Entity Framework.
To answer your question: In my experience, the UserStore that sits behind UserManager, SignInManager etc is accessed multiple times when an action is carried out.
The main problem I noticed was they this was incredibly slow with EntityFramework in place. I found that if I created my own CustomUserStore it dramatically sped up the login/user action speed. There are still multiple calls to the methods but performs far better.
In my case I used the Micro-ORM Dapper.NET to drive my new UserStore. Dapper can perform queries much faster than EntityFramework (3-10 times faster depending on the query in some tests - see: https://www.exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/)
Dapper.NET: https://github.com/StackExchange/Dapper
With the custom UserStore rewrite, you can find loads of examples online but here is one: https://markjohnson.io/articles/exorcising-entity-framework-from-asp-net-identity/
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
