Optimiser une requête

[chlebta*tsotsi]

salut,
Voici ma requête. Je veux l'optimiser pour diminuer le temps d’exécution :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SELECT DISTINCT Student.Last_Name AS lname, 
  Student.First_Name AS fname,
  Student_Service_Header.header_id AS hid, 
  Student_Service_Header.pract_id AS pid,
  upd, 
  Student_Service_Header.medicaid_id AS medid,
  Student_Service_Header.service_area AS svcarea, 
  sub,
  Student_Service_Header.district_id AS did,
  Practitioner.first_name AS practfn, 
  Practitioner.last_name AS practln,
  Practitioner.account AS acct, transdt,
  Student_Service_Header.QC_status AS sts, 
  Student_Service_Header.QC_comment AS cmt,
  District.district_name AS dname
FROM Student
JOIN Student_Service_Header 
    ON Student.medicaid_id = Student_Service_Header.medicaid_id 
    AND Student.district_id = Student_Service_Header.district_id
  JOIN vw_Service_Details ON Student_Service_Header.header_id = vw_Service_Details.header_id
  JOIN Practitioner ON Student_Service_Header.pract_id = Practitioner.pract_id
  JOIN District ON Student_Service_Header.district_id = District.district_id
WHERE Practitioner.account = 
(
  SELECT account 
  FROM Practitioner 
  WHERE pract_id = '$pract_id'
) $sqlqual 
  AND
  (
    minDOS IS NULL 
    OR 
    (
      minDOS < 
      (
    SELECT end_date 
    FROM School_Year 
    WHERE year_id = '$year'
      ) 
      AND minDOS >= 
      (
    SELECT start_date 
    FROM School_Year 
    WHERE year_id = '$year'
      )
    ) 
    OR 
    (
      maxDOS < 
      (
    SELECT end_date 
    FROM School_Year 
    WHERE year_id = '$year'
      ) 
      AND maxDOS >= 
      (
    SELECT start_date 
    FROM School_Year 
    WHERE year_id = '$year'
      )
    )
  )
ORDER BY sub, lname, fname, hid DESC

As the database has grown, it has become exceeding slow. I have discovered that one of the views I am using runs in 4 seconds when I just run the query, but the view that runs the same query is taking 11 seconds. I would like to incorporate the functionality from the view into the query, and am looking for help.
The vs_Service_Details does this:

Code :

1
2
3
4
5
6
7
8
SELECT `vw_Details`.`header_id` AS `header_id`,
  max(`vw_Details`.`lastupd`) AS `upd`,
  max(`vw_Details`.`transferdt`) AS `transdt`,
  max(`vw_Details`.`submit`) AS `sub`,
  max(`vw_Details`.`DOS`) AS `maxDOS`,
  min(`vw_Details`.`DOS`) AS `minDOS` 
FROM `vw_Details` 
GROUP BY `vw_Details`.`header_id`

And, the vw_Details does this:

Code :

1
2
3
4
5
6
SELECT `Student_Service_PCAR`.`header_id` AS `header_id`,
  str_to_date(`Student_Service_PCAR`.`date_of_service`,_utf8'%m/%d/%Y') AS `DOS`,
  date_format(`Student_Service_PCAR`.`last_update`,_utf8'%m/%d/%Y') AS `lastupd`,
  `Student_Service_PCAR`.`transfer_date` AS `transferdt`,
  `Student_Service_PCAR`.`submit_yn` AS `submit` 
FROM `Student_Service_PCAR`

[Aurélien LEQUOY]

Déjà je retirerai les requêtes imbriqués. après il faudrait que tu nous donnes la définition des tables / indexes / FK avec un échantillon de données

[CinePhil]

Ouch ! Elle font mal aux yeux tes requêtes !

Généralités
1) Utilise des alias pour les tables, ça rend l'écriture et la lecture de la requête plus facile.

2) Les apostrophes inversées autour des noms de tables et de colonnes sont inutiles tant que ces noms ne sont pas des mots du langage SQL ou ne comprennent ni espace, ni caractère diacritique, ni tiret.

