Optimisation de requêtes SQL : « SARGABLE » c’est quoi ?

4 réponses

Le terme anglais « SARGABLE » indique si une expression de filtrage (prédicat) peut utiliser ou non une recherche dans un index. Si c’est le cas, le prédicat est dit sargable, l’index est utilisé pour une recherche et le temps de réponse sera excellent. Sinon, le temps de réponse sera mauvais car il faudra balayer toutes les lignes de la table pour satisfaire la dempande… Mais quelles sont les expressions « sargable », peut t-on transformer une expression non sargable en expression sargable ? Voici quelques questions et les réponses associées, avec des exemples sous MS SQL Server…

Le terme « SARGABLE » vient de la contraction de l’expression anglaise « Search ARGument Able » c’est à dire « capable d’utiliser un argument de recherche ».

1 – RÈGLE GÉNÉRALE

Sont sargable :
les recherches qui portent sur le même sens de tri que celui effectué pour la structuration de l’index (alphabétique pour des chaînes de caractères, numérique pour des nombres, temporels pour des dates…).
Ne sont pas sargable :
Le NOT n’est en principe jamais sargable, tout comme le OR et le IN avec plusieurs valeurs.
De même une colonne à laquelle on applique une opération, tout comme une colonne « noyée » dans une fonction n’est en principe jamais sargable. À ce sujet, méfiez vous des transtypages implicites !

2 – DÉTAILS

Nous disposons d’une table avec les colonnes suivantes :

Colonne, colonne1, colonne2
et des index suivants :
X1 (colonne)
X2 (colonne1, colonne2)

Sont sargable :

Colonne = valeur
Colonne >=, , < valeur
Colonne = valeur1 OPERATEUR valeur2 -- ou opérateur est +, -, x, /, % (modulo)
Colonne BETWEEN valeur1 AND valeur2
Colonne LIKE 'valeur%'
Colonne1 = valeur1 AND Colonne2 = valeur2
Colonne IN (valeur1)
EXISTS(...)
Colonne = FONCTION(valeur, ...)

Ne sont pas sargable :

Colonne  valeur
Colonne OPERATEUR valeur2 = valeur1  
NOT Colonne BETWEEN valeur1 AND valeur2
NOT Colonne1 = valeur1 AND Colonne2 = valeur2
Colonne LIKE '%valeur'
Colonne LIKE '%valeur%'
Colonne LIKE 'val%eur'
NOT Colonne LIKE '...'
Colonne NOT LIKE '...'
Colonne IN (valeur1, valeur2)
Colonne NOT IN (...)
NOT Colonne IN (...)
NOT EXISTS(...)
FONCTION(Colonne, ...) = valeur

NOTA : bien que certains prédicats de cette liste soient présentés comme non sargable, certains optimiseurs arrivent à récrire la requête pour rendre le prédicat sargable (algébrisation).

Non sargable, rendu sargable :



Non sargable                             | récriture sargable 

=========================================|====================================== 

Colonne OPERATEUR valeur2 = valeur1      | Colonne = valeur1 OPERATEUR valeur2 

-----------------------------------------|-------------------------------------- 

Colonne1 = valeur1 OR Colonne2 = valeur2 | Colonne1 = valeur1 

                                         | UNION [ALL] 

                                         | Colonne2 = valeur2 

-----------------------------------------|-------------------------------------- 

NOT Colonne = valeur 

-----------------------------------------|-------------------------------------- 

NOT Colonne BETWEEN valeur1 AND valeur2  | Colonne  valeur2 

-----------------------------------------|-------------------------------------- 

Colonne LIKE '%valeur'                   | ennoloC LIKE 'ruelav%' 

-----------------------------------------|------------------------------------- 

Colonne LIKE 'val%eur'                   | Colonne LIKE 'val%' 

                                         | INTERSECT 

                                         | ennoloC LIKE 'rue%'  

-----------------------------------------|------------------------------------- 

Colonne IN (valeur1, valeur2)            | Colonne = valeur1 

                                         | UNION [ALL] 

                                         | Colonne = valeur2

