Récupérer des données à l'aide d'une requête SQL simple et rapide

Question

Je dispose des données suivantes :

ExamEntry   Student_ID     Grade
  11           1             80
  12           2             70
  13           3             20
  14           3             68
  15           4             75

Je veux trouver tous les étudiants qui ont réussi un examen. Dans ce cas, s'il y a plusieurs examens qu'un étudiant a passé, je dois trouver le dernier résultat.

Dans ce cas, je dirais donc que tous les élèves ont réussi.

Puis-je le trouver en une seule recherche rapide ? Je procède de la manière suivante :

1. Trouver la liste des entrées en select max(ExamEntry) from data group by Student_ID

2. Trouver les résultats :

select ExamEntry from data where ExamEntry in ( ).

Mais c'est TRÈS lent - j'ai environ 1000 entrées, et ce processus en deux étapes prend 10 secondes.

Existe-t-il une meilleure solution ?

Merci.

Answer 1

Si votre requête est très lente avec 1000 enregistrements dans votre table, c'est qu'il y a un problème. Pour un système de base de données moderne, une table contenant 1000 entrées est considérée comme très très petite.
Il est fort probable que vous n'ayez pas fourni de clé (primaire) pour votre table ?

En supposant qu'un étudiant réussisse si au moins une de ses notes est supérieure au minimum requis, la question à poser serait la suivante :

SELECT 
  Student_ID
, MAX(Grade) AS maxGrade
FROM table_name
GROUP BY Student_ID
HAVING maxGrade > MINIMUM_GRADE_NEEDED

Si vous avez vraiment besoin que la dernière note soit supérieure au minimum :

SELECT 
  Student_ID
, Grade
FROM table_name
WHERE ExamEntry IN ( 
    SELECT 
      MAX(ExamEntry) 
    FROM table_name 
    GROUP BY Student_ID
)
HAVING Grade > MINIMUM_GRADE_NEEDED

Answer 2

SELECT student_id, MAX(ExamEntry)
FROM data
WHERE Grade > :threshold
GROUP BY student_id

Comme ceci ?

Answer 3

Je vais supposer que vous avez une table étudiant et une table test et que la table que vous nous montrez est la table test_result... (si vous n'avez pas une structure similaire, vous devriez revoir votre schéma).

select s.id, s.name, t.name, max(r.score)
from student s
left outer join test_result r on r.student_id = s.id
left outer join test t on r.test_id = t.id
group by s.id, s.name, t.name

Tous les champs contenant l'identifiant doivent être indexés.

Si vous n'avez vraiment qu'un seul test (type) dans votre domaine... alors la requête serait la suivante

select s.id, s.name, max(r.score)
from student s
left outer join test_result r on r.student_id = s.id
group by s.id, s.name

Answer 4

Comme nous l'avons mentionné, l'indexation est un outil puissant pour accélérer les requêtes. L'ordre de l'index est toutefois d'une importance fondamentale.

Un index dans l'ordre de (ExamEntry) puis (Student_ID) puis (Grade) serait pratiquement inutile pour trouver les examens que l'étudiant a réussis.

Un index dans l'ordre inverse conviendrait parfaitement, si l'on voulait seulement savoir quels examens ont été passés. Cela permettrait au moteur de recherche d'identifier rapidement les lignes correspondant aux examens réussis et de ne traiter que celles-ci.

Dans MS SQL Server, cela peut être fait avec...

CREATE INDEX [IX_results] ON [dbo].[results] 
(
    [Grade],
    [Student_ID],
    [ExamEntry]
)
ON [PRIMARY]

(Je recommande de lire davantage sur les index pour voir quelles sont les autres options disponibles, telles que ClusterdIndexes, etc, etc).

Avec cet index, la requête suivante permettrait d'ignorer très rapidement les examens "échoués" et d'afficher uniquement les étudiants qui ont réussi l'examen...

(Cela suppose que si vous obtenez plus de 60, vous êtes considéré comme ayant réussi, même si vous repassez l'examen par la suite et obtenez 27).

SELECT
    Student_ID
FROM
    [results]
WHERE
    Grade >= 60
GROUP BY
    Student_ID

Si vous avez absolument besoin de la valeur la plus récente, vous devez modifier l'ordre de l'index pour obtenir quelque chose comme...

CREATE INDEX [IX_results] ON [dbo].[results] 
(
    [Student_ID],
    [ExamEntry],
    [Grade]
)
ON [PRIMARY]

En effet, la première chose qui nous intéresse est l'ExamEntry le plus récent pour un étudiant donné. Ce qui peut être réalisé en utilisant la requête suivante...

SELECT
   *
FROM
   [results]
WHERE
   [results].ExamEntry = (
                          SELECT
                              MAX([student_results].ExamEntry)
                          FROM
                              [results] AS [student_results]
                          WHERE
                              [student_results].Student_ID = [results].student_id
                         )
   AND [results].Grade > 60

Une telle sous-requête peut sembler lente, notamment parce qu'elle semble être exécutée pour chaque ligne de [résultats].

Ce n'est pourtant pas le cas...
- La requête principale et la requête secondaire font référence à la même table.
- Le moteur de recherche parcourt l'index à la recherche de chaque identifiant unique d'étudiant.
- La sous-requête est exécutée pour l'identifiant de l'étudiant.
- Le moteur de recherche se trouve déjà dans cette partie de l'index.
- Une nouvelle recherche d'index n'est donc pas nécessaire

EDIT :

Il a été dit qu'à partir de 1000 enregistrements, les index ne sont plus pertinents. Il convient de noter que la question indique qu'il y a 1000 enregistrements retournés, et non que la table contient 1000 enregistrements. Pour qu'une requête de base prenne autant de temps que ce qui est indiqué, je parierais que la table contient bien plus que 1000 enregistrements. Peut-être cela peut-il être clarifié ?

EDIT :

Je viens d'étudier 3 requêtes, avec 999 enregistrements dans chacune d'elles (3 résultats d'examen pour chacun des 333 étudiants)

Méthode 1 : WHERE a.ExamEntry = (SELECT MAX(b.ExamEntry) FROM results [a] WHERE a.Student_ID = b.student_id)

Méthode 2 : WHERE a.ExamEntry IN (SELECT MAX(ExamEntry) FROM resuls GROUP BY Student_ID)

Méthode 3 : Utilisation d'un INNER JOIN au lieu de la clause IN

Les temps suivants ont été trouvés :

Method    QueryCost(No Index)   QueryCost(WithIndex)
   1               23%                    9%
   2               38%                   46%
   3               38%                   46%

Ainsi, la requête 1 est plus rapide indépendamment des index, mais les index rendent la méthode 1 nettement plus rapide.

La raison en est que les index permettent d'effectuer des recherches, alors qu'autrement il faut effectuer un balayage. La différence entre une loi linéaire et une loi carrée.

Answer 5

J'ai utilisé les conseils donnés ici, et voici la requête que j'ai trouvée qui s'exécute presque 3 ordres plus vite que ma première requête (.03 sec au lieu de 10 sec) :

SELECT ExamEntry, Student_ID, Grade from data,
       ( SELECT max(ExamEntry) as ExId GROUP BY Student_ID) as newdata
WHERE `data`.`ExamEntry`=`newdata`.`ExId` AND Grade > 60;

Merci à tous !