Problèmes de performance de vue et de sous-requête SQL Server 2005

Question

Je possède les deux tables suivantes :

Personne - environ 7000 lignes

• PerosnId - 9 caractères
• TypePersonne - Char (un parmi 'F' / 'C' / 'M')

PersonneStatuts - environ 90K lignes (environ 13 lignes pour chaque ligne de Personne)

• Id - Identité
• PersonneId - 9 caractères
• CodeStatut - entier
• DernièreMiseÀJour - DateTime

Je utilise une vue pour renvoyer la dernière ligne de PersonneStatuts concernant une ligne de Personne unique :

DerniersStatutsPersonne

SELECT      PersonneId, CodeStatut 
FROM        PersonneStatuts ps1
WHERE       DernièreMiseÀJour = (SELECT MAX(DernièreMiseÀJour) 
                                  FROM   PersonneStatuts ps2
                                  WHERE ps2.PersonneId = ps1.PersonneId)

La requête suivante :

SELECT DISTINCT Personne.Id
FROM Personne  
WHERE  Personne.Id IN (SELECT PersonneId 
              FROM DerniersStatutsPersonne
              WHERE CodeStatut = 12) AND
Personne.TypePersonne='F'

prend environ une minute pour s'exécuter, et donc dépasse le délai, tandis que la suivante :

SELECT DISTINCT Personne.Id
FROM Personne  
WHERE  Personne.Id  IN (SELECT PersonneId 
                     FROM DerniersStatutsPersonne
                     WHERE CodeStatut = 12)

s'exécute presque instantanément.

Plan 2 http://tinypic.com/images/404.gif

Je n'arrive pas à comprendre pourquoi l'ajout de la clause WHERE dans la première requête :

Personne.TypePersonne='F'

fait une telle différence.

Est-ce que quelqu'un pourrait m'orienter, s'il vous plaît?

Answer 1

C'est parce que l'optimiseur de requêtes a décidé que, étant donné que vous traitez directement avec une colonne persontype, il serait peut-être plus rapide d'utiliser un index sur cette colonne (si vous en aviez un), ou sinon une analyse directe de la table + filtre. Cela est en comparaison au coût perçu/estimé d'évaluer la sous-requête et de faire une comparaison de liste IN, sans connaître les métriques/statistiques de la liste de la sous-requête.

Même en regardant votre requête, j'aurais presque deviné que ce serait plus rapide de balayer et filtrer sur la colonne de la table plutôt que de descendre dans la sous-requête, qui implique une expansion d'une vue.

Vous devriez être en mesure de réécrire (pour 2005+) la vue comme

--VUE: LatestPersonStatuses
SELECT PersonId, StatusCode 
FROM   (SELECT *, rownum=row_number() over (
                  partition by personID order by LastUpdateDate desc)
        from PersonStatuses p) X
WHERE rownum = 1

Peut-être que cela aidera l'optimiseur