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

PHP & Base de données Discussion :

Délais php - requete sql


Sujet :

PHP & Base de données

  1. #1
    Membre actif
    Délais php - requete sql
    Bonjour,
    j'ai une bonne grosse requête et une bonne grosse table.
    j'essayes d'optimiser évidement tout cela.

    Ma requete:
    via phpmyadmin je mets 0.0003 sec
    pourtant via php je mets plus de 30 sec.

    Pourquoi?

    voici mon code de test
    Code PHP :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    $sql = "SELECT ana_liste.nom_action,ana_marge.suffixe,ana_marge.date_num,STR_TO_DATE(ana_marge.date_date,'%d/%m/%Y'),ana_marge.sens,ana_marge.marge,ana_marge.jrs_posit,ana_marge.ma_jrsp,ana_tende.dwphase_cac,ana_tende.dwprofil_cac,ana_tende.posbdfg_cac,ana_tende.posd200_cac,ana_tende.posd100_cac,ana_tende.posd050_cac,ana_tende.postdli_cac,ana_tende.poskal_cac,ana_tende.dwpente_cac,ana_tende.dwptcac_max,ana_tende.dwptcac_min,ana_tende.dwphase_act,ana_tende.dwprofil_act,ana_tende.posbdfg_act,ana_tende.posd200_act,ana_tende.posd100_act,ana_tende.posd050_act,ana_tende.postdli_act,ana_tende.poskal_act,ana_tende.dwpente_act,ana_tende.dwptact_max,ana_tende.dwptact_min,ana_tende.ifr_act,ana_tende.ifr_act_max,ana_tende.ifr_act_min FROM ana_marge INNER JOIN ana_liste ON ana_marge.suffixe = ana_liste.suffixe INNER JOIN ana_tende ON ana_marge.suffixe = ana_tende.suffixe AND ana_marge.date_num = ana_tende.date_num WHERE ana_marge.sens = 'ACH' AND ana_marge.ref_cfg = '' AND SUBSTR(ana_marge.cas,1,1) <> 'S'";
    $db = new PDO('mysql:host=localhost;dbname=anatec', 'admin', 'admin');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // testé avec et sans n'a pas d'influance sur la durée
    //envoie de la requete
    $req = $db->query($sql);
    //$db->query($sql); //même sans la stocker cela prends autant de temps
    $db = null;
    echo "<br/>durée: ".(microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]); // entre 30 - 40 sec à chaque fois


    Pour la requête comme vous pouvez le voir, j'ai supprimé quasi tout les espaces et l'ai mis sur une ligne pour optimiser tout cela

    Config:
    mysql + apache + phpmyadmin sur le même serveur
    tests:
    via un client sur le même réseau local

    si vous avez des idées/questions n'hésitez pas

    Merci d'avance

  2. #2
    Modérateur

    salut,

    ça ne sert strictement à rien de condenser ton code à part perdre toute lisibilité.
    Vu que tu annonces un temps d'exécution très court en direct via l'admin, il n'y a pas de raison que cela soit des interminable via PHP.
    Il faudrait que tu chronomètres séparément l'ouverture de la connexion new PDO() puis le $db->query() et reviens après
    # Dans la Création, tout est permis mais tout n'est pas utile...

  3. #3
    Membre actif
    Bonjour,
    Effectivement je n'ai pas pensé à les chronométrer individuellement

    voici le code donc ré indenté avec les chrono
    Code PHP :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
    $time_start_loading = microtime(true);
     
    $sql = "SELECT
                    ana_liste.nom_action, ana_marge.suffixe, ana_marge.date_num, STR_TO_DATE(ana_marge.date_date, '%d/%m/%Y'), ana_marge.sens, ana_marge.marge, ana_marge.jrs_posit, ana_marge.ma_jrsp,
                    ana_tende.dwphase_cac, ana_tende.dwprofil_cac, ana_tende.posbdfg_cac, ana_tende.posd200_cac, ana_tende.posd100_cac, ana_tende.posd050_cac, ana_tende.postdli_cac, ana_tende.poskal_cac, ana_tende.dwpente_cac, ana_tende.dwptcac_max, ana_tende.dwptcac_min, ana_tende.dwphase_act, ana_tende.dwprofil_act, ana_tende.posbdfg_act, ana_tende.posd200_act, ana_tende.posd100_act, ana_tende.posd050_act, ana_tende.postdli_act, ana_tende.poskal_act, ana_tende.dwpente_act, ana_tende.dwptact_max, ana_tende.dwptact_min, ana_tende.ifr_act, ana_tende.ifr_act_max, ana_tende.ifr_act_min
                    FROM ana_marge
                    INNER JOIN ana_liste ON ana_marge.suffixe = ana_liste.suffixe
                    INNER JOIN ana_tende ON ana_marge.suffixe = ana_tende.suffixe
                        AND ana_marge.date_num = ana_tende.date_num
                    WHERE ana_marge.sens = 'ACH'
                        AND ana_marge.ref_cfg = ''
                        AND SUBSTR(ana_marge.cas,1,1) <> 'S'";
     
    $time_start_connexion = microtime(true);
    $db = new PDO('mysql:host=192.168.0.23;dbname=anatec', 'admin', 'admin');
    $time_end_connexion = microtime(true);
     
    //envoie de la request
    $time_start_request = microtime(true);
    $req = $db->query($sql);
    $time_end_request = microtime(true);
     
    $db = null;
     
    $time_connexion = $time_end_connexion - $time_start_connexion;
    $time_request = $time_end_request - $time_start_request;
    echo "connexion time: ".$time_connexion."<br/>";
    echo "request time: ".$time_request."<br/>";
     
     
    $time_end_loading = microtime(true);
    $time_loading = $time_end_loading - $time_start_loading;
    echo "loading time: ".$time_loading;

    Voici les résultats
    connexion time: 0.0012328624725342
    request time: 34.754498958588
    loading time: 34.75576210022

    Et voici executé coté serveur en ligne de commande pour exclure l’influence apache au cas ou
    connexion time: 0.0023901462554932<br/>
    request time: 37.092872142792<br/>
    loading time: 37.104971885681

  4. #4
    Modérateur

    Ok et question index sur la table ça dit quoi ?
    Poste voir la DDL
    # Dans la Création, tout est permis mais tout n'est pas utile...

  5. #5
    Membre actif
    La je n'ai pas vraiment compris ce que tu demandes / quoi faire
    Si tu me demandes si j'ai fais des index? => non

    Ajout:
    actuellement je suis occupé de retravailler toute la base je n'ai pas encore fais ces index c'est une table que j'ai repris hier et je suis occupé justement de lui optimiser.
    Ici je cherchais à optimiser les temps de réponse par requête en changeant déjà le type des données après je pensais commencer à lier ces tables par leurs index.

  6. #6
    Modérateur

    l'optimisation passe principalement par le mise en place des index qui vont bien sur tes tables.
    Tant que tu n'as pas d'index, ça ne sert à rien de regarder les temps d'exécution des requêtes.
    # Dans la Création, tout est permis mais tout n'est pas utile...

  7. #7
    Membre actif
    justement au niveau des index j'ai un petit soucis
    sur ce site, http://www.up2.fr/M1/td/foreignkey.html, il est conseillé de changer la base de donnée en InnoDB.
    Actuellement j'ai la moitié en MyISAM et l'autre en InnoDB.
    Le soucis est que dans opérations il n'y à pas l'option indiqué pour changer le moteur de stockage.

    Ajout: j'ai fini par le fare en ligne de commande c'est plus rapide

  8. #8
    Modérateur

    surtout que MyISAM a été abandonné depuis un bout de temps, c'est InnoDB qui le remplace
    # Dans la Création, tout est permis mais tout n'est pas utile...

  9. #9
    Membre actif
    Bonsoir,
    Désolé pour le délais j'ai eu plusieurs soucis de corruptions de données via phpmyadmin (visiblement assez courent).
    Je suis donc occupé d'ajouter toutes les clés étrangères mais j'obtiens des erreurs pour les quels je n'ai pas encore réussi à résoudre le problème
    ALTER TABLE `ana_condi` ADD CONSTRAINT `ana_condi$date_num$date$fk` FOREIGN KEY (`date_num`) REFERENCES `date`(`date_num`) ON DELETE RESTRICT ON UPDATE RESTRICT;.
    voici l'erreur que j'obtiens
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`anatec`.`#sql-c63_898`, CONSTRAINT `ana_condi$date_num$date$fk` FOREIGN KEY (`date_num`) REFERENCES `date` (`date_num`))
    et j'ai beau chercher je ne parviens pas à résoudre mon soucis

  10. #10
    Modérateur

    si je comprends bien, tu essaies de créer tes contraintes d'intégrité une fois des données présentes dans tes tables !
    C'est chaud ça. Tu devrais TRUNCATE toutes tes tables après avoir fait un dump de ta base et recréer les contraintes entre les tables une fois celles-ci purgées.
    # Dans la Création, tout est permis mais tout n'est pas utile...

  11. #11
    Membre actif
    Pour être honnête il y a 1 051 888 données et ça m'aurait arrangé de pouvoir faire cela sans supprimer puis réinsérer toutes les données.
    Après c'est la premiere fois que je travail sur une table contenant déjà les données d'habite je crée mes tables puis importes les données hahaha.
    De plus si j'ai agis comme ça, c'était pour être sur que les type des tables soit compatibles avec les données,...

    Ajout et la je n'ai plus qu'a faire les clé étrangères tout le reste a déjà été fait et c'est sur ces fameuses clé que ça coince

  12. #12
    Modérateur

    ça veut tout simplement dire que tes jeux de données contiennent des incohérences si les contraintes d'intégrité bloquent à posteriori
    Donc y a du boulot
    # Dans la Création, tout est permis mais tout n'est pas utile...

  13. #13
    Membre actif
    oui je m'en suis rendu compte j'avais fini par procéder comme tu m'a conseillé parcontre phpmyadmin m'a produit une erreur et m'a carrément supprimé une table et la j'ai une contrainte dans le vide qui m'empeche de recréer ma table youhou un bug en plus a résoudre

  14. #14
    Modérateur

    Pour revenir sur la problématique d'origine le problème serait pas que ta requête retourne énormément de données ?
    PHPmyadmin pagine par défaut et donc te retourne 25 résultats alors que ta requêtes PHP retourne surement plusieurs milliers ce qui explique la différence
    Pry Framework php5 | N'oubliez pas de consulter les FAQ Java et les cours et tutoriels Java

  15. #15
    Membre éprouvé
    pour faire suite à l'interrogation soulevée précédemment par @grunk, il te faut exécuter la requête SQL sans pagination( sans clause WHERE ) dans une console MYSQL par exemple du coup t'auras une idée exacte du temps d'exécution de ta requête.
    Après exporté des données à forte volumétrie via PhPMyAdmin ça peut être aussi limite quant à la directive PHP max_execution_time limitant le temps d'exécution du script PHP en l’occurrence PhPMyAdmin.Et un outil comme mysqldump ne serait non pas un luxe pour effectuer une sauvegarde de la BD.

  16. #16
    Membre actif
    @grunk effectivement j'avais zappé ce "détaille"
    @armel18 je vais voir également par rapport à cela

    Bonjour,
    Voila j'ai retravaillé quasi toute la base de donnée et j'ai donc du bien-sur retravailler la requête.
    Voici ce que je fais
    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
    <?php
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);
    $time_start_loading = microtime(true);
     
    $sql = "SELECT
            #from ana_liste
            B.suffixe, isin, nom_action
            #from ana_date
            , C.date_num, date_date
            #from ana_marge
            , sens, cas, marge, jrs_posit, ma_jrsp, ref_cfg
            #from ana_cac
            , dwphase_cac, dwprofil_cac, posbdfg_cac, posd200_cac, posd100_cac, posd050_cac, postdli_cac, poskal_cac, dwpente_cac, dwptcac_max, dwptcac_min
            #from ana_tende
            , dwphase_act, dwprofil_act, posbdfg_act, posd200_act, posd100_act, posd050_act, postdli_act, poskal_act, dwpente_act, dwptact_max, dwptact_min, ifr_act, ifr_act_max, ifr_act_min
        FROM ana_marge A
        INNER JOIN ana_liste B
            ON B.suffixe = A.suffixe
        INNER JOIN ana_date C
            ON C.date_num = A.date_num
        INNER JOIN ana_cac D
            ON D.date_num = C.date_num
        INNER JOIN ana_tende E
            ON E.date_num = C.date_num AND E.suffixe = B.suffixe";
     
     
    $time_start_connexion = microtime(true);
    $db = new PDO('mysql:host=192.168.0.23;dbname=anatec', 'admin', 'admin');
    $time_end_connexion = microtime(true);
     
    //envoie de la request
    $time_start_request = microtime(true);
    $req = $db->query($sql);
    $time_end_request = microtime(true);
     
     
    $db = null;
    $time_end_loading = microtime(true);
     
    $time_connexion = $time_end_connexion - $time_start_connexion;
    echo "connexion time: ".$time_connexion."<br/>";
     
     
    $time_request = $time_end_request - $time_start_request;
    echo "request time: ".$time_request."<br/>";
     
     
    $time_loading = $time_end_loading - $time_start_loading;
    echo "loading time: ".$time_loading;
    ?>


    j'obtiens par contre cette erreur:
    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 196552512 bytes) in [B]... on line 34
    cela vous sembles plausible?
    ana_cac 4 409 lignes
    ana_marge 341 237 lignes
    ana_date 4 409 lignes
    ana_liste 98 lignes
    ana_tende 341 237 lignes

    et je suis sensé récupérer du coup 341 273 lignes au total.
    Cela vous sembles donc normal qu'il y au une erreur de taille de mémoire?

    De plus que pensez vous de la requête?
    serrait-il possible de l'optimiser encore?

    Merci d'avance

  17. #17
    Membre éprouvé

    De plus que pensez vous de la requete?
    serrait-il possible de l'optimiser encore?
    Vu le nombre de lignes renvoyées par la requête, l'optimiser n'apportera pas un gain de performance tu seras toujours confronté au problème lié à l'allocation de la taille mémoire.
    341 273 lignes à afficher c'e
    En général on évite d'afficher un nombre considérables de lignes. On a tendance à afficher les lignes par lots en les limitant par la clause LIMIT : c'est le principe de la pagination.
    Par exemple quand tu es sur la page 1, on affiche les 100 1ères lignes, sur la page 2 les lignes allant de 100 à 200...
    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
    $sql = "SELECT
            #from ana_liste
            B.suffixe, isin, nom_action
            #from ana_date
            , C.date_num, date_date
            #from ana_marge
            , sens, cas, marge, jrs_posit, ma_jrsp, ref_cfg
            #from ana_cac
            , dwphase_cac, dwprofil_cac, posbdfg_cac, posd200_cac, posd100_cac, posd050_cac, postdli_cac, poskal_cac, dwpente_cac, dwptcac_max, dwptcac_min
            #from ana_tende
            , dwphase_act, dwprofil_act, posbdfg_act, posd200_act, posd100_act, posd050_act, postdli_act, poskal_act, dwpente_act, dwptact_max, dwptact_min, ifr_act, ifr_act_max, ifr_act_min
        FROM ana_marge A
        INNER JOIN ana_liste B
            ON B.suffixe = A.suffixe
        INNER JOIN ana_date C
            ON C.date_num = A.date_num
        INNER JOIN ana_cac D
            ON D.date_num = C.date_num
        INNER JOIN ana_tende E
            ON E.date_num = C.date_num AND E.suffixe = B.suffixe
       LIMIT 0,100
      ";

  18. #18
    Membre actif
    Bonjour,
    @armel18 Merci pour ta réponse concernant les "limit" j'étais au courent et cela était en projet comme dernière élément avant le propre.
    Ici j'essayais de voir comment optimiser déjà un maximum le tout pour voir combien de temps de prends le chargement de toutes les données et en fonction du délais je pensais par après couper tout cela en petits paquets.
    Après ce que je ne comprends pas, c'est Pourquoi cela fonctionnait (pourquoi j'arrivais à récupérer toutes les données) lorsque la bdd/les tables étaient crades à mort (toutes les données tapées comme ça dans des tables, sans clé,...) Même si au final cela prenait 30 sec.
    Et maintenant que j'ai restructuré la bdd (les liaisons=>clés, doublons,...) on été supprimés, pourquoi est-ce que maintenant je me retrouves avec une taille alloué qui est trop grosse.

    Ce que j'essayes de comprendre c'est avant de découper mes 300k lignes en bloques, pourquoi il dépasses la mémoire alloué.
    Car c'est une bonne idée de couper le bloque de données en petites parties mais c'est encore mieux si le bloque, de base, est moins gros.

  19. #19
    Rédacteur/Modérateur

    Bonjour,

    Tu dis avoir tout restructuré.
    Or :
    Code sql :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
        INNER JOIN ...
            ON B.suffixe = A.suffixe
    ...
            ON C.date_num = A.date_num

    Cela dit, généralement, les JOINTURES de tables se font sur les id (auto-incrémenté).
    Donc, là, j'ai l'impression que tu as construit une grosse usine à gaz.


    Sinon, ces colonnes 'suffixe', 'date_num',... sont-elles correctement indexées ?
    Pour en dire plus, il faudrait la structures et liaisons des tables SQL.

    Code sql :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
           B.suffixe, isin, nom_action
    ...

    Il faut indiquer la table (A., B., ...) pour CHAQUE nom de colonne (pas juste sur le 1er de la ligne !)
    Code sql :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
           B.suffixe, B.isin, B.nom_action
    ...
    "Si tu suis le chemin qui s'appelle « plus tard », tu arriveras à la place qui s'appelle « jamais »."
    François Camille Prévot (1910-1996), instituteur puis Directeur d'école et... mon grand-père.
    "Pose ta question, tu seras idiot une seconde. Ne la pose pas, tu seras idiot toute ta vie."
    Albert Einstein (1879-1955).
    Mes tutos DVP
    Gestion-Affichage de Nouvelles
    Affichage en tableau HTML
    Fonctions de redimensionnement d'images

  20. #20
    Membre actif
    @jreaux62 concernant les inner join.
    J'utilises le suffix comme clé primaire de ana_list et comme clé étrangère pour les autre tables.
    ana_cac est une table de référence qui contiendra les données du cac_40.
    J'au donc lié toutes les tables via le suffixe sauf ana_date qui donc contient la date ou la bourse est ouvert ainsi qu'une id date_num.
    ana_cac ne possède donc que les données liés à cac_40 (suffixe 0)
    ana_cac et ana_tende contiennent donc les même tables. J'ai cependant distingué ana_cac de ana_tende car l'une est référence de l'autre.
    ana_cac d'ailleur possède donc 1 * count(date_num)
    D'ou les liaisons par date_num dans ce cas la.

    Question: une id doit-elle être obligatoirement auto-incrémenté? ne puis-je pas définir moi même l'id(le suffixe) dans ana_liste?

    pour répondre à ton message "Cela dit, généralement, les JOINTURES de tables se font sur les id (auto-incrémenté)." J'ai donc fais la liaison via les clé primaires et étrangères => date_num et suffixe.

    - "Pour en dire plus, il faudrait la structures et liaisons des tables SQL."
    qu'entends tu par la?

    -"Il faut indiquer la table (A., B., ...) pour CHAQUE nom de colonne (pas juste sur le 1er de la ligne !"
    ça je ne savais pas je pensais que je ne devais mettre le nom que des table ou la recherche pouvait poser confusion.
    ex. j'ai mis le nom de table à suffixe car suffixe ce retrouves dans plusieurs tables mais pas à Isin vu qu'il n’apparaît que dans ana_liste.

    Pourrais tu me confirmer que ce que j'ai fais est bon ou suis-je à coté de la plaque?