When observing Query Plans through the Windows Azure Management Portal for SQL Database you may come across warnings. Some warning are about missing Indexes and some are about performance issues related to the Query itself.
This specific warning occurs when you are comparing two strings that are not of the same Type. By this I mean one is NVARCHAR and the second is VARCHAR.
Addressing these warnings will have a great impact on query performance with large datasets. It may not be noticeable on tables that have a small amount of records. Once you start querying over a few million records, performance gains will be noticeable by the end users.
Fixing these issues can go a long way when it comes to end user satisfaction! Remember, slow applications are applications to which people find alternatives!
The following is specific for Entity Framework Code First
If this issue occurs on queries generated Entity Framework Code First, it means that you have a problem with the mapping configurations between your entities and the Database. In most cases this will occur when you map to an existing database without paying too much attention to string Database Types. Using tools to generate your DbContext may also result in this kind of error.
To resolve this issue, navigate to the EntityTypeConfiguration of the entities causing the problems and compare the configurations for the specific field with the field from Database table definition.
Database Table Definition
CREATE TABLE [dbo].[Users] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(50) NOT NULL, [Country] VARCHAR(50) NOT NULL )
Entity And Its Configuration
public class User { public int Id { get; set; } public string Name { get; set; } public string Country { get; set; } }
Possible errors can be spotted by looking for the IsUnicode configuration on your string properties. When this configuration is not present, the property is configured as NVARCHAR by convention.
public class UserMap : EntityTypeConfiguration<User> { public UserMap() { // Primary Key HasKey(t => new { t.Id }); Property(t => t.Name) .IsRequired(); // Table & Column Mappings ToTable("Users"); Property(t => t.Id) .HasColumnName("Id"); Property(t => t.Name) .HasColumnName("Name") .IsRequired(); Property(t => t.Country) .HasColumnName("Country") .IsRequired(); } }
Looking at the previous code everything seems to be in order. Taking a step back and comparing the Database table definition with this configuration we notice that both Name and Country are by default configured as NVARCHAR. Modify the Property method chain to override default configurations applied to your entities by convention. Setting IsUnicode to False will ensure that Entity Framework Code First will not try to convert strings to or from NVARCHAR when making comparisons.
public class UserMap : EntityTypeConfiguration<User> { public UserMap() { // Primary Key HasKey(t => new { t.Id }); Property(t => t.Name) .IsRequired(); // Table & Column Mappings ToTable("Users"); Property(t => t.Id) .HasColumnName("Id"); Property(t => t.Name) .HasColumnName("Name") .IsRequired(); Property(t => t.Country) .HasColumnName("Country") .IsRequired() .IsUnicode(false); } }
This revised version of the EntityTypeConfiguration correctly configured Country as a VARCHAR.
Entity Framework Code First and SQL Database will not try to covert your values to NVARCHAR when querying the Users table by filtering with a string on Country.
Plan Affecting Convert Warnings will also occur when a JOIN is being made on string columns that are not of the same Type.
CREATE TABLE [dbo].[Users] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(50) NOT NULL, [Country] VARCHAR(50) NOT NULL ) CREATE TABLE [dbo].[Stores] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(50) NOT NULL, [Country] NVARCHAR(50) NOT NULL )
JOIN Example
SELECT * FROM Users as u JOIN Stores as s ON u.Country = s.Country
To resolve this issue, make sure that both JOIN columns are of the same Type. Either make Country in Stores VARCHAR(50) or make Country in Users NVARCHAR(50)
Alexandre Brisebois ☁ 