Une alternative a WHERE .... like '%.......' [Résolu]

Signaler
-
Messages postés
3659
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
26 novembre 2020
-
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

3 réponses

Messages postés
30356
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
27 novembre 2020
3 029
Bonjour,

Tu es sur sqlserver quelle version ?

Sur SQL Server 2017 Express
Messages postés
30356
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
27 novembre 2020
3 029
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

J'ai essayé ça, tu penses que ça peut faire l'affaire ?

CREATE INDEX index1 ON MarneEtOise_panorama_Diagbox.[dbo].[TNameData] (PropertyFullName)

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 [PropertyFullName] like '%Description'
and LEN(variableName) > 20
and LEN(PropertyFullName) > 20
order by 1

[;]
Messages postés
30356
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
27 novembre 2020
3 029 > Ploup
L'index, ça se créé une seule fois.
Il ne faut pas le recréer à chaque requête....

Pour le reste, ça fait des années que je n'ai pas mis le nez dans une bdd sqlserver.
Tout est indiqué dans la doc. Je ne peux rien te dire de plus.
>
Messages postés
30356
Date d'inscription
mercredi 22 octobre 2003
Statut
Modérateur
Dernière intervention
27 novembre 2020

ça marche merci :)
Messages postés
3659
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
26 novembre 2020
975
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