salut,
Voici ma requête. Je veux l'optimiser pour diminuer le temps d’exécution :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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`