Optimisation d'une requête sql

Version imprimable

Bonjour ,

actuellement je crée une application en entreprise , sur mon serveur local le temps d'affichage de mon interface est rapide car au niveau de ma base postgresql n'y a pas beaucoup de données . à la mise en production de mon application l'interface prend environs 3 min pour être affiché ,car au niveau de ma requête je récupère les données des 12 derniers moi et ya énormément de données en production .
S'il vous plait quelqu'un pourrai m'aider à optimiser ma requête .

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT SUM(tmp.nombre) as somme, tmp.date as date FROM (
(SELECT count(*) as nombre, to_char(datecreation, 'YYYY-MM') as date FROM archive WHERE idann IN (SELECT idann FROM annonce an WHERE (opem, opta) IN (    
    SELECT DISTINCT opem, opta FROM annonce an 
WHERE statut = 'OK' AND dateannonce < date_trunc('month', CURRENT_DATE) 
AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH) 
AND typeoperation = 'REAPTG' AND statut = 'OK' AND dateannonce < date_trunc('month', CURRENT_DATE) 
AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH 
AND opta <> 'APNF00' AND opem <> 'APNF00') 
AND  datecreation > current_date -360 GROUP BY to_char(datecreation, 'YYYY-MM'))
UNION(SELECT count(*) as nombre, to_char(datecreation, 'YYYY-MM') as date FROM archive_longue WHERE idann IN (    
    SELECT idann FROM annonce an WHERE (opem, opta) IN (    
    SELECT DISTINCT opem, opta FROM annonce an WHERE statut = 'OK' 
AND dateannonce < date_trunc('month', CURRENT_DATE) AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH) 
AND typeoperation = 'REAPTG'
 AND statut = 'OK' 
AND dateannonce < date_trunc('month', CURRENT_DATE)
 AND dateannonce > date_trunc('month', CURRENT_DATE -360) - INTERVAL '1' MONTH AND opta <> 'APNF00' AND opem <> 'APNF00') AND  datecreation > current_date -360 GROUP BY to_char(datecreation, 'YYYY-MM'))) as tmp GROUP BY tmp.date ORDER BY 2,1 desc;

Merci

Bonjour,

1) remplacez UNION ALL à la place de UNION (si les deux tables ARCHIVE et archive_longue ne se recoupent pas au niveau des données)
2) DISTINCT ne sert à rien dans une sous requête introduite par IN
3) remplacez le To_char(datecreation, 'YYYY-MM') par l'extraction des années et mois en 2 colonnes de type entier
4) si la colonne Statut ne contient que deux valeurs la remplacer par un booléen
5) remontez le critère d'exclusion (AND opta <> 'APNF00' AND opem <> 'APNF00') dans la sous requête initiale (si c'est possible)
6) utilisez une CTE pour factoriser les sous requêtes identiques (je n'ai pas appliqué toutes les 5 premières recommandations) :
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 WITH TA AS (SELECT opem, opta FROM annonce an WHERE statut = 'OK' AND dateannonce < Date_trunc('month', current_date) AND dateannonce > Date_trunc('month', current_date - 360)), TB AS (SELECT idann FROM annonce an WHERE ( opem, opta ) IN (SELECT opem, opta FROM TA) AND typeoperation = 'REAPTG' AND statut = 'OK' AND dateannonce < Date_trunc('month', current_date) AND dateannonce > Date_trunc('month', current_date - 360) - interval '1' month AND opta <> 'APNF00' AND opem <> 'APNF00'), TC AS (SELECT Count(*) AS nombre, To_char(datecreation, 'YYYY-MM') AS date FROM ARCHIVE WHERE idann IN (SELECT idann FROM TB) AND datecreation > current_date - 360 GROUP BY To_char(datecreation, 'YYYY-MM') UNION ALL SELECT Count(*) AS nombre, To_char(datecreation, 'YYYY-MM') AS date FROM archive_longue WHERE idann IN (SELECT idann FROM TB) AND datecreation > current_date - 360 GROUP BY To_char(datecreation, 'YYYY-MM')) SELECT SUM(tmp.nombre) AS somme, tmp.DATE AS date FROM TC GROUP BY tmp.DATE ORDER BY 2, 1 DESC;
Au niveau du modèle, remplacez la colonne typeoperation par un code_operation associée à une table de références des types d'iopération. De même pour OPTA/OPTEM.

Au niveau admin, créez les index suivants s'ils n'exitent pas déjà :
Code:

1 2 3 CREATE INDEX X1 ON annonce (statut, dateannonce) --ou CREATE INDEX X1 ON annonce (statut, dateannonce, opem, opta)
Code:

CREATE INDEX X2 ON annonce (opem, opta, typeoperation, statut, dateannonce, idann)

Code:

CREATE INDEX X3 ON ARCHIVE (idann, datecreation)

Code:

CREATE INDEX X4 ON ARCHIVE (idann, datecreation)

A +

29/08/2017, 16h02
alassanediakite

Salut
En plus des suggestions de SQLpro, je pense qu'on peut créer une vue matérialisée et l'actualiser au 1er de chaque mois. Mais si des modifications sont permises sur les données antérieurs au mois courant il faut un trigger sur les tables concernées.
@+

Essayez :

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
WITH
TA AS
(SELECT idann
 FROM   annonce an
 WHERE  statut = 'OK'
   AND  dateannonce < Date_trunc('month', current_date)
   AND  dateannonce > Date_trunc('month', current_date - 360)
   AND  opta <> 'APNF00'
   AND  opem <> 'APNF00'
   AND  typeoperation = 'REAPTG'
   AND  statut = 'OK'),
TC AS
(SELECT Count(*)  AS nombre,
        EXTRACT(YEAR FROM datecreation) AS Y, EXTRACT(MONTH FROM datecreation) AS M 
 FROM   ARCHIVE
 WHERE  idann IN (SELECT idann FROM TA)
   AND  datecreation > current_date - 360
 GROUP  BY EXTRACT(YEAR FROM datecreation) AS Y, EXTRACT(MONTH FROM datecreation) AS M 
 UNION ALL
 SELECT Count(*)  AS nombre,
        EXTRACT(YEAR FROM datecreation) AS Y, EXTRACT(MONTH FROM datecreation) AS M 
 FROM   archive_longue
 WHERE  idann IN (SELECT idann FROM TA)
   AND  datecreation > current_date - 360
 GROUP  BY EXTRACT(YEAR FROM datecreation) AS Y, EXTRACT(MONTH FROM datecreation) AS M )
SELECT SUM(tmp.nombre) AS somme,
       Y, M
FROM   TC
GROUP  BY Y, M
ORDER  BY 2, 1 DESC;

A +

29/08/2017, 17h20
web_des

Re

Merci pour vos réponse ,SQLpro votre requête me donne le même résultat que la requête que j'ai posté le seule problème est que la date je l'utilise pour un test au niveau de ma classe java dans ce cas il me fallait l'année et le mois au même temps c'est pour ça que j'ai utilisé le to_char avec le extract(year_month) ne marche pas :(
31/08/2017, 16h24
SQLpro

Mais ça c'est de la cosmétique et on s'en bât les c.... C'est à vous d'adapter votre code java au résultat de la requête et non au SGBDR de faire quelque chose pour lequel il n'est pas fait !!!!

A +