Le piège des conversions implicites

Les problèmes de conversion de types de données sont un des problèmes de performances trop souvent rencontré.

Vous avez peut-être déjà rencontré des plans d'exécution de requêtes qui, à cause d'une conversion implicite, deviennent inefficaces : parcours de table au lieu d'une recherche indexée, par exemple.

Nous allons voir ici dans quel cas de figure cela se produit.

Prenons un exemple avec une simple table avec quelques index, remplie de données :

CREATE TABLE [dbo].[Persons](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Matricule] VARCHAR(10) NOT NULL INDEX IxMatricule NONCLUSTERED,
    [Nom] [varchar](50) NULL INDEX IxNom NONCLUSTERED,
    [Prenom] [varchar](50) NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED ([Id] ASC))

Effectuons maintenant la requête suivante :

SELECT * FROM Persons WHERE Matricule = '123'

Ici, tout va bien, l'index sur le matricule a bien été utilisé. 6 lectures logiques.

Modifions donc cette requête :

SELECT * FROM Persons WHERE Matricule = 123

Patatras : l'index n'est plus utilisé et toute la table est scannée via son index cluster : plus de 10000 lectures ! Que s'est-il passé ?

  • Dans notre table, la colonne matricule est de type VARCHAR.
  • Dans notre prédicat de recherche, la valeur recherchée est de type INT

CQFD ! Nous avons demandé à SQL Server de faire une conversion implicite. Le petit icône d'avertissement nous prévient : "la conversion de type dans l'expression... peut affecter SeekPlan dans le choix du plan de requête"

De même que le prédicat de recherche du parcours d'index cluster :

Mais pourquoi SQL Server a-t-il choisi de convertir la colonne Matricule en INT, plutôt que le prédicat 123 en VARCHAR ?

Parce qu'il y a un sens, pour les conversions implicites !
En effet il y a un ordre de priorité dans les types de données : le type de données avec la priorité la plus faible est converti vers le type avec la priorité la plus forte.
VARCHAR a une priorité plus faible que INT, et donc c'est lui qui est converti : dommage, c'est la colonne qui sera convertie et non de la valeur recherchée, d'où l'impossibilité d'utiliser l'index...

Vous trouverez l'ordre de priorités documenté ici.

Notons que nous pourrions faire la même expérience avec les deux requêtes suivantes :

SELECT * FROM Persons WHERE Nom = 'Dupont'
SELECT * FROM Persons WHERE Nom = N'Dupont'

Le NVARCHAR ayant une priorité plus faible que le VARCHAR, on peut imaginer que nous aurons le même comportement, avec un scan coûteux pour la seconde requête.
Mais ici, la collation de la colonne (ou base) rentre en ligne de compte : avec une collation SQL nous constatons bien ce problème, mais il ne se produit pas avec une collation Windows.

Moralité : avis aux développeurs, évitez à tout prix les erreurs de type de données dans vos prédicats ! Cela reste la meilleure solution...