IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Firebird Discussion :

Améliorer la performance d'une requête


Sujet :

SQL Firebird

  1. #1
    Membre régulier
    Homme Profil pro
    Développeur informatique
    Inscrit en
    mars 2010
    Messages
    842
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : mars 2010
    Messages : 842
    Points : 87
    Points
    87
    Par défaut Améliorer la performance d'une requête
    Bonjour à tous,

    J'ai cette procedure stockée
    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
     
    CREATE PROCEDURE REDCAL (
        CAS1 Char(1),
        NUM1 Varchar(5),
        CHE Varchar(255) )
    AS
    DECLARE VARIABLE i integer;
    DECLARE VARIABLE NUM Varchar(5); 
    DECLARE VARIABLE ANA Varchar(20); 
    DECLARE VARIABLE DES Varchar(255); 
    BEGIN
     I= gen_id(GENRED,-gen_id(GENRED,0));
    delete  from RED where (cas=2) or(cas=3);
    if (:cas1<>'2') then
    begin
    insert into red (cas,dat,clt,fou,rec,dep,des)
    with c as (
    select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select first 1 desf from ach a where (a.ANA=p.ANA)) as des  from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0))
    union all 
    select 2 as cas,DAT,ANA as clt,null as fou,VER as rec,null as dep,(select first 1 descl from ven v where (v.ANA=p.ANA)) as des from PAC P where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0)) 
    ) select * from c;
     end
     else
     begin
          insert into red (cas,dat,clt,fou,rec,dep,des)
            with c as (
                select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select first 1 desf from ach a where (a.ANA=p.ANA)) as des  from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0))
                union all 
           select 2 as cas,dat,'00000' as clt,null as fou,sum(mht) as rec,null as dep,'VENTE JOURNEE ' as des from ven group by dat)
           select * from c;
     end     
     
     
    SUSPEND;
    END
    et voci la strcutres des tables utilisées
    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
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
     
    -- table Achat:
    CREATE TABLE ACH
    (
      CAS Integer NOT NULL,
      DAT Date NOT NULL,
      FAC Varchar(30) NOT NULL,
      ORD Integer NOT NULL,
      ANA Varchar(20) CHARACTER SET ISO8859_1,
      MAT Varchar(20) CHARACTER SET ISO8859_1,
      PRO Varchar(20) CHARACTER SET ISO8859_1,
      DES Varchar(255) CHARACTER SET ISO8859_1,
      QTE Numeric(14,5),
      MHT Numeric(14,2),
      COI Integer,
      PER Integer,
      DAP Date,
      CHA Varchar(255) CHARACTER SET ISO8859_1,
      IMA Varchar(35) CHARACTER SET ISO8859_1,
      MON Numeric(14,2),
      OBS Varchar(255) CHARACTER SET ISO8859_1,
      PUA Numeric(14,5),
      COIR Integer,
      DESF Varchar(200) CHARACTER SET ISO8859_1,
      MTB Numeric(14,5),
      TAU Numeric(14,2),
      TVA Numeric(14,5),
      TRE Numeric(14,2),
      REM Numeric(14,5),
      UTI Varchar(50),
      COID Integer,
      COIF Integer,
      PUDEV Numeric(14,6),
      TAUXC Numeric(14,6),
      FRATRANS Numeric(14,6),
      FRADOU Numeric(14,6),
      FRAMAN Numeric(14,6),
      FRAAUT Numeric(14,6),
      DEVISE Varchar(15),
      FRATRANSP Numeric(14,6),
      PRIMARY KEY (CAS,DAT,FAC,ORD)
    );
     
    -- Table VEN
    CREATE TABLE VEN
    (
      DAT Date NOT NULL,
      BON Varchar(20) CHARACTER SET ISO8859_1 NOT NULL,
      ORD Integer NOT NULL,
      ANA Varchar(20) CHARACTER SET ISO8859_1,
      PRO Varchar(20) CHARACTER SET ISO8859_1,
      DES Varchar(255) CHARACTER SET ISO8859_1,
      QTE Numeric(14,5),
      PUV Numeric(18,6),
      MHT Numeric(14,2),
      COI Smallint,
      CDR Numeric(14,5),
      BEN Numeric(14,2),
      FAC Varchar(20) CHARACTER SET ISO8859_1,
      HEU Timestamp,
      DEN Numeric(14,5),
      OBS Varchar(255) CHARACTER SET ISO8859_1,
      UME Varchar(30),
      ED1 Numeric(14,4),
      ED2 Numeric(14,4),
      ED3 Numeric(14,4),
      QTP Numeric(14,5),
      PUA Numeric(14,5),
      QTD Numeric(14,5),
      QTR Numeric(14,5),
      DAP Date,
      COIR Integer,
      DESCL Varchar(200) CHARACTER SET ISO8859_1,
      CHA Varchar(30),
      IMA Varchar(15),
      MTB Numeric(14,5),
      TAU Numeric(14,2),
      TVA Numeric(14,5),
      TRE Numeric(14,2),
      REM Numeric(14,5),
      UTI Varchar(50),
      COID Integer,
      COIF Integer,
      MHTR Numeric(18,6),
      CLTLIV Char(1),
      CPUV Char(1),
      PRIMARY KEY (DAT,BON,ORD)
    );
     
    -- Table PAF
    CREATE TABLE PAF
    (
      CAS Integer NOT NULL,
      DAT Date NOT NULL,
      ANA Varchar(20) NOT NULL,
      FAC Varchar(20) NOT NULL,
      ORD Integer NOT NULL,
      DEP Numeric(14,2),
      MON Numeric(14,2),
      VER Numeric(14,2),
      RES Numeric(14,2),
      DES Varchar(255) CHARACTER SET ISO8859_1,
      MOP Varchar(30) CHARACTER SET ISO8859_1,
      DAC Date,
      MTB Numeric(14,2),
      TVA Numeric(14,5),
      REM Numeric(14,5),
      TIM Numeric(14,5),
      PRIMARY KEY (CAS,DAT,ANA,FAC,ORD)
    );
    -- Table PAC
    CREATE TABLE PAC
    (
      CAS Integer NOT NULL,
      DAT Date NOT NULL,
      ANA Varchar(20) NOT NULL,
      FAC Varchar(20) NOT NULL,
      ORD Integer NOT NULL,
      DEP Numeric(14,2),
      MON Numeric(14,2),
      VER Numeric(14,2),
      RES Numeric(14,2),
      DES Varchar(255) CHARACTER SET ISO8859_1,
      MOP Varchar(30) CHARACTER SET ISO8859_1,
      DAC Date,
      MTB Numeric(14,2),
      TVA Numeric(14,5),
      REM Numeric(14,5),
      TIM Numeric(14,5),
      BEN Numeric(14,2),
      PRIMARY KEY (CAS,DAT,ANA,FAC,ORD)
    );
    Mon problème c'est que lorsque les tables deviennent volumineuses l’exécution de cette procédure prend beaucoup de temps presque 1 minute. Et je voudrais pouvoir réduire ce temps, peut être en modifiant la procédure.

  2. #2
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    12 707
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 64
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 12 707
    Points : 34 290
    Points
    34 290
    Billets dans le blog
    52
    Par défaut
    Bonjour,

    Déjà en enlevant le suspend qui ne sert à rien puisqu'il n'y a pas de retour de données.

    Ensuite AMHA il vaut mieux faire deux inserts plutôt que d'utiliser une CTE
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    insert into red (cas,dat,clt,fou,rec,dep,des) select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select first 1 desf from ach a where (a.ANA=p.ANA)) as des  from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0));
    insert into red (cas,dat,clt,fou,rec,dep,des)  select 2 as cas,DAT,ANA as clt,null as fou,VER as rec,null as dep,(select first 1 descl from ven v where (v.ANA=p.ANA)) as des from PAC P where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0));
    ensuite ces SELECT First 1 sans clause ORDER BY me laissent dubitatif.
    1- il faut ajouter des indexs sur PAF et PAC
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
      CREATE INDEX IX_PAFANA ON TABLE PAF(ANA);
      CREATE INDEX IX_PACANA ON TABLE PAC(ANA);
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  3. #3
    Membre régulier
    Homme Profil pro
    Développeur informatique
    Inscrit en
    mars 2010
    Messages
    842
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : mars 2010
    Messages : 842
    Points : 87
    Points
    87
    Par défaut
    CREATE INDEX IX_PAFANA ON PAF(ANA);
    CREATE INDEX IX_PACANA ON PAC(ANA);
    merci je vai voir avec votre proposition

  4. #4
    Membre régulier
    Homme Profil pro
    Développeur informatique
    Inscrit en
    mars 2010
    Messages
    842
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : mars 2010
    Messages : 842
    Points : 87
    Points
    87
    Par défaut
    Bonjour,

    J'ai indexé les 2 tables mais toujours pareil , le temps de l’exécution de la requête reste toujours élevé.

    ensuite ces SELECT First 1 sans clause ORDER BY me laissent dubitatif.
    c'est la conception des tables qui veut ça, le champs commun est "ana", et j'ai besoin des "desf" qui correspond à la désignation du champs "ana".

  5. #5
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    12 707
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 64
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 12 707
    Points : 34 290
    Points
    34 290
    Billets dans le blog
    52
    Par défaut
    Citation Envoyé par chekkal Voir le message
    c'est la conception des tables qui veut ça
    Ce qui ne veut pas dire que cette conception soit bonne. Quand je vois des clés primaires composées je doute, et quand il y a besoin d'un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    select first 1 desf from ach a where (a.ANA=p.ANA)
    , qui plus est sans clause ORDER BY, c'est qu'il y a des redondances.
    Créez un table temporaire (ANA,DESF PK ANA) ON COMMIT DELETE ROWS pourrait être une solution, même s'il me semble que les index sur ces tables ne sont pas forcément pris en compte
    Remplir la table temporaire par un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    INSERT INTO SELECT ANA,DESF FROM  ... WHERE  ... GROUP BY ANA;
    et faite une jointure pour vos reqêtes

    Mais j'ai quand même un gros doute, déjà exprimé, sur ce first.
    Si ANA correspond à une seule désignation, il y aurait dû y avoir un table d'une structure minimale* ANA(CODE,DESIGNATION PK ANA) les tables utilisant ce code ayant des contraintes de référence.
    Cela sent une migration PARADOX où les requêtes de jointures étaient inconnues.

    *minimale car une meilleure structure aurait avant tout une colonne auto incrémentée servant d'index primaire, le code étant utilisé comme index unique
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  6. #6
    Membre régulier
    Homme Profil pro
    Développeur informatique
    Inscrit en
    mars 2010
    Messages
    842
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : mars 2010
    Messages : 842
    Points : 87
    Points
    87
    Par défaut
    Si ANA correspond à une seule désignation, il y aurait dû y avoir un table d'une structure minimale* ANA(CODE,DESIGNATION PK ANA) les tables utilisant ce code ayant des contraintes de référence.
    Tout à fait , il ya bien une table analy(ana,designation) mais il se trouve sur une autre base de données.

  7. #7
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    12 707
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 64
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 12 707
    Points : 34 290
    Points
    34 290
    Billets dans le blog
    52
    Par défaut
    Ça doit être coton pour créer des contraintes ! C'est
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  8. #8
    Membre régulier
    Homme Profil pro
    Développeur informatique
    Inscrit en
    mars 2010
    Messages
    842
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : mars 2010
    Messages : 842
    Points : 87
    Points
    87
    Par défaut
    Bonjour,

    Je l'ai récupéré comme ça

  9. #9
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    12 707
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 64
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 12 707
    Points : 34 290
    Points
    34 290
    Billets dans le blog
    52
    Par défaut
    Essayez avec la table temporaire
    une fois (par bases)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    CREATE GLOBAL TEMPORARY TABLE analy(ana VARCHAR(20) NOT NULL PRIMARY KEY,designation VARCHAR(255))
    ON COMMIT DELETE ROWS;
    procédure pour tester
    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
    SET TERM ^ ;
     
    CREATE PROCEDURE TESTANALY(BASEREF VARCHAR(30)) 
    RETURNS 
     ( A VARCHAR(25),
       D VARCHAR(255))
    AS 
    BEGIN
     FOR EXECUTE STATEMENT 'SELECT ANA,DESIGNATION FROM ANALY' 
       ON EXTERNAL :BASEREF INTO :A,:D  
     DO INSERT INTO ANALY(ANA,DESIGNATION) VALUES (:A,:D);
     FOR SELECT ANA,DESIGNATION FROM ANALY INTO :A,:D 
       DO SUSPEND;
    END^
     
    SET TERM ; ^
    dans votre procédure, aprés bien sûr avoir mis en debut de procedure, et déclaré A et D
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     FOR EXECUTE STATEMENT 'SELECT ANA,DESIGNATION FROM ANALY' 
       ON EXTERNAL :BASEREF INTO :A,:D  
     DO INSERT INTO ANALY(ANA,DESIGNATION) VALUES (:A,:D);
    l'appel du (select first ...) sera remplacé par ,(select designation from ANALY where ANA= p.ANA)exemple
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    insert into red (cas,dat,clt,fou,rec,dep,des) select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select designation from ANALY where ANA=p.ANA) from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0));
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  10. #10
    Membre régulier
    Homme Profil pro
    Développeur informatique
    Inscrit en
    mars 2010
    Messages
    842
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : mars 2010
    Messages : 842
    Points : 87
    Points
    87
    Par défaut
    pour la création de la table temporaire il me signale erreur
    Nom : Untitled.png
