Délais php - requete sql

**Sparky95** · 17/02/2020, 04h11

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

**rawsrc** · 17/02/2020, 09h00

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

**Sparky95** · 17/02/2020, 09h47

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

**rawsrc** · 17/02/2020, 10h01

Ok et question index sur la table ça dit quoi ?
Poste voir la DDL

**Sparky95** · 17/02/2020, 10h02

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.

**rawsrc** · 17/02/2020, 10h20

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.

**Sparky95** · 17/02/2020, 10h22

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

**rawsrc** · 17/02/2020, 10h40

surtout que MyISAM a été abandonné depuis un bout de temps, c'est InnoDB qui le remplace

**Sparky95** · 17/02/2020, 21h02

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

**rawsrc** · 17/02/2020, 21h18

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.

**Sparky95** · 17/02/2020, 21h26

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

**rawsrc** · 17/02/2020, 21h43

ç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

**Sparky95** · 17/02/2020, 22h15

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

**grunk** · 18/02/2020, 08h44

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

**armel18** · 18/02/2020, 11h49

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.

**Sparky95** · 21/02/2020, 23h40

@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

**armel18** · 22/02/2020, 11h02

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
  ";

**Sparky95** · 22/02/2020, 15h15

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.

Invité · 22/02/2020, 15h38

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
...

**Sparky95** · 22/02/2020, 16h18

@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?

Délais php - requete sql

PHP & Base de données

Discussions similaires

Partager

Partager