Discussion :

[kfages]

Bonjour,

Je me pose une question concernant l'optimisation d'une requête utilisant la clause LIKE.

J'ai un tableau de plusieurs millions de lignes contenant une colonne pyWorkStatus.
Cette colonne peut avoir des valeurs commençant uniquement par :

• New
• Open
• Pending
• Resolved

Nous avons plusieurs requêtes nécessitant de rechercher les lignes ne commençant pas par Resolved
Nous avons créé les index qui vont bien incluant la colonne pyWorkStatus

dans ma requête je fais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

where pyWorkStatus not like 'Resolved%'

Est-ce que cette clause est la plus performante ?
J'ai pensé à d'autres clause comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
where pyWorkStatus < 'R'
where pyWorkStatus not like 'R%'

Ne connaissant pas bien comment fonctionne SQL Serveur, je me demande quelle syntaxe est la plus rapide.

Je vous remercie par avance

Cordialement

Kévin

[SQLpro]

A lire :
https://blog.developpez.com/sqlpro/p...sql_sargable_c

A +

[escartefigue]

Outre la réponse qui précède, la meilleure façon d'optimiser cette requête, est de remplacer une recherche sur libellé par une recherche sur code ou identifiant
Seule la table des codes statuts devrait contenir le libellé des statuts et il semble que ça ne soit pas le cas ici...

Avec un modèle respectant les formes normales :
LIGNE 1,1 --- concerner --- 0,n STATUT

Vous n'aurez plus besoin de votre index encombrant et sensible à la collation sur le libellé et qui en plus s'applique sur la table la plus volumineuse (celle que j'ai appelée ligne dans le mini mcd ci dessus)
Vous le remplacerez avantageusement par une jointure et l'utilisation de l'index sur la colonne code de la table STATUT, table qui ne contiendra que très peu de lignes

De plus si certains statuts sont très majoritaires et/ou qu'il y a très peu de valeurs distinctes, alors votre nouvel index sur libellé est non filtrant, du coup vous vous tapez un table scan malgré la présence de cet index ...

[kfages]

Merci à tous les 2 pour vos retours, c'est très instructif.

escartefigue> Pour information, il s'agit bien de l'identifiant du statut et non de son libellé. En réalité, je n'ai pas vraiment de marge de manœuvre sur le MCD car je développe sur une application qui se nomme PEGA (il s'agit d'un BPM/CRM qui génère du Java et qui est connecté à une base SQL Serveur). La table en question est générée automatiquement par PEGA et la colonne statut ne peut-être gérée comme vous le suggérer.

Pour information, cette table stocke des demandes (gérées via un processus)?

Et effectivement, je n'ai pas beaucoup de statut différent, une quinzaine tout au plus dont 6 ou 7 qui commencent par Resolved (Resolved-Completed, Resolved-Rejected, Resolved-Invalid...). Dans ma grosse table, j'ai une proportion de 95% de lignes avec le statut Resolved-xxx et le reste avec un statut différent indiquant que mes lignes correspondent à des "processus" non clos.

Dans mon outil, j'ai quelques requêtes peu restrictives (via d'autres critères) pour remonter l'ensemble des demandes non closes d'où mon critère

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

pyStatusWork not like 'Resolved%'

Je pense donc que l'index sera utilisé car cela permet de ne lire que 5% des lignes enfin je pense
et du coup, je me demandais si ce critère est le plus efficace par rapport aux autres que j'ai cités ou s'il y a encore mieux.

PS : nous avons déjà un traitement d'archive pour déplacer les demandes closes dans une autre base de données mais une exigence métier nous oblige à garde les demandes closes de moins de 6 mois. Ce n'est donc pas non plus une piste d'amélioration.

Cordialement

[escartefigue]

Dans mon outil, j'ai quelques requêtes peu restrictives (via d'autres critères) pour remonter l'ensemble des demandes non closes d'où mon critère

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

pyStatusWork not like 'Resolved%'

Je pense donc que l'index sera utilisé car cela permet de ne lire que 5% des lignes enfin je pense
et du coup, je me demandais si ce critère est le plus efficace par rapport aux autres que j'ai cités ou s'il y a encore mieux.

Ce ne sera pas le cas : "not like" tout comme "<>" sont des prédicats non "sargables" (Search Argument Able) c'est à dire que l'utilisation d'un index n'est pas possible en tant que critère de recherche (un index peut être toutefois parcouru séquentiellement en cas d'index couvrant, mais ce n'est pas du tout la même chose)

[kfages]

Merci pour tout escartefigue. Je comprend ce que vous expliquez après avoir relu le lien de SQLpro.
Du coup, dans mon cas, je peux alors remplacer mon prédicat par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

pyStatusWork < 'R'

Là au moins, le prédicat est sargable si j'ai bien compris et du coup l'index sera utilisé.
Je peux faire ce inférieur à 'R' car dans mon cas de figure tous les statuts indiquant que mes demandes ne sont pas closes commencent forcément par une lettre N, O ou P.

[aieeeuuuuu]

Bonjour,

il serait intéressant d'avoir la structure des tables et la requête compléte, car une autre approche sans doute plus efficace serait de créer un index filtré, idéalement couvrant

[elsuket]

Hé oui, en créant un index filtré, on doit pouvoir s'en sortir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE INDEX IX_maTable_mesColonnes
ON dbo.maTable(mesColonnes)
WHERE pyStatusWork NOT LIKE 'Resolved%'

On peut éventuellement ajouter une clause INCLUDE pour que l'index soit couvrant; il faut pour cela juger de la quantité de lignes retournées, et du coût des key lookup induits par l'absence de clause INCLUDE.

L'approche la plus raisonnable cependant reste celle d'une table de statuts, proposée par Escartefigue.

@++

[kfages]

Merci aieeeuuuuu et elsuket !!

Je n'avais pas pris connaissance de vos derniers retours. Je ne connaissait pas l'existence des index filtrés.
Je vais me pencher dessus pour voir les résultats en terme de performance.

Pour la clause include, effectivement je m'en suis servi pour améliorer les performances de la requête.
Avec tous ces conseils, j'ai pu élaborer un lot d'index pour couvrir 60 requêtes hétérogènes sur mon application.
Ce qui m'aura le plus aider est l'article expliquant comment un prédicat est ou pas sargable.

Bonne journée à tous,