Une alternative a WHERE .... like '%.......' Résolu/Fermé

Question

Bonjour, 

Je voudrais optimiser un peu mon script et j'ai vu que les cherches avec un %xxxxx consommaient énormément de ressources sur une grosse BDD, j'ai vu que je devais utiliser les index ? Mais je n'arrive à comprendre comment les utiliser, est-ce que quelqu'un pourrait m'expliquer comment faire peut-être ? Merci d'avance.

Bien cordialement,

Ploup

Configuration: Windows / Chrome 83.0.4103.116

jordane45 · Answer

Bonjour,

Tu es sur sqlserver quelle version ?

jordane45 · Answer

Un index ne remplacera pas la façon de faire ta requête
Si tu dois utiliser un LIKE pour chercher une "portion" de text dans ta table, tu n'auras pas le choix que de continuer à faire ainsi;

Par contre, mettre des INDEX sur ses tables permets de gagner en performance.
Attention toutefois à n'en mettre que sur les champs "utils" et pas n'importe où.
( en gros, tu index que les champs sur lesquelles tu dois appliquer une clause WHERE ou pour les JOIN )

Pour créer un index : 
https://docs.microsoft.com/fr-fr/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

Reivax962 · Answer

Bonjour,

Vu ta requête, je doute qu'un index sur [PropertyFullName] puise aider en quoi que ce soit.

Petite explication sur les index.
Sous SQL Server, ils sont de deux types : les index CLUSTERED (un seul par table), et les index standards. Les premiers sont uniques parce qu'en fait ils donnent la façon dont sont écrites les données dans la table.
Par exemple, imagine un dictionnaire : c'est un index CLUSTERED sur les mots, rangés par ordre alphabétique. Tu retrouves assez rapidement un mot, sa nature, sa définition dans le dictionnaire car ils sont rangés par ordre alphabétique.
Un index non clustered c'est presque pareil mais ça ne donne pas directement les données, seulement leur adresse, comme dans une table des matières : tu retrouves rapidement ta donnée parce qu'on te donne la page où tu peux la trouver.
Au final du point de vue de l'écriture des requêtes, ces deux types d'index fonctionnent à peu près pareil.

Maintenant, reprenant ton cas avec l'analogie du dictionnaire. Si tu veux trouver tous les mots de plus de 20 lettres qui se terminent par « re »... Ordre alphabétique ou pas, tu vas devoir lire toutes les entrées... Donc l'index ne sert à rien.
Alors comment faire ?

Si tu as besoin fréquemment d'isoler les champs de type description, le mieux est encore de créer une colonne qui indique explicitement que le champ en question est de type description.
ALTER TABLE [TNameData] ADD [is_description] bit not null default 0;
Puis en initialiser la valeur
UPDATE [TNameData] SET [is_description] = CASE
    WHEN [PropertyFullName] like '%Description'
        AND LEN(variableName) > 20
        AND LEN(PropertyFullName) > 20 THEN 1
    ELSE 0 END;

Ensuite tu peux mettre un index sur ce champ, même si ce n'est pas optimal ça devrait être mieux que de chercher à chaque fois un nom dans une chaîne :
CREATE INDEX idx_TNameData_is_description ON MarneEtOise_panorama_Diagbox.[dbo].[TNameData] (is_description);

Et ta requête de sélection devient :
SELECT distinct SUBSTRING([PropertyFullName], 1, LEN([PropertyFullName]) - 12), [PropertyType], vn.Gama
FROM MarneEtOise_panorama_Diagbox.[dbo].[TNameData]
inner join @variableNameGamas vn on SUBSTRING(vn.variableName, 1, LEN(vn.variableName) - 7) like SUBSTRING([PropertyFullName], 1, LEN([PropertyFullName]) - 12)
where [is_description] = 1
order by 1
Ce n'est pas encore optimal à cause de la jointure qui elle aussi ne permet pas d'utiliser les index, si tu trouves le moyen de la faire sur des champs plutôt que des valeurs fonction des champs, ce serait mieux.

Voilà, bon courage :)

Xavier

Une alternative a WHERE .... like '%.......'

3 réponses

Discussions similaires