Discussion :

[Redlet]

Bonjour,

J'ai trois tables :
films (no_film, annee, titre)
acteurs (no_acteur, nom)
roles (no_film, no_acteur)

Pour retrouver tous les films dans lesquels un acteur a tourné :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select F.no_film, F.annee, F.titre
from roles R
join films F on F.no_film = R.no_film
where R.no_acteur = NNN

Ce que je voudrais, c'est ne retrouver que le dernier film dans lequel l'acteur NNN a tourné.
Comment faire ? Avec max() ? Comment ?

D'avance merci.

[escartefigue]

bonjour,

Besoin très récurrent, voici l'une des solutions :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
  select F.no_film, F.annee, F.titre
  from       roles R
  inner join films F 
     on F.no_film = R.no_film
  where R.no_acteur = NNN
    and not exists
       (select 0
        from roles R2
        inner join films F2 
          on  F2.no_film = R2.no_film
        where R2.no_acteur = R.no_acteur
          and F2.annee > F.annee)

J'ai ajouté "INNER" dans votre requête principale, bien que facultatif, ça me semble plus clair

Limite de la solution, votre table ne propose que "année", ce qui fait court pour comparer des dates, une date complète eut été mieux, des fois que l'acteur ait tourné 2 films la même année

[Redlet]

Impeccable, ça fonctionne. Et votre remarque sur la date est très pertinente. Merci.

Techniquement, la requête que j'ai écrite initialement est exécutée deux fois puis les lignes retournées par la première exécution sont comparées à celles retournées par la deuxième exécution. C'est bien ça ? Est-ce la solution la plus simple ou la plus rapide ? Quels sont les index à créer pour que ça fonctionne le plus rapidement possible ?

Encore merci.

[escartefigue]

Impeccable, ça fonctionne. Et votre remarque sur la date est très pertinente. Merci.

Tant mieux
Si ma réponse a pu vous rendre service, votez en conséquence

Techniquement, la requête que j'ai écrite initialement est exécutée deux fois puis les lignes retournées par la première exécution sont comparées à celles retournées par la deuxième exécution. C'est bien ça ?

Non, votre requete initiale n'est exécutée qu'une seule fois, et pour chaque ligne sélectionnée, la sous-requete est exécutée

Est-ce la solution la plus simple ou la plus rapide ?
Encore merci.

Alors la on entre dans un débat beaucoup plus long, d'autres solutions sont possibles (sub select avec un max, jointure outer avec un résultat null...), la requete la plus performante dépend de nombreux paramètres parmi lesquels la volumétrie traitée, le facteur de filtrage des index, le cluster ratio et autres considérations entrent en ligne de compte
Disons que cette solution est souvent la meilleure d'un point de vue performances, car le where exists avec utilisation d'une constante a le mérite, sous réserve que le where permette d'utiliser un index bien sur, de ne faire qu'une seule lecture de l'index et de ne pas transporter les données de la table. Dans certains cas, une jointure outer avec un test de nullité peut s'avérer meilleur, par contre le subselect avec max ne sera jamais plus performant (au mieux égal)
D'un point de vue simplicité, les 3 solutions se valent

Quels sont les index à créer pour que ça fonctionne le plus rapidement possible ?

Les index doivent prioritairement satisfaire les where, de façon à limiter le nombre de lignes à traiter
Les index doivent aussi prioritairement satisfaire les prédicats de jointure, quand jointure il y a
Tout index doit être discriminant, par exemple, un index sur le code sexe qui ne prend que 2, voire 3 valeurs (si on a droit à un code pour sexe non renseigné) ne sert absolument à rien
Après d'autres qualités peuvent êtres intéressantes comme par exemple un index cluster qui correspond à l'order by, si le cluster ratio est de 100%, ou un index qui est couvrant, c'est à dire qu'il n'est pas nécessaire d'aller dans les data puisque l'index contient toutes les colonnes requises, mais c'est accessoire comparativement aux 3 premiers critères.
Et aussi, il ne faut pas créer d'index inutiles, c'est couteux lors des MàJ

