0

I have an Entity Framework application that is connecting to a non-unicode database. If I leave all my mapped string columns as the default type, Entity Framework sends query parameters as unicode values as shown below, and my performance seems to suffer.

exec sp_executesql N'SELECT [s].[Something]
FROM [MyTable] AS [s]
WHERE [s].[id] = @__id_0',N'@__id_0 nvarchar(450)',@__id_0=N'123456'

If I change my mapping to specify a VARCHAR column type, the query performance improves.

modelBuilder.Entity<MyObject>().Property(p => p.Id).HasColumnName("id").HasColumnType("VARCHAR(10)");

I'd rather not have to specify the column lengths of each field individually. Is there a problem with specifying all my column mappings as VARCHAR(MAX) (not the actual columns; those will still have a defined length), assuming that I either never write to that table or I handle truncating the string to the appropriate length within my application?

Ben Rubin
  • 6,909
  • 7
  • 35
  • 82
  • 1
    Question has been answered here: https://stackoverflow.com/questions/8512164/is-varcharmax-always-preferable – Paul Sinnema Mar 11 '22 at 20:16
  • Thank you for the link. My question is regarding the Entity Framework mapping, rather than the actual column definition. Does using VARCHAR(MAX) when mapping the column in Entity Framework cause the query to perform worse or have unintended consequences over using VARCHAR(N)? – Ben Rubin Mar 11 '22 at 20:21
  • 1
    EF translates everything to a parameterized query statement that the RDBMS executes. What type of column you choose and the performance thereof will be observed at the RDBMS level, *not* at the ORM level. See the suggested duplicate in the first comment, this is what you need to be concerned with. – Igor Mar 11 '22 at 20:45
  • @Igor Thank you. So it seems like I'd be better off declaring all the columns as something like VARCHAR(450) instead of VARCHAR(MAX), right? – Ben Rubin Mar 11 '22 at 20:54
  • 1
    Yes, if you read the answer in the link I gave you, you should come to the same conclusion. The varchar(MAX) does have a purpose though. When it is not possible to know the length of the string you are storing varchar(MAX) is your savier. – Paul Sinnema Mar 11 '22 at 21:06
  • 1
    If you know that the value of the string can exceed 8000 length then use varchar(max) and if it exceeds 4000 and is unicode then use nvarchar(max). Otherwise use the highest possible estimated/known length of the values which will be more performant than (max). – Igor Mar 11 '22 at 21:14

0 Answers0