Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 16 sur 16
  1. #1
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut Optimiser un calcul

    Bonjour
    J'essaie d'optimiser une requête SQL appelée un grand nombre de fois par un script :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    // Renvoie la distance de la ville de nom $new la plus proche des latitude/longitude de $coo(['lat'],['lon'])
    function Get_Distance ($bdd, $coo, $new)
    {
    	$coo['ville'] = $new;
    	$req = $bdd->prepare ('SELECT 6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon)) D FROM `fr` WHERE :ville LIKE VILLE ORDER BY D ASC LIMIT 1');
    	$req->execute($coo);
    	// Restitution 1ère donnée
    	$out = $req->fetch(PDO::FETCH_ASSOC);
    	$req->closeCursor();
    	return intval($out['D']);
    }
    1- Les coordonnées $coo sont converties en radians par php mais dans la table elles sont en d°
    Pour le 'RADIANS (latitude)' répété 2 fois on ne peut pas écrire 'COS (RADIANS (latitude) AS L)' au milieu du calcul, faut-il le pré-déclarer dans le select même si la valeur n'est pas renvoyée ?

    2- Le résultat est une chaine et non un nombre (la partie entière me suffit d'où le 'intval')

    3- Si c'était un nombre je suppose que MIN pourrait être utilisé à la place de 'ORDER BY D ASC LIMIT 1' (marche pas) ?

    Enfin de façon générale faut-il partir du principe que SQL est plus rapide que php ??

    Merci d'avance

  2. #2
    Membre chevronné
    Homme Profil pro Frédéric
    Inscrit en
    juin 2011
    Messages
    442
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric
    Localisation : France

    Informations forums :
    Inscription : juin 2011
    Messages : 442
    Points : 612
    Points
    612

    Par défaut

    Je suppose qu'à chaque appel, il n'y a pas 50 villes qui ont le même nom. Le calcul n'étant pas fait un grand nombre de fois, son coût est probablement négligeable par rapport au coût de la requête.
    De même pour le ORDER BY, vu qu'il n'y a pas grand chose à trier, ce n'est probablement pas la peine de s'y attarder.


    Reste le :
    Code :
    WHERE :ville LIKE VILLE
    Tu pourrais mettre "=" et as tu bien indexé VILLE ?

  3. #3
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut

    En fait c'est toute la fonction qui appelée un grand nombre de fois (elle est dans une boucle) ... avec des noms test $new différents.
    Sinon, la probabilité de doublons ('VILLE') est effectivement très faible.

    A part ça le LIKE gère les accents. Les noms 'VILLE' sont directement codés en utf8 (Périgueux = Périgueux) mais je ne sais pas si c'est la meilleure solution. Je l'ai récupéré comme ça (chez geonames), et à vrai dire je n'ai pas encore géré le problème des accents de façon plus poussée.

    Et quant à l'indexation de la colonne j'avoue ne pas non plus avoir étudié le truc .. Je précise que la table n'est pas très volumineuse (38000 entrées) et qu'elle a une clé primaire.
    Elle sert également à une autocompletion. Si il y a un vrai bénéfice à indexer la colonne, j'indexe ..

  4. #4
    Membre chevronné
    Homme Profil pro Frédéric
    Inscrit en
    juin 2011
    Messages
    442
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric
    Localisation : France

    Informations forums :
    Inscription : juin 2011
    Messages : 442
    Points : 612
    Points
    612

    Par défaut

    Il va te falloir 2 minutes pour ajouter un index, et 2 minutes pour l'enlever si les résultats ne sont pas concluants. Ça vaut le coup d'essayer...

    Je peux comprendre l'utilité d'une telle fonction, mais je n'arrive pas à comprendre son intérêt lorsqu'elle est dans une boucle.

  5. #5
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut

    Ok, je regarderai l'indexation de plus près, également si ça m'aide à gérer les accents.

    Je peux comprendre l'utilité d'une telle fonction, mais je n'arrive pas à comprendre son intérêt lorsqu'elle est dans une boucle.
    C'est parce que le nom des villes 'test' ($new) ne viennent pas de la base, il proviennent d'un parsing. Je fais un calcul de distance (avec la ville de la requête initiale) sur chaque résultat (différent) pour filtrer/trier...

  6. #6
    Membre chevronné
    Homme Profil pro Frédéric
    Inscrit en
    juin 2011
    Messages
    442
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric
    Localisation : France

    Informations forums :
    Inscription : juin 2011
    Messages : 442
    Points : 612
    Points
    612

    Par défaut

    Au lieu d'une boucle, tu pourrais faire une seule requête de ce genre :

    Code :
    1
    2
    3
    4
    SELECT VILLE, MIN(calculdistance) AS D 
    FROM `fr` 
    WHERE VILLE IN (ville1,ville2,ville3,...) # Ou VILLE LIKE ville1 OR VILLE like ville2 OR ...
    GROUP BY VILLE

  7. #7
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut

    Pourquoi pas, mais dans le parsing je fabrique un tableau de tableaux...
    Alors ça m'obligerait à ajouter d'autres boucles : une pour extraire les noms des villes des tableaux, puis une autre pour insérer la distance dans le tableau de la ville correspondante (avec un test).. Je suis pas sûr que ça optimise.

    A propos de MIN c'était une de mes questions : la requête suivante ne renvoie rien ..
    Code :
    SELECT MIN (6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon))) FROM `fr` WHERE :ville LIKE VILLE

  8. #8
    Membre actif
    Homme Profil pro
    Inscrit en
    février 2013
    Messages
    79
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : février 2013
    Messages : 79
    Points : 165
    Points
    165

    Par défaut

    Citation Envoyé par krunch Voir le message
    A propos de MIN c'était une de mes questions : la requête suivante ne renvoie rien ..
    Code :
    SELECT MIN (6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon))) FROM `fr` WHERE :ville LIKE VILLE
    Je ne sais pas si c'est une typo, mais le LIKE, il veut des quotes et il fonctionne dans le sens inverse:
    Code :
    WHERE ville LIKE 'pari%'

  9. #9
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut

    Désolé, en fait le MIN marche très bien (avec LIKE dans les 2 sens) et permet de remplacer le 'ORDER BY D ASC LIMIT 1'.
    Version simplifiée :
    Code :
    1
    2
    3
    4
    5
    6
    $coo['ville'] = $new;
    $req = $bdd->prepare ('SELECT RADIANS (latitude) L, MIN(6371 * ACOS (SIN (L) * SIN(:lat) + COS (L) * COS (:lat) * COS (RADIANS (longitude) - :lon))) D FROM `fr` WHERE :ville LIKE VILLE');
    $req->execute($coo);
    $out = $req->fetch();
    $req->closeCursor();
    return $out['D'];
    Pour ce qui est de la structure, Fred m'a mis un doute ...
    J'ai fait la structure la plus évidente avec cette fonction appelée dans la boucle principale, comme si c'était une fonction de calcul normale.
    Dans la mesure où c'est une requête SQL ce n'est peut-être pas le bon raisonnement, je ne sais pas ..

  10. #10
    Expert Confirmé Avatar de ericd69
    Homme Profil pro Eric Dureuil
    Développeur informatique
    Inscrit en
    avril 2011
    Messages
    1 897
    Détails du profil
    Informations personnelles :
    Nom : Homme Eric Dureuil
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : avril 2011
    Messages : 1 897
    Points : 3 207
    Points
    3 207

    Par défaut

    salut,

    et si tu utilisais une fonction stockée pour la formule, ça te permettrait au moins de simplifier l'écriture de ce genre de requête, de limiter la taille de code à transférer à mysql et si tu appelles la fonction plusieurs fois dans la même session tu as en plus le gain de la compilation et du plan d'exécution...
    soyons pensez à mettre quand votre problème est résolu ou à utiliser pour les réponses pertinentes...
    ne posez pas de problématique soi-disant simplifiée sur des problèmes que vous n'êtes pas capable de résoudre par respect pour ceux qui planchent dessus... sinon: et à utiliser pour insérer votre code...

  11. #11
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut

    Salut

    J'ai jeté un œil aux procédures stockées .. effectivement c'est pas bête (c'est un peu comme définir une méthode) et probablement plus rapide.
    Si je reste sur cette structure je l'adopte.

    En gros j'ai le choix entre :
    - cette structure qui répète la requête SQL (mettons de 10 à 300 fois par requête, en procédure stockée)
    - une structure php plus compliquée qui fait 1 seule requête SQL (plus compliquée aussi)

    Je ne sais pas si la 1ère solution (plus simple) est un handicap pour un serveur normal, si quelqu'un sait surtout qu'il n'hésite pas ..

    Et sinon merci pour vos réponses.

  12. #12
    Membre chevronné
    Homme Profil pro Frédéric
    Inscrit en
    juin 2011
    Messages
    442
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric
    Localisation : France

    Informations forums :
    Inscription : juin 2011
    Messages : 442
    Points : 612
    Points
    612

    Par défaut

    300 requêtes c'est souvent plus handicapant qu'une seule requête...

    As-tu ajouté un index ?

  13. #13
    Expert Confirmé Avatar de ericd69
    Homme Profil pro Eric Dureuil
    Développeur informatique
    Inscrit en
    avril 2011
    Messages
    1 897
    Détails du profil
    Informations personnelles :
    Nom : Homme Eric Dureuil
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : avril 2011
    Messages : 1 897
    Points : 3 207
    Points
    3 207

    Par défaut

    c'est surtout un gain dans l'écriture si ce calcul entre dans plusieurs requête...

    de toutes façons, je recommande toujours de séparer langage appelant du sql en utilisant du procédural...

    ça renforce encore la sécurité et l'isolation et la maintenance...

    en général, pour les problème de distance à des coordonnées (ou une autre ville de référence) tu fais ton calcul de distance par rapport à chaque ville que tu classes en ordre desc sur la distance avec un "limit 1" et tu as ton résultat...

    pas besoin de traitement externe à mysql...
    soyons pensez à mettre quand votre problème est résolu ou à utiliser pour les réponses pertinentes...
    ne posez pas de problématique soi-disant simplifiée sur des problèmes que vous n'êtes pas capable de résoudre par respect pour ceux qui planchent dessus... sinon: et à utiliser pour insérer votre code...

  14. #14
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut

    As-tu ajouté un index ?
    Oui j'ai essayé d'indexer la colonne 'VILLE' mais ça ne fait pas de différence : en local, avec ou sans index la requête globale dure env 400ms pour une 20aine de requêtes SQL (et 20 si aucune ..), par contre ça augmente la taille de la table de 1/5.

    tu fais ton calcul de distance par rapport à chaque ville que tu classes en ordre desc sur la distance avec un "limit 1" et tu as ton résultat...
    C'est ce que je fais dans cette fonction (avec MIN).
    J’essaierai en procédure stockée, ça m'a l'air effectivement plus pertinent (également pour l'autocompletion d'ailleurs).


    Pour ce qui est de la structure je ne chasse pas les millisecondes, c'est juste histoire de ne pas partir sur un truc foireux ..

  15. #15
    Expert Confirmé Avatar de ericd69
    Homme Profil pro Eric Dureuil
    Développeur informatique
    Inscrit en
    avril 2011
    Messages
    1 897
    Détails du profil
    Informations personnelles :
    Nom : Homme Eric Dureuil
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : avril 2011
    Messages : 1 897
    Points : 3 207
    Points
    3 207

    Par défaut

    je voulais te faire comprendre que c'est le genre de traitement (boucle ou autre truc) que tu n'as pas forcément à faire coté langage appelant...

    une fois tes données chargées en bd tu peux faire plein de choses en procédural en bd et éviter les allers-retours avec le langage appelant
    soyons pensez à mettre quand votre problème est résolu ou à utiliser pour les réponses pertinentes...
    ne posez pas de problématique soi-disant simplifiée sur des problèmes que vous n'êtes pas capable de résoudre par respect pour ceux qui planchent dessus... sinon: et à utiliser pour insérer votre code...

  16. #16
    Membre à l'essai
    Inscrit en
    juin 2008
    Messages
    100
    Détails du profil
    Informations forums :
    Inscription : juin 2008
    Messages : 100
    Points : 21
    Points
    21

    Par défaut

    je voulais te faire comprendre que c'est le genre de traitement (boucle ou autre truc) que tu n'as pas forcément à faire coté langage appelant...
    Oui en fait vous avez tous les deux raisons, je m'accrochais à une structure lourdingue (chaque appel SQL depuis mon parsing prend 20ms, soit autant que le parsing complet sans appel).

    Alors j'ai fait un essai avec le code de Fred (message #6) écrit sur mesure, il gère les éventuels doublons de nom de villes en renvoyant la plus proche .. il marche impeccable !

    Ça m'oblige à réécrire mes parsings pour extraire les noms des villes dans un tableau à part, puis à les refusionner .. mais c'est sans doute une meilleure solution.
    Ça donnerait quelque chose comme ça :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    function Get_Distance2 ($bdd, $coo, $new)
    {
    	$req = "SELECT VILLE, MIN(6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon))) AS D 
    		FROM `fr` 
    		WHERE VILLE IN ('".implode("','",$new).'\')
    		GROUP BY VILLE';
     
    	$req = $bdd->prepare ($req);
    	$req->execute($coo);
     
    	while ($donnees = $req->fetch())
    	{
    		echo $donnees['VILLE'].$donnees['D'].'<br />';
    	}
    	$req->closeCursor();
    }
    Avec
    Code :
    $new = array ('Paris', 'Lyon', 'Marseille')
    Et une syntaxe de guillemets toute bizarre trouvée ici (apostrophes interdites dans les noms ..)

    Bref, merci de m'avoir fait comprendre...

+ 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
  •