+ Répondre à la discussion
Affichage des résultats 1 à 10 sur 10
  1. #1
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    novembre 2011
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : novembre 2011
    Messages : 95
    Points : 100
    Points
    100

    Par défaut Problème de performance avec une variable

    Bonjour,

    Je fais face à un problème assez inattendu.

    Voici une partie de code.

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    DECLARE
        v_code      VARCHAR2 (24);
        v_count   NUMBER;
    BEGIN
        v_code := '4.1214';
     
        SELECT   COUNT ( * )
          INTO   v_count
           FROM   customer cu, contract ca, contract_status cgtf
         WHERE       cu.code LIKE v_code
                 AND ca.customer_id = cu.customer_id
                 AND ca.co_id = cgtf.co_id
                 AND ch_status IN ('a', 's');
     
        DBMS_OUTPUT.put_line (v_count);
    END;
    Et une copie de celui-ci, la seule différence se situe dans le fait que je n'utilise pas de variable, mais la valeur en brut dans la requète

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    DECLARE
        v_code      VARCHAR2 (24);
        v_count   NUMBER;
    BEGIN
        v_code := '4.1214';
     
        SELECT   COUNT ( * )
          INTO   v_count
           FROM   customer cu, contract ca, contract_status cgtf
         WHERE       cu.code LIKE '4.1214'
                 AND ca.customer_id = cu.customer_id
                 AND ca.co_id = cgtf.co_id
                 AND ch_status IN ('a', 's');
     
        DBMS_OUTPUT.put_line (v_count);
    END;
    Mon soucis est celui-ci. Pour la première requête, le temps d'exécution est de 119 sec, la seconde : 0.015 seconde.

    Sachant que cette requête est appelée dans une boucle pouvant aller jusque 30.000 codes, la différence de performance est énorme.

    (Notez, j'utilise un LIKE car normalement il y a '%' à la fin, mais je ne l'utilise pas pour l'exemple, le résultat est semblable et n'est donc pas en cause).

    Notez aussi que le type VARCHAR2(24) utilisé pour ma variable v_code est le même que celui défini dans la table Customer pour mon cu.code.

    Qu'est-ce qui peut causer cela? Quelle serait la solution pour garder autant de performance avec une variable?

    Un grand merci d'avance.

    Merci à vous.

  2. #2
    Membre Expert
    Avatar de islamov2000
    Homme Profil pro
    Ingénieur d'études & developpement en informatique
    Inscrit en
    septembre 2007
    Messages
    657
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2007
    Messages : 657
    Points : 1 257
    Points
    1 257

    Par défaut

    ça mesemble un peu bizare;
    il y a une énorme différence.
    je te propose le SQL dynamique.DECLARE
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
        v_code      VARCHAR2 (24);
        v_count   NUMBER;
        la_requete varchar2(500);
    BEGIN
        v_code := '4.1214';
     
    la_requete:='    SELECT   COUNT ( * )'||
           ' FROM   customer cu, contract ca, contract_status cgtf '||
         ' WHERE       cu.code LIKE ''' ||v_code ||''''||
                 ' AND ca.customer_id = cu.customer_id'||
                 ' AND ca.co_id = cgtf.co_id'||
                 ' AND ch_status IN (''a'', ''s'')'||;
     
                 execute immediate la_requete into v_count;
     
        DBMS_OUTPUT.put_line (v_count);
    END;
    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

  3. #3
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    novembre 2011
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : novembre 2011
    Messages : 95
    Points : 100
    Points
    100

    Par défaut

    Ha, super, merci.

    Oui, c'est étrange cette différence pour une requête semblable... :/


    Mais avec l'execute immediate, c'est parfait.

  4. #4
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    novembre 2011
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : novembre 2011
    Messages : 95
    Points : 100
    Points
    100

    Par défaut

    Encore un petit soucis.

    Comme je disais, je dois ajouter un % pour mon LIKE.

    Mais j'obtiens ce message d'erreur :
    Code :
    ORA-00911: invalid character
    Pour simplifier, j'obtiens également l'erreur avec ce code :
    Code :
    1
    2
    3
    4
    5
    6
    DECLARE
    v_test VARCHAR2(500);
    BEGIN
    v_test := 'SELECT * FROM CUSTOMER WHERE CUSTCODE LIKE 4.%';
    EXECUTE IMMEDIATE v_test;
    END;
    Et pas avec ceci :

    Code :
    1
    2
    3
    4
    5
    6
    DECLARE
    v_test VARCHAR2(500);
    BEGIN
    v_test := 'SELECT * FROM CUSTOMER WHERE CUSTCODE LIKE 4.';
    EXECUTE IMMEDIATE v_test;
    END;
    Merci.

  5. #5
    Membre Expert
    Avatar de islamov2000
    Homme Profil pro
    Ingénieur d'études & developpement en informatique
    Inscrit en
    septembre 2007
    Messages
    657
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2007
    Messages : 657
    Points : 1 257
    Points
    1 257

    Par défaut

    quel est letype CUSTCODE
    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

  6. #6
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Ingénieur d'études décisionnel
    Inscrit en
    mai 2002
    Messages
    5 718
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 5 718
    Points : 14 806
    Points
    14 806

    Par défaut

    4. est une valeur numérique. L'interpréteur Oracle peut soit le convertir en chaine pour l'utiliser avec l'opérateur LIKE, soit remplacer l'opérateur LIKE par l'opérateur = puisqu'il n'y a pas de caractère générique à prendre en compte.

    4.% n'est pas compréhensible par l'interpréteur. Il faut dans ce cas déclarer une chaine et donc l'encadrer d'apostrophes : '4.%'.
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  7. #7
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    novembre 2011
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : novembre 2011
    Messages : 95
    Points : 100
    Points
    100

    Par défaut

    C'est du VARCHAR2, effectivement...

    Code :
    1
    2
    3
    4
    5
    6
    DECLARE
    v_test VARCHAR2(500);
    BEGIN
    v_test := 'SELECT * FROM CUSTOMER WHERE CUSTCODE LIKE ''4.%''';
    EXECUTE IMMEDIATE v_test;
    END;
    Hé bien, merci encore.

  8. #8
    Membre Expert
    Avatar de islamov2000
    Homme Profil pro
    Ingénieur d'études & developpement en informatique
    Inscrit en
    septembre 2007
    Messages
    657
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2007
    Messages : 657
    Points : 1 257
    Points
    1 257

    Par défaut

    C'est ce que je voulais te dire.

    Noter que cette procédure a aucune resultat en sortie.

    Afin d'avoir un resultat en sorie tu dois ajouter un INTO comme suite

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    DECLARE
    v_test VARCHAR2(500);
    BEGIN
    v_test := 'SELECT COL1,COL2,..,COLN FROM CUSTOMER WHERE CUSTCODE LIKE ''4.%''';
    EXECUTE IMMEDIATE v_test into variable1,variable2,...,variableN;
     
     
    END;

    A condition que la requete retourne une et seule ligne, et le nombre de variable est de celui de champs ou colonnes projectés dans le select.


    http://sheikyerbouti.developpez.com/execute_immediate/
    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

  9. #9
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    novembre 2011
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : novembre 2011
    Messages : 95
    Points : 100
    Points
    100

    Par défaut

    Oui oui, mais cet exemple était là juste pour la forme. ^^

  10. #10
    Expert Confirmé Sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    4 665
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2007
    Messages : 4 665
    Points : 9 178
    Points
    9 178

    Par défaut

    La requête 1 utilise une variable de liaison pour la clause LIKE pendant que la deuxième requête ne le fait pas. A partir d’Oracle 9 l’optimisation de la première requête implique la mise en route du mécanisme d’introspection de la valeur des variables de liaison (bind variable peeking) pendant l’élaboration du plan d’exécution. Par la suite le plan une fois élaboré reste le même pour toutes les exécutions consécutives et pour n’importe quelle autre valeur de la variable de liaison parce que le texte de la requête ne change plus d’une valeur a une autre.

    La deuxième requête utilise des littéraux codés en dur pour passer les valeurs de la clause LIKE. A l’optimisation de la requête la valeur est connue et le plan est élaboré en conséquence. Mais si la valeur change le texte de la requête change et par conséquence un parsing hard de la requête doit être effectué. Ce type de parsing coûte cher et comme il est un facteur inhibiteur de l’accès concurrent devrait être réduit.

    Il faut également comprendre qu’une même requête peut être exécutée d’une manière performante avec des plans d’exécutions différentes en fonction des valeurs fournis pour les prédicats de la requête. Ca c’est vrai seulement en présence des valeurs non-uniformément distribuées que l’optimiseur peut prendre en compte en présence des histogrammes.

    Pour revenir à votre problématique : vous constatez de temps d’exécution différents en fonction de l’écriture du bloc PL/SQL. Avez-vous analysé et compris qu’elle est l’origine de cette différence ? Avez-vous une explication de ce qui se passe ? Parce que si non c’est comme allez au marabout du coin pour vous faire traiter de douleur de poitrine : il vous donne de l’aille des ses précieuse abeilles pour traiter un infarctus:
    je te propose le SQL dynamique

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •