Corriger les conversions implicites

Le problème des conversions implicites

Les conversions implicites se produisent lorsque SQL Server doit convertir un type de données en un autre pour effectuer une opération.

Par exemple, si vous comparez une colonne de type VARCHAR avec une valeur de type NVARCHAR, SQL Server devra convertir la valeur de la colonne en NVARCHAR avant de pouvoir effectuer la comparaison.

Cela peut entraîner un parcours complet de l’index ou de la table (scan) au lieu d’une recherche rapide dans l’index (seek).

Voici un exemple de requête qui pose problème, sur la base de démonstration PachaDataFormation. Dans la table Contact.ProspectUS_SQL_Latin1, les colonnes sont en VARCHAR avec une collation SQL (SQL_Latin1_General_CP1_CI_AS). Créez d’abord un index sur la colonne Nom :

CREATE INDEX nix_prospectus_sql_latin1_nom
ON Contact.ProspectUS_SQL_Latin1 (Nom);
GO

DECLARE @Nom NVARCHAR(50) = N'Klein';

SELECT Prenom, Nom
FROM Contact.ProspectUS_SQL_Latin1
WHERE Nom = @Nom;

Nom est de type VARCHAR dans la base de données, mais le paramètre @Nom est de type NVARCHAR (ce qui est le cas par défaut avec ADO.NET quand le type du paramètre n’est pas précisé). SQL Server doit donc convertir la colonne Nom en NVARCHAR avant de pouvoir effectuer la comparaison. Cela empêche une recherche (seek) dans l’index sur la colonne Nom : SQL Server parcourt tout l’index. La conversion peut aussi, dans certains cas, fausser l’estimation du nombre de lignes retournées (l’estimation de cardinalité).

Pourquoi les conversions implicites peuvent convertir la colonne plutôt que le paramètre

Lorsque SQL Server doit comparer deux valeurs de types de données différents, il peut convertir implicitement l’une des valeurs pour que les types soient compatibles. La règle générale est que SQL Server choisit le type de données avec la priorité la plus élevée pour la conversion. Cela signifie que si une colonne est de type VARCHAR et qu’un paramètre est de type NVARCHAR, SQL Server va convertir la colonne en NVARCHAR plutôt que de convertir le paramètre en VARCHAR.

On peut comprendre cette règle de priorité ainsi :

1. Minimiser la perte de données : Convertir une valeur de type VARCHAR en NVARCHAR est moins susceptible de causer une perte de données que l’inverse. Par exemple, si vous convertissez une valeur NVARCHAR en VARCHAR, vous pourriez perdre des caractères si la valeur NVARCHAR contient des caractères qui ne peuvent pas être représentés en VARCHAR.

2. Cohérence et prévisibilité : En suivant une hiérarchie de priorité claire, SQL Server peut s’assurer que les conversions de types de données sont effectuées de manière cohérente et prévisible.

ADO.NET et NVARCHAR

Quand le type d’un paramètre n’est pas précisé, par exemple avec AddWithValue, ADO.NET déduit le type NVARCHAR d’une chaîne .NET. Cela peut poser problème si les colonnes de la base de données sont de type VARCHAR. En effet, SQL Server devra convertir les données de la colonne en NVARCHAR avant de pouvoir effectuer la comparaison, ce qui peut entraîner des problèmes de performance.

Par exemple, si vous avez une table avec une colonne Nom de type VARCHAR et que vous utilisez ADO.NET pour exécuter une requête avec un paramètre de type NVARCHAR, SQL Server devra convertir la colonne Nom en NVARCHAR avant de pouvoir effectuer la comparaison.

Identifier les conversions implicites

Pour identifier les conversions implicites dans vos requêtes, vous pouvez utiliser les plans d’exécution de SQL Server. Les plans d’exécution montrent comment SQL Server exécute une requête et peuvent inclure des informations sur les conversions implicites.

La capture a été prise sur une ancienne version de la base de démonstration, dans une table Contact.Contact_SQLCollation dont la colonne LastName est en collation SQL. L’exemple ci-dessus produit le même avertissement de seek.