NOTA : ennoloC étant le contenu de la colonne « Colonne » inversé (par exemple à l’aide de la fonction REVERSE) et stocké soit en colonne calculée persistante (MS SQL Server), soit sous forme d’index sur expression (PostGreSQL)

À SAVOIR : Il est possible de rendre la recherche de : Colonne LIKE ‘%valeur%’, sargable, en utilisant une technique d’index rotatif que les SGBDR n’ont pas encore intégrés actuellement mais qu’il est possible de simuler dans une table SQL.

3 – TRANSFORMATION SPÉCIFIQUES

Quelques problématiques plus subtiles…

Avec MS SQL Server :



Non sargable                             | récriture sargable  

=========================================|======================================  

YEAR(COL_DATE) = 2008                    | COL_DATE >= '2008-01-01' AND  

                                         | COL_DATE < '2009-01-01' 

-----------------------------------------|-----------------------------------------  

UPPER(COL_CHAINE) = 'MARTIN'             | COL_CHAINE = 'MARTIN'  

                                         | COLLATE French_CI_AS 

-----------------------------------------|-----------------------------------------  

CODE_POSTAL = 75001                      | CODE_POSTAL = '75001'  

-----------------------------------------|-----------------------------------------  

COL_NOMBRE = '1999'                      | COL_NOMBRE = 1999 

-----------------------------------------|-----------------------------------------





-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

4 réflexions au sujet de « Optimisation de requêtes SQL : « SARGABLE » c’est quoi ? »

  1. Avatar de Artemus24Artemus24

    Salut SQLPRO.
    Il serait bien de mieux préciser la définition de ce qui est sargable comme vous m’avez fait la remarque [url=https://www.developpez.net/forums/d1496411/bases-donnees/langage-sql/l-operateur-different-sargable/#post8122520]ici[/url].
    Le terme « recherche » est vague et peut-être pris à confusion avec celui de balayage de l’index.

    Vous avez une faute de frappe : pour satisfaire la dempande

    En ce qui concerne l’expression « COL_DATE >= ‘2008-01-01′ AND COL_DATE < '2009-01-01' " ne serait-il pas plus judicieux de mettre "COL_DATE BETWEEN '2008-01-01' AND '2008-12-31'" ?

    Remarque : c'est bien de nous donner la liste des prédicats sargable.
    Mais il serait intéressant de donner un exemple d'optimisation sous SQL Server en utilisant "SET STATISTICS IO ON" pour obtenir les indicateurs de performances.

    @+

    ↓
  2. Avatar de exentroexentro

    (je viens de remarquer que mon précédent message était incomplet, visiblement les chevrons dans les commentaires ne sont pas échappés s’il ne reconnait pas une balise, bref)

    Sont sargable :
    Colonne LIKE ‘valeur%’

    Ne sont pas sargable :
    Colonne LIKE ‘valeur%’

    Un oubli dans la correction ou une subtilité que je n’ai pas saisie ?

    Au passage, merci pour ce blog instructif ;)
    A+

    ↓
  4. Avatar de exentroexentro

    Dans la liste SARGABLE, il y a , il est également dans la liste non sargable.
    Quand j’ai vu la suite (réécriture sargable), je me suis dit que c’était intentionnel.
    Mais je m’interroge sur la réécriture,

    Non sargable | récriture sargable
    ———————–+————————
    Colonne LIKE ‘valeur%’ | ennoloC LIKE ‘ruelav%’

    A la vue de la réécriture, n’est ce pas ‘%valeur’ au lieu de ‘valeur%’ ? (dans le tableau des transformation spécifique et dans la liste des non sargable).

    Autre question, la réécriture de ne serait pas
    Colonne LIKE ‘val%’
    INTERSECT
    ennoloC LIKE ‘rue%’

    plutôt que
    Colonne LIKE ‘valeur%’
    INTERSECT
    ennoloC LIKE ‘ruelav%’

    ?

    ↓

Laisser un commentaire