Normalization on UserName and Email causes slow performance

[azuneca]

I have 5 million users and every time a new one registers. EF generates a heavy select query, which contains the following peace of code:

WHERE ((UPPER([Extent3].[UserName])) = (UPPER(@p__linq__0)))

The problem is that it has to calculate UPPER on all the 5 million rows and that slows down the performance a lot. It takes 3-4 seconds the query to complete and I am operating it on an Azure sql database with 200 DTUs.

Moreover, I checked out the source of the Identity system and to me it seems that the normalization is causing it. Can you suggest me any workaround?

[HaoK]

What version of identity are you using?

[divega]

@azuneca FWIW, we supposedly don’t do this anymore in recent builds of Identity 3.0 (i.e. the version we maintain in this repo). Instead we compute a normalized representation of the user name and we store it in a separate column so that lookups by normalized user name should now be sargable.

Also, for any previous version of Identity or Universal providers in which we produced queries similar to this the performance can be improved by manually adding an index over a computed column in SQL Server. Here are sample SQL commands that you would need to run on the database (table and column names may be different):

ALTER TABLE dbo.Users ADD NormalizedName  AS UPPER(Username); 
CREATE NONCLUSTERED INDEX [IX_NormalizedName] ON [dbo].[Users] ([NormalizedName] ASC);

Note that 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 UPPER(Username);
CREATE NONCLUSTERED INDEX [IX_NormalizedName] ON [dbo].[Users] ([Id] ASC) 
  INCLUDE ([NormalizedName]);

You can replace UPPER() with LOWER() if that is what the query contains.

HTH

[azuneca]

The version is 2.1.0

[azuneca]

I fixed it by doing the following things:

1. I added two computed columns to the users table NormalizedUsername and NormalizedEmail.
2. I set indexes on the columns...

Now everything, works just fine :)