Fixing PlanAffectingConvertWarning: ConvertIssue= SeekPlan, Expression= CONVERT_IMPLICIT(nvarchar(50),[a].[Name],0)=[b].[Name] With Entity Framework On Windows Azure SQL Database

February 13, 2013 — Leave a comment

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.

2013-02-11_17h22_09

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)

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s