Dans ce plan, vous voyez une icône d’avertissement sur l’opérateur SELECT qui résume la commande. Cet avertissement signale qu’une conversion implicite peut influencer le choix du plan. Toutes les conversions implicites n’en produisent pas : quand c’est le paramètre qui est converti vers le type de la colonne, le plan n’en montre aucun. En survolant cette icône, vous pouvez voir des détails sur la conversion implicite, y compris les types de données impliqués.

L’avertissement peut indiquer deux problèmes : un problème de performance dû à l’impossibilité d’utiliser un index (index seek), et un problème d’estimation de cardinalité (cardinality estimation). Il arrive que le problème d’estimation de cardinalité soit bénin (conversion implicite dans la partie SELECT), mais l’avertissement de seek est en lien avec une conversion implicite dans un prédicat, et c’est un signe qu’il faut optimiser la requête.

C’est le cas dans l’exemple ci-dessus, où la conversion implicite empêche la recherche (seek) dans l’index sur la colonne Nom.

Détecter les conversions implicites

Vous pouvez tracer ces warnings à l’aide d’une session d’évènements étendus (XEvents). Vous trouvez le script de création de session sur mon Github.

Vous pouvez aussi interroger le cache de plans pour trouver les plans avec des conversions implicites.

Corriger les conversions implicites

Pour corriger les conversions implicites, vous devez modifier les types de données dans vos requêtes pour qu’ils correspondent aux types de données dans la base de données. Par exemple, si la colonne Nom est de type VARCHAR, vous pouvez modifier votre requête pour utiliser un paramètre de type VARCHAR au lieu de NVARCHAR.

Voici un exemple de correction :

// Avant : utilisation de NVARCHAR par défaut
command.Parameters.AddWithValue("@Nom", Nom);

// Après : utilisation explicite de VARCHAR, avec la taille de la colonne
command.Parameters.Add("@Nom", SqlDbType.VarChar, 50).Value = Nom;

Précisez aussi la taille : sans elle, la longueur du paramètre est déduite de la valeur envoyée, et la même requête arrive avec des déclarations varchar(n) différentes selon la longueur du nom.

Entity Framework et conversions implicites

Par défaut, Entity Framework peut utiliser des types de données qui ne correspondent pas exactement aux types de données dans la base de données. Par exemple, si vous avez une colonne de type VARCHAR dans votre base de données, Entity Framework pourrait utiliser un type de données .NET qui est mappé à NVARCHAR dans SQL Server. Cela peut entraîner des conversions implicites lorsque les requêtes sont exécutées.

Dans les versions antérieures d’Entity Framework comme dans Entity Framework Core, les chaînes de caractères sont mappées par défaut à NVARCHAR. Entity Framework Core permet de préciser le type de chaque propriété, avec HasColumnType, avec IsUnicode(false) ou, depuis EF Core 6, avec l’attribut [Unicode(false)].

Voici un exemple de comment spécifier le type de données pour une propriété dans Entity Framework, pour la table Contact.Contact de PachaDataFormation :

public class Contact
{
    public int ContactId { get; set; }

    [Column(TypeName = "varchar(50)")]
    public string Nom { get; set; }
}

Dans cet exemple, la propriété Nom est mappée à une colonne de type VARCHAR(50) dans la base de données. Cela peut aider à éviter les conversions implicites lorsque des requêtes sont exécutées.

Vous pouvez également configurer les types de données dans la classe de configuration de l’entité. Par exemple, si vous utilisez Fluent API pour configurer votre modèle, vous pouvez spécifier le type de données comme suit :

modelBuilder.Entity<Contact>()
    .Property(c => c.Nom)
    .HasColumnType("varchar(50)");

Cela permet de s’assurer que la colonne Nom est mappée à un type VARCHAR dans la base de données, ce qui peut aider à éviter les conversions implicites.

Voici un exemple complet de configuration d’une entité avec Fluent API pour éviter les conversions implicites, dans une classe dédiée d’Entity Framework Core :

public class ContactConfiguration : IEntityTypeConfiguration<Contact>
{
    public void Configure(EntityTypeBuilder<Contact> builder)
    {
        builder.Property(c => c.Nom)
            .HasColumnType("varchar(50)")
            .IsRequired();
    }
}

La configuration s’applique dans OnModelCreating, avec modelBuilder.ApplyConfiguration(new ContactConfiguration());. Dans cet exemple, la propriété Nom est configurée pour être mappée à une colonne de type VARCHAR(50) dans la base de données.