Faire usage de l'index lors de la JOINTURE avec l'encontre de plusieurs colonnes

Simplifié, j'ai deux tables, contacts et donotcall

CREATE TABLE contacts
(
    id int PRIMARY KEY,
    phone1 varchar(20) NULL,
    phone2 varchar(20) NULL,
    phone3 varchar(20) NULL,
    phone4 varchar(20) NULL
);
CREATE TABLE donotcall
(
    list_id int NOT NULL,
    phone varchar(20) NOT NULL
);
CREATE NONCLUSTERED INDEX IX_donotcall_list_phone ON donotcall
(
    list_id ASC,
    phone ASC
);

Je voudrais voir ce que les contacts correspond au numéro de téléphone dans une liste spécifique de DoNotCall téléphone.
Pour accélérer la recherche, j'ai indexé donotcall sur list_id et phone.

Quand je fais la JOINTURE suivante, il prend beaucoup de temps (par exemple. 9 secondes):

SELECT DISTINCT c.id
FROM contacts c
JOIN donotcall d
    ON d.list_id = 1
    AND d.phone IN (c.phone1, c.phone2, c.phone3, c.phone4)

Faire usage de l'index lors de la JOINTURE avec l'encontre de plusieurs colonnes

Plan d'exécution sur Pastebin

Alors que si j'LEFT JOIN sur chaque champ téléphone séparément, il fonctionne beaucoup plus vite (par exemple. 1.5 secondes):

SELECT c.id
FROM contacts c
LEFT JOIN donotcall d1
    ON d1.list_id = 1
    AND d1.phone = c.phone1
LEFT JOIN donotcall d2
    ON d2.list_id = 1
    AND d2.phone = c.phone2
LEFT JOIN donotcall d3
    ON d3.list_id = 1
    AND d3.phone = c.phone3
LEFT JOIN donotcall d4
    ON d4.list_id = 1
    AND d4.phone = c.phone4
WHERE
    d1.phone IS NOT NULL
    OR d2.phone IS NOT NULL
    OR d3.phone IS NOT NULL
    OR d4.phone IS NOT NULL

Faire usage de l'index lors de la JOINTURE avec l'encontre de plusieurs colonnes

Plan d'exécution sur Pastebin

Mon hypothèse est que le premier extrait s'exécute lentement, car il n'utilise pas l'index sur donotcall.
Alors, comment faire une jointure vers plusieurs colonnes et de toujours utiliser l'index?

Ce que vous devez faire est de fixer votre datbase structure. Vous ne devriez JAMAIS avoir phone1, fixe2, phone3, phone4 - qui indique que vous avez besoin d'une table d'enfant.
Point à noter. J'aurais également fait différemment si j'ai eu le choix. Mais parfois, vous êtes coincé avec une structure que d'autres ont créé, sans espoir de refactoring.

OriginalL'auteur ANisus | 2013-09-04

6

SQL Server pourrait penser que la résolution de IN (c.phone1, c.phone2, c.phone3, c.phone4) l'aide d'un index est trop cher.

Vous pouvez tester si l'index est plus rapide avec un soupçon:
```
SELECT c.*
FROM contacts c
JOIN donotcall d with (index(IX_donotcall_list_phone))
    ON d.list_id = 1
    AND d.phone IN (c.phone1, c.phone2, c.phone3, c.phone4)
```
De la plans de requête que vous avez posté, il montre le premier plan est estimé à produire 40k lignes, mais il retourne juste 21 lignes. Le deuxième plan d'estimations 1 ligne (et bien sûr retourne 21).

Sont vos statistiques jusqu'à ce jour? Out-of-date, les statistiques peuvent expliquer l'analyseur de requêtes de faire de mauvais choix. Statistiques devraient être mises à jour automatiquement ou dans un hebdomadaire du travail. Vérification de l'âge de vos statistiques avec:
```
select  object_name(ind.object_id) as TableName
,       ind.name as IndexName
,       stats_date(ind.object_id, ind.index_id) as StatisticsDate
from    sys.indexes ind
order by 
        stats_date(ind.object_id, ind.index_id) desc
```
Vous pouvez mettre à jour manuellement avec:
```
EXEC sp_updatestats;
```
Bonne suggestion. J'ai essayé d'ajouter l'indication. Toutefois, cela n'a rien changé. Le LEFT JOIN version est encore beaucoup plus vite. Il me semble que si SQL server n'est pas en mesure de résoudre le code à l'aide de l'index.
Avez-vous essayé de comparer les plans de requête? (Menu Requête, puis d'Inclure le Plan d'Exécution Réel.)
Je suis en train de le faire, mais j'ai très peu d'expérience dans l'analyse des plans de requête. De ce que je peux voir, le LEFT JOIN solution est assez simple, à l'aide de 4 Hachage Correspond à (à Droite Jointures Externes). La seule JOINDRE solution n'complètement différemment. Deux boucles imbriquées (Jointures internes), d'abord avec l'index, puis avec la table contacts.
Existe-il des services gratuits en ligne vous permettant de télécharger et de visualiser les plans de requête à partager avec les autres?
Vous pouvez l'exporter au format XML et de le mettre sur pastebin.com. D'autres peuvent visualiser le plan en utilisant la connexion sqlsentry.net/plan-explorer/sql-server-query-view.asp

OriginalL'auteur Andomar
0

Avec cette mauvaise structure de base de données, une UNION de TOUS requête peut être plus rapide.

Alors que l'UNION de TOUS actuellement plus performant que le simple JOINTURE de solution, c'est un peu plus lent que la JOINTURE GAUCHE de la solution.

OriginalL'auteur HLGEM

Vous devez vous connecter pour publier un commentaire.