1

I am using ASP.NET MVC 5 Identity. I have allowed EF code first to create the database tables for me. PasswordHash, SecurityStamp and PhoneNumber are all nvarchar(MAX) - yet when I look at their content they are far shorter.

For my main DAL I am using Dapper - though I am letting EF handle Identity.

For database performance reasons am I able to shorten these MAX fields whilst guaranteeing Identity still functions correctly - and if so to what length?

(I assume I can do whatever I like with PhoneNumber as I control the contents of that field - but I am not so sure about the other 2).

I can't find anything mentioning this online.

niico
  • 11,206
  • 23
  • 78
  • 161
  • 3
    Performance reasons? Do you really think it will help? Obligatory link for any perf question: https://ericlippert.com/2012/12/17/performance-rant/ – DavidG Feb 12 '17 at 18:16
  • It's just not good practice to have lots of MAX fields is it - and there really seems like no good reason here. But hey when you're using EF you're hosed anyway ;) perf isn't just "can the server take it" - it's "can this be more responsive", better perf is a win in so many ways from scaling to response time to disk usage. – niico Feb 12 '17 at 23:34
  • You're right, but you're unlikely to have millions of users so it's not really n issue. – DavidG Feb 12 '17 at 23:35
  • Also perhaps I should have phrased this more about good practice than performance specifically sure; I take the point I think you're making. – niico Feb 12 '17 at 23:41

2 Answers2

0

I'm not really sure how much it will help you with performance, but you can configure your Identity properties using the fluent API:

public class YourContext : IdentityDbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //Make you you do this BEFORE the code below
        base.OnModelCreating(modelBuilder);

        //Assuming your user class is called "User" here:
        modelBuilder.Entity<User>()
            .Property(e => e.PasswordHash).HasMaxLength(100);

    }
}

Be aware though, if you make those fields incompatible with the values that the Identity framework wants to insert, then you will run into issues. So I would recommend quite a large value for the max length.

DavidG
  • 113,891
  • 12
  • 217
  • 223
  • Thx. So there's no documentation in how long those fields need to be? – niico Feb 12 '17 at 23:33
  • No idea how long they need to be, but this might give more info http://stackoverflow.com/questions/20621950/asp-net-identity-default-password-hasher-how-does-it-work-and-is-it-secure – DavidG Feb 12 '17 at 23:34
  • flipping this on its head - are there any benefits to doing this at all? As a rule I like to keep things as close to boilerplate as possible. If there is little or no benefit to changing these values, I'm thinking I will just leave them as-is. – niico Feb 13 '17 at 10:06
-1

The difference in SQL Server (I assume this is what you use) performance given nvarchar(x) vs nvarchar(Max) is when you do a search on these fields. I.e. select * from users where phoneNumber='123456789'.

But fields you mention are not subject to where clause anywhere in standard Identity template. Not even SecurityStamp and definitely not on PasswordHash. Users are searched by email, username, id. And these are limited in length for you already - 256 chars (see source, scroll to OnModelCreating). With an index applied on UserName column.

So whatever performance reasons you are talking about are non-existing.

If you do a lot of lookups by email, you can apply an index on Email column - that's as much as you can gain performance-wise. Same goes for phone number - if you do lookup users by phone numbers, it makes sense to make that column of a limited length and apply an index.

trailmax
  • 34,305
  • 22
  • 140
  • 234
  • Thanks - I should have phrased the question differently, not just about performance. So basically just sticking with all the default field lengths is unlikely to have real world downsides, right? There is little to no benefit in changing them (not only the MAX columns, but also, say, changing email length from nvarchar(256) to nvarchar(100)?) – niico Feb 13 '17 at 10:30
  • there is no good reason of doing just for the sake of doing it. Unless you are into business of saving bytes of storage: "Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation" from [documentation](https://msdn.microsoft.com/en-GB/library/ms186939.aspx). But somehow I feel you don't really care about 24 bytes. – trailmax Feb 13 '17 at 10:37
  • "...The perf hit is small, so it is not a huge deal in all but the largest applications, but if you know your data can fit in a varchar(n) field it is better to use a large n value than use max. Max is a safety net, but if you don't need it, don't put it up." < basically the designers of Identity were being lazy / keeping things flexible on the db side. These little perf hits do add up though: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4d9c6504-496e-45ba-a7a3-ed5bed731fcc/varcharmax-vs-varchar255 – niico Feb 13 '17 at 10:53
  • More information about significant performance differences - (MAX) is 50% slower in some cases (though yes under very high load) http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/ – niico Feb 13 '17 at 11:00
  • 1
    My thinking is now - if there's no good reason to use MAX don't use it. – niico Feb 13 '17 at 11:01
  • Have you read the article you link to? It talks about `Where` clause on the nvarchar(max) field - in that case yes, perf will go down through the floor and below - no doubt about that. Are you doing lookups by nvarchar(max) columns? – trailmax Feb 13 '17 at 11:03
  • another link about bad performance - that is highly relevant (comparing data that would fit in 256 but is in a 5000 column): http://dba.stackexchange.com/questions/162113/would-using-varchar5000-be-bad-compared-to-varchar255/162117#162117 – niico Feb 13 '17 at 11:12
  • I wasn't referring to that part of the article. See the other 2 articles also. – niico Feb 13 '17 at 11:13
  • Perf issues in the second link is exactly for the same reasons as in the first link. They do run `distinct` on nvarchar(max) field and that will make you crash and burn. – trailmax Feb 13 '17 at 11:16
  • there are also insert performance penalties. In my situation I see no reason to not shorten the lengths. Also my last 2 links - one certianly doesn't use distinct. I think avoiding MAX is a good idea if there's no reason to use it - that's my takeaway. – niico Feb 13 '17 at 11:18