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

[Barbibulle]

Bonjour,

Juste quelques remarques :

Je suppose que no_film, no_acteur sont les clés primaires des tables respectives films et acteurs et que no_film et no_acteur de la table roles sont deux clés étrangères vers leurs tables respectives...

La requête de SergioMaster est à mon avis plus performante. Mais bon vu le volume de données manipulées ça n'est pas du tout significatif (un acteur ne vas pas avoir des millions de roles)....

La requête de SergioMaster demande au serveur de manipuler la liste des films d'un acteur.

Alors que la requête de Redlet va manipuler cette même liste et en plus la sous-requête.

D'un point de vue fonctionnelle il y a également une différence.

La requête de SergioMaster ne retournera qu'un seul film alors celle de Redlet retournera tous les films correspondant à l'année du dernier film.

Une autre solution pourrait s'écrire :

Code :

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

1
2
3
4
5
6
7
8
9
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 f.annee = (select max(f1.annee)
                   from roles r1
                   inner join films f1 on f1.no_film=r1.no_film
                   where r1.no_acteur=:NNN
                 )

Moins performant que celle de SergioMaster (la liste des films d'un acteurs est manipulée 2 fois, une pour optenir le max(année) et l'autre pour la liste des films de l'acteur) mais qui récupère le même résultat que la requête de Redlet.

Je ne pense pas que les CTE apporte quelque chose ici. Ca reste des requêtes "simples".

[Redlet]

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.

La table films contient 145150 enregistrements. La table roles en contient 642475. La table acteurs en contient 6395.

NB : en fait, je ne manipule pas des acteurs, des films et des rôles mais les tables sur lesquelles je travaille réellement ont une structure identique à l'exemple que j'ai donné. J'ai donné un exemple qui fait appel à un vocabulaire qui permet de comprendre facilement ce que je veux faire. Et dans ma base de données réelle, j'ai bien une date et non une année .

600000 enregistrements, ce n'est peut-être pas énorme, mais on devrait commencer à sentir des différences de performance d'une requête à l'autre, non ?

[Redlet]

Bonjour,

Si on fait select * from table where condition order by champ rows 1 et si la table est indexée sur champ, un seul enregistrement est lu, n'est-ce pas ? Dans ce cas, la méthode de SergioMaster est d'une efficacité redoutable.

[Redlet]

Bonjour,

la liste des films d'un acteurs est manipulée 2 fois, une pour optenir le max(année) et l'autre pour la liste des films de l'acteur)

Si on fait select max(champ) from table et si champ est indexé, un seul enregistrement est lu, non ?

[Barbibulle]

Non car si on met un index sur annee c'est tous films confondu. Or la requete max ne travaille sur que quelques films (ceux de l'acteur sélectionnée).

La différences de performance entre la requête de SergioMaster et celle que j'ai donnée est minime... Le volume manipulée est limité aux roles que peut avoir un acteur.


Admettons qu'un acteur fasse dans sa carrière 10 000 films (ce qui me parait énorme même dans le genre pour adulte )

La requête de SergioMaster va utiliser les index(cle etrangère) et balayer les 10 000 films et les trier.

Ma requête va rechercher le max et donc balayer les 10 000 films de l'acteur puis va de nouveau balayer les 10 000 films pour voir celui (ou ceux) qui a l'année qui correspond au max.

Résultat des courses SergioMaster travail que sur les films d'un acteur. Ma requête balaye une fois de plus cette même liste...

Par curiosité que donne :

Code :

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

1
2
3
select first 1 count(r.no_film) as nombre_film, r.no_acteur from roles r
group by r.no_acteur
order by 1 desc

histoire de voir combien de films maxi seront manipulée par ces requêtes.