Vue vw_Details
1) Pourquoi y a t-il un STR_TO_DATE et un DATE_FORMAT qui sont deux fonctions inverses ?
Student_Service_PCAR.date_of_service est converti en format standard DATE 'aaaa-mm-jj' à partir d'une chaîne de caractères au format 'mm/jj/aaaa' alors que Student_Service_PCAR.last_update est converti au format 'mm/jj/aaaa' à partir du format standard de date.
Quant à la colonne Student_Service_PCAR.transfer_date, elle n'est pas convertie !
Bizarre non ?
De quel type sont ces trois colonnes ?
Est ce le même type dans les deux tables Student_Service et Student_Service_PCAR ?

J'aurais tendance à enlever ces formatages bizarres de cette vue, ce qui la simplifiera et rendra son exécution plus rapide.

2) Au niveau modèle de données, pourquoi y a t-il deux tables avec apparemment la même structure ?

Première requête
1) Tu écris cette restriction :

Code :

1
2
3
4
5
6
WHERE Practitioner.account = 
(
  SELECT account 
  FROM Practitioner 
  WHERE pract_id = '$pract_id'
)

Traduction en français :
" où l'acompte de practitionner est égal à l'accompte de practitionner pour le pract_id envoyé par le programme. "

Il me semble que ceci serait suffisant :

Code :

WHERE pract_id = $pract_id

Au passage, comme l'identifiant est en principe de type entier, inutile de mettre la valeur souhaitée entre apostrophes.

2) Juste après cette première restriction se trouve une variable $sqlqual.
Que contient-elle et que vient-elle faire là ?

4) Au lieu de

Code :

a < date_fin AND a >= date_debut

il vaut mieux utiliser BETWEEN en tenant compte du fait que les bornes sont incluses.

Voici la requête récrite :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SELECT s.Last_Name AS lname,
  s.First_Name AS fname,
  h.header_id AS hid, 
  h.pract_id AS pid,
  sd.upd,
  h.medicaid_id AS medid,
  h.service_area AS svcarea, 
  sd.sub,
  h.district_id AS did,
  p.first_name AS practfn, 
  p.last_name AS practln,
  p.account AS acct, 
  sd.transdt,
  h.QC_status AS sts, 
  h.QC_comment AS cmt,
  d.district_name AS dname
FROM Student s
INNER JOIN Student_Service_Header h
    ON s.medicaid_id = h.medicaid_id 
    AND s.district_id = h.district_id
  INNER JOIN Practitioner p ON h.pract_id = p.pract_id
  INNER JOIN District d ON h.district_id = d.district_id
  INNER JOIN vw_Service_Details sd ON h.header_id = sd.header_id
WHERE p.pract_id = $pract_id
  $sqlqual -- que contient cette variable ?
  AND
  (
    sd.minDOS IS NULL
    OR sd.minDOS BETWEEN 
      (
    SELECT start_date
    FROM School_year
    WHERE year_id = $year
      ) 
      AND
      DATE_SUB( 
    (  
      SELECT end_date
      FROM School_year
      WHERE year_id = $year
    )
    , INTERVAL 1 DAY
      )
    OR sd.maxDOS BETWEEN 
      (
    SELECT start_date
    FROM School_year
    WHERE year_id = $year
      ) 
      AND
      DATE_SUB( 
    (  
      SELECT end_date
      FROM School_year
      WHERE year_id = $year
    )
    , INTERVAL 1 DAY
      )
    )
ORDER BY sd.sub, s.Last_Name, s.First_Name

vw_Service_Details étant une vue de calcul/regroupement basée sur une vue d'union, je ne pense pas que d'inclure leurs requêtes améliorerait les choses.

La première des choses à vérifier lorsqu'on rencontre des problèmes de rapidité d'exécution est l'indexation des tables. Toutes les colonnes figurant dans les conditions de jointure doivent être indexées, ainsi que les colonnes figurant dans les clauses du WHERE.

Ce qui peut ralentir aussi, c'est le fait que l'ORDER BY soit basé en premier sur une colonne issue de la vue et donc non indexée.

Un index sur s.Last_Name, s.First_Name pourrait peut-être améliorer légèrement les choses mais comme ce ne sont pas les premières colonnes du tri, c'est pas sûr que ce soit très sensible.

Quel est le volume de données à traiter par cette requête ?