Problème de performance de requêtes.

Version imprimable

Bonjour,

Question classique, je sais....

J'ai une BdD SQL Server avec (pour simplifier) 2 tables :
- Personne (UID, Nom, Prénom, DDN, Nom_UID, Prénom_UID) qui contient 7 millions de personnes.
Les champs Nom_UID et Prénom_UID sont associés à des ID uniques pour chaque nom et chaque prénom.

- Matches (Score, UID1, UID2) qui contient environ 700.000 lignes.
Cette table contient tous les scores de matching entre les noms, les prénoms...

Le Select que je veux faire :
Code:

1 2 3 4 5 6 7 8 SELECT 'H1-A'+ M.Score AS Score, P1.UID, P2.UID, '' FROM Matches M JOIN Personnes P1 ON M.UID1 = P1.Prénom_UID JOIN Personnes P2 ON M.UID2 = P2.Prénom_UID WHERE P1.DDN != '' AND P1.Nom = P2.Nom AND P1.DDN = P2.DDN AND P1.UID < P2.UID
Il tourne, tourne... et donne les résultats (environ 2.000) au bout de 20 minutes, ce qui est un peu long ...

J'ai 3 Index sur la table Matches : Un sur UID1, un sur UID2, et un sur (UID1, UID2)
Sur la table Personnes, j'ai des index sur les champs UID, Noms, DDN, Nom_UID et Prénom_UID.

Quand je demande à SQL de me montrer le plan, il m'indique qu'il fait (dans l'ordre):
- Une analyse de la table Matches (1%), suivi d'un Calcul scalaire (0%), puis d'un filtre (0%)
- une recherche d'index sur la table Personnes (P1). 6% . Index = Personnes_IX12
- Une correspondance de hash (jointure interne) 13 %
- une recherche d'index sur la table Personnes (P2). 7 % . Index = Personnes_IX12
- Une correspondance de hash (jointure interne). 73%
- Un calcul scalaire. 0%
- le SELECT. 0%

Description de l'Index Personnes_IX12 :
- Clé de l'index : DDN
- Colonnes incluses : UID, Nom, Prénom_UID

Même s'il y a 7 Millions de personnes dans la base, devoir attendre près de 20 minutes, ça fait un peu lent...
Surtout pour 2.000 réponses...
Je suis sur un PC avec 16 Go de RAM, et d'autres requêtes où, par exemple, je fais une auto-jointure entre la table Personnes se font rapidement (moins d'une minute.
Du genre :
Code:

1 2 3 4 SELECT P1.UID, P2.UID, '' FROM Personnes P1 JOIN Personnes P2 ON P1.Nom = P2.Nom AND P1.Prénom = P2.Prénom WHERE P1.DDN != '' and P2.DDN = ''
Ca donne un résultat en 10 secondes...

Que dois-je faire pour corriger le problème ?

Merci pour votre aide.

23/10/2017, 14h19
SQLpro

Cette requête n'est pas optimisable car elle fait un jointure circulaire.... Votre modèle est mauvais, ce qui vous oblige à cette jointure ignoble.

Revoyez votre modèle et tout rentrera dans l'ordre.

Au passage différente de c'est <> et non !=

A +
23/10/2017, 15h56
jjcasalo

Bonjour SQLPro,

Merci pour la réponse.

Pour l'utlilisation de != ou <>, les 2 donnent les mêmes résultats, et la seule différence semble être que l'un est au format ANSI.

Sur la jointure "ignoble" :
Le principe du programme est de trouver des personnes avec des noms proches. Du genre "Jean DUPONT" et "Jean DUPOND".
Ce que j'ai fait, c'est d'exporter tous les noms, prénoms et NomJeuneFille dans une table où chaque "nom" n'est présent qu'une seule fois. "Martin", par exemple, peut se retrouver en tant que Nom, NomJeuneFille ou Prénom.
Je normalise ces noms pour enlever les espaces, les tirets..., pour obtenir que "Jean-Pierre" et "Jean Pierre" soient fusionnés dans un seul enregistrement.
J'ai donc environ 1 million de nom uniques, à partir des 7 millions de personnes du départ.

J'applique ensuite sur cette liste de 1 millions de noms des calculs (Levenshtein, ...) pour dire que 2 noms sont proches. Le résultat est stocké dans la table Matches.

Je dois donc à un moment faire une jointure entre la table Personnes, la table matches, et la table Personne à nouveau, pour dire que "Jean-Pierre MARTINEAU" et "Jean Pierre MARTINAU" sont similaires.
Surtout que je teste tous les cas possibles (les noms et prénoms sont inversés, les noms et NomJeuneFille sont inversés...). Donc il faut que je puisse comparer les prénoms avec les prénoms, mais aussi avec les Noms, et avec les NomJeuneFille. En gros, je compare tout avec tout.

Refondre entièrement la structure de ma base ne me pose aucun problème, mais j'ai du mal à voir comment je peux représenter les données autrement, pour arriver à ce que je veux...

Merci pour votre aide.

Voici un modèle beaucoup plus correct pour ce faire, à base d’héritage et sans références circulaire :
Code:

1 2 3 4 CREATE TABLE Personne (UID INT IDENTITY PRIMARY KEY, DDN DATE) CREATE TABLE PersonneNom (UID INT PRIMARY KEY REFERENCES Personne (UID), Nom VARCHAR(32)) CREATE TABLE PersonnePrenom (UID INT PRIMARY KEY REFERENCES Personne (UID), Prenom VARCHAR(32)) CREATE TABLE Matches (UID1 INT REFERENCES Personne (UID), UID2 INT REFERENCES Personne (UID), MATCH_NOM BIT NOT NULL DEFAULT 1, Score INT, PRIMARY KEY (UID1, UID2), CHECK (UID1 <> UID2))
Au passage supprimez les accents dans les indentifiants SQL !

Une fois remodélisé de la sorte, les requêtes partent de la table MATCHES avec 2 jointures: l'une pour les noms avec MATCH_NOM = 1 et l'autre pour les prénoms avec MATCH_NOM = 0

A +

23/10/2017, 20h54
jjcasalo

En effet, ça demande de revoir entièrement le schéma.
Le piège, c'est que quand on part dans une direction et qu'on est le nez dans le guidon, on a du mal à prendre du recul.

Là, ça demandait de ne pas utiliser la technique habituelle de la représentation d'une Personne dans une table...

Merci beaucoup pour votre aide précieuse !

Et j'arrête les accents, promis !