[SergioMaster]

Bonjour,

j'arrive un peu tard mais je vois quelques autres solutions, quoique peut être pas aussi performante sur une grosse base de données

on est d'accord ,pour obtenir tous les films de l'acteur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select F.no_film, F.annee, F.titre
from roles R
inner join films F on F.no_film = R.no_film
where R.no_acteur = NNN

si l'on les avait voulu en ordre chronologique on aurait rajouté un ORDER BY ANNEE et à fortiori si l'on veut du plus récent au plus ancien on aurait rajouté DESC je ne reviendrais pas sur le fait que l'acteur peut avoir "sorti" plusieurs films la même année, donc admettons qu'un acteur ne sorte qu'un film par an

récupérer le dernier film devient facile grâce à une instruction de limitation de nombre d'enregistrements

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select FIRST 1 F.no_film, F.annee, F.titre
from roles R
inner join films F on F.no_film = R.no_film
where R.no_acteur = NNN
ORDER BY F.ANNEE DESC

ou (je suis moins habitué à la seconde syntaxe ROWS)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select F.no_film, F.annee, F.titre
from roles R
inner join films F on F.no_film = R.no_film
where R.no_acteur = NNN
ORDER BY F.ANNEE DESC
ROWS 1

je pense que dans ce cas même pour un acteur de 100 ans ayant joué depuis l'enfance c'est jouable (max 100 enregistrements récupérés puis triés par date) et peu consommateur de ressource (plus que dans la solution d'escartefigue mais bon ....)

[Redlet]

Merci à escartefigue et à SergioMaster.

J'aime bien la méthode de SergioMaster.

Si je comprends bien, avec la méthode de escartefigue :
- la requête principale accède à autant de lignes que l'acteur a tourné de films
- pour chaque film, la sous-requête (qui fait quasiment la même chose que la requête principale) accède, elle aussi, à autant de lignes que l'acteur a tourné de films.

Finalement, avec cette méthode, on accède à n² enregistrements, n étant le nombre de films dans lesquels l'acteur a tourné.

Avec la méthode de SergioMaster, une requête unique accède à seulement n enregistrements au lieu de n². Ça paraît, sur le papier, plus rapide. Pourtant, à l'exécution, les performances sont pratiquement identiques. Je n'y comprends rien...

[escartefigue]

Bonjour,

Les mesures ne sont significatives que si les volumes sont suffisants, combien y a -t- il de lignes dans chacune de vos tables ?
En effet, il y a des chances que la méthode de SergioMaster soit plus rapide, mais le tri a aussi un coût qui peut expliquer le résultat équivalent.

[SergioMaster]

Bonjour,

la méthode d'Escartefigue (ça sent bon la provence) est beaucoup plus générique et de ce point vue mieux (surtout sur des grosses tables)

Si je comprends bien, avec la méthode de escartefigue :
- la requête principale accède à autant de lignes que l'acteur a tourné de films

oui

- pour chaque film, la sous-requête (qui fait quasiment la même chose que la requête principale) accède, elle aussi, à autant de lignes que l'acteur a tourné de films.

pas tout à fait, il ne faut pas oublier le and F2.annee > F.annee

Ça paraît, sur le papier, plus rapide. Pourtant, à l'exécution, les performances sont pratiquement identiques. Je n'y comprends rien...

tu oublies qu'il faut trier la table résultat dans ma proposition et de plus il faudrait certainement faire un test avec de grosses charges de données (ce qui pour ce cas me parait peu vraisemblable).


en fait ce qui me choque toujours c'est que l'on fait une recherche sur l'année et non une date de sortie (et pour un acteur prolifique cela peut faire beaucoup, pour peu qu'un réalisateur, que le montage etc... soit lent)
Il doit encore y avoir une autre approche avec les CTE (Common Table Expression) mais j'avoue ne pas assez bien les maitriser