Improve performance by optimizing queries for ASP.NET Identity and other Membership Providers

pranav rastogi

If your application is using ASP.NET Identity or other membership systems such as SQL, Universal or Simple membership, then you might be running into the following issue.

If you are using ASP.NET Identity 2.x, the query for getting profile values (among other things) uses the UPPER() function on Username – this causes any indexes on username to be ignored.

The workaround is to add a computed column with the call to UPPER(Username) and a database index over it. This is extremely effective in improving the performance and it does not require any changes to the application or updating ASP.NET Identity Framework.

Here are sample SQL commands that you would need to run on the database (table and column names may be different based on your app):

SQL Query for ASP.NET Identity 2.1

ALTER TABLE dbo.AspNetUsers ADD NormalizedName  AS UPPER(UserName);

CREATE NONCLUSTERED INDEX [IX_NormalizedName] ON [dbo].[AspNetUsers] ([NormalizedName] ASC);

Note If Username is of a type larger than the allowed index key length, e.g. nvarchar(max) you may need to do this instead:

ALTER TABLE dbo.AspNetUsers ADD NormalizedName  AS LOWER(UserName);

CREATE NONCLUSTERED INDEX [IX_NormalizedName] ON [dbo].[AspNetUsers] ([Id] ASC) INCLUDE ([NormalizedName]);

SQL Query for ASP.NET Universal Provider

ALTER TABLE dbo.Users ADD NormalizedName  AS LOWER(Username);

CREATE NONCLUSTERED INDEX [IX_NormalizedName] ON [dbo].[Users] ([NormalizedName] ASC);

Note If Username is of a type larger than the allowed index key length, e.g. nvarchar(max) you may need to do this instead:

ALTER TABLE dbo.Users ADD NormalizedName  AS LOWER(Username);

CREATE NONCLUSTERED INDEX [IX_NormalizedName] ON [dbo].[Users] ([Id] ASC) INCLUDE ([NormalizedName]);

Note:

Although we did it only for Username, the same principle should apply to other columns where you are doing lots of lookups on.

You can use LOWER() or UPPER() based on what queries are being executed.

You can follow the same steps for SQL Membership and Simple Membership as well.

Customers have seen significant improvements in performance with this workaround so we would recommend you to implement this in your application.

pranav rastogi

Follow