Affichages : 27
Taille : 98,1 Ko

    en plus comment doit-je créer la table temporaire, est-ce à l'intérieur de la procédure?

  11. #11
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    12 707
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 64
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 12 707
    Points : 34 290
    Points
    34 290
    Billets dans le blog
    52
    Par défaut
    Ah oui, j'aurais du signaler que les tables temporaires ne supportent les index qu'à partir de la version 3 ? Mais avez vous indiqué votre version de Firebird en début de discussion !

    en plus comment doit-je créer la table temporaire, est-ce à l'intérieur de la procédure?
    c'est affligeant, vous osez poser cette question. renseignez vous sur ce qu'est une table temporaire.
    Moi j'abandonne le sujet
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

Discussions similaires

  1. Réponses: 9
    Dernier message: 16/10/2020, 15h50
  2. Réponses: 13
    Dernier message: 11/02/2010, 14h00
  3. Améliorer les performances d'une requête avec agrégats
    Par alexis_ dans le forum Requêtes
    Réponses: 4
    Dernier message: 05/11/2009, 22h31
  4. Améliorer la performance d'une requête
    Par BorisShem dans le forum Requêtes et SQL.
    Réponses: 9
    Dernier message: 03/04/2009, 22h47
  5. [SQL ] performances dans une requête
    Par claralavraie dans le forum Oracle
    Réponses: 12
    Dernier message: 05/01/2006, 17h54

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo