procedure stocke non optimise.

Version imprimable

[RESOLU] procedure stocke non optimise.

Bonjour,

Voici ma procedure stockee, qui ma pris pas mal de temp a develloper et qui me renvoiles information que je veux. Le probleme , c'est que la performance de cette procedure est tres mauvaise et tres longue.

Merci pour vos idee afin de l'optimiser.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DISTINCT dateend,active,Tparts.idpart,TParts.Namepart, 
COALESCE( (SELECT     SUM(nb)  FROM   TEChart   WHERE      TEChart.part = TParts.idpart and qc=0  and DateInsp>=@start and DateInsp<=@end),0)  AS review 
,COALESCE((SELECT     SUM(nb)
                            FROM          TEChart
                            WHERE      TEChart.part = TParts.idpart and TEchart.qc = @concern   and DateInsp>=@start and DateInsp<=@end),0) AS reject,
                     COALESCE
                          ((SELECT     - DATEDIFF(day, GETDATE(), MAX(DateInsp))
                              FROM         TEchart
                              WHERE     TEchart.QC <> 0 AND TEchart.part = TParts.idPart
                              GROUP BY TEchart.part),
                          (SELECT     - DATEDIFF(day, GETDATE(),
                                                       (SELECT     datestart
                                                         FROM          Tproject
                                                         WHERE      idproject= @project)))) AS jour
FROM Tparts,TPartQC
WHERE Tparts.project=@project and TPartQC.qc=@concern  and TPartQC.part=Tparts.idpart 
order by reject DESC,active DESC,review DESC

29/07/2005, 18h10
Fabien Celaia

Pas étonnant avec cette somme de DISTINCT, de GROUP BY et de sous-selects avec agrégats.

Ne pourriez-vous pas essayer de réécrire cela via jointures plutôt que sous-selects imbriqués ? En découpant les processus dans une procédures stockée, par exemple ?

1) identez proprement vos requêtes pour les rendre lisible
2) utilisez le prédicat BETWEEN
3) utilisez des jointures pas du WHERE !
4) utilisez des alias
5) placez des index aux bon endrroits
6) comme le dit fadace, faites des jointures plutôt que des sous requêtes

Votre 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
 
SELECT DISTINCT dateend, active, 
       Tparts.idpart, TParts.Namepart,
       COALESCE((SELECT SUM(nb)  
                 FROM   TEChart   
                 WHERE  TEChart.part = TParts.idpart 
                   and  qc=0  
                   and  DateInsp >= @start and DateInsp <= @end)
                ,0)  AS review,
       COALESCE((SELECT SUM(nb)
                 FROM   TEChart
                 WHERE  TEChart.part = TParts.idpart 
                   and  TEchart.qc = @concern   
                   and DateInsp>=@start and DateInsp<=@end)
                ,0) AS reject,
       COALESCE((SELECT DATEDIFF(day, GETDATE(), MAX(DateInsp))
                 FROM   TEchart
                 WHERE  TEchart.QC <> 0
                   AND  TEchart.part = TParts.idPart
                 GROUP  BY TEchart.part),
      (SELECT DATEDIFF(day, GETDATE(), (SELECT datestart
                                        FROM   Tproject
                                        WHERE  idproject= @project)))) AS jour
 
 
FROM Tparts,
     TPartQC
 
WHERE Tparts.project=@project 
  and TPartQC.qc=@concern  
  and TPartQC.part=Tparts.idpart
 
order by reject DESC, active DESC, review DESC

Voici une autre façon de faire votre requête sans sous reqsuêtes :
(pas garantie car vous n'avez pas repecté ceci : http://www.developpez.net/forums/viewtopic.php?t=32668)

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
 
SELECT DISTINCT dateend, active, 
       PT.idpart, PT.Namepart,
       COALESCE(SUM(TC1.nb), 0) AS review,
       COALESCE(SUM(TC2.nb), 0) AS reject,
       COALESCE(DATEDIFF(day, GETDATE(), MAX(TC3.DateInsp)),
                DATEDIFF(day, GETDATE(), PJ.datestart)) AS jour
 
FROM Tparts PT
     INNER JOIN TPartQC QC
           ON PT.idpart = QC.part
     INNER JOIN TEChart TC1
           ON TC1.part = PT.idpart
     INNER JOIN TEChart TC2
           ON TC2.part = PT.idpart
              AND TC2.qc = QC.qc
     INNER JOIN TEChart TC3
           ON TC3.part = PT.idpart
     INNER JOIN Tproject PJ
           ON PT.project = PJ.idproject
 
WHERE PT.project = @project 
  and QC.qc = @concern  
  and TC1.qc =0
  and TC1.DateInsp BETWEEN @start and  @end
  and TC2.DateInsp BETWEEN @start and  @end
  and TC3.QC <> 0
 
GROUP BY TC3.part, dateend, active, PT.idpart, PT.Namepart, TC3.DateInsp, PJ.datestart
 
order by reject DESC, active DESC, review DESC

Pour ce qui est d'écrire du SQL, mon site comme mon bouquin vous serait sans doute très utile !

A +

Est-ce qu'on ne gagne pas un peu de performance en diminuant le nombre de jointures (pas testé sur des données ...) ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT dateend, active, PT.idpart, PT.Namepart, 
       COALESCE(SUM(CASE WHEN TC.qc =0 
                          AND TC.DateInsp BETWEEN @start and  @end
                         THEN TC.nb
                         ELSE 0 END), 0) AS review, 
       COALESCE(SUM(CASE WHEN TC.DateInsp BETWEEN @start and  @end 
                          AND TC.qc = @concern    
                         THEN TC.nb
                         ELSE 0 END), 0) AS reject, 
       COALESCE(DATEDIFF(day, GETDATE(), MAX(CASE WHEN TC.QC <> 0 
                                                  THEN TC.DateInsp
                                                  ELSE NULL END )) , 
                DATEDIFF(day, GETDATE(), PJ.datestart)) AS jour 
 
FROM Tparts PT INNER JOIN TPartQC  QC ON PT.idpart  = QC.part 
               INNER JOIN TEChart  TC ON TC.part    = PT.idpart 
               INNER JOIN Tproject PJ ON PT.project = PJ.idproject 
WHERE PT.project = @project 
 AND  QC.qc      = @concern
GROUP BY dateend, active, PT.idpart, PT.Namepart, PJ.datestart 
ORDER BY reject DESC, active DESC, review DESC

01/08/2005, 19h00
arnololo

s

MErci beaucoup pour votre aide, vos precieux conseil m'on permis de resoudre mon probleme