Doctrine - Pourquoi es-tu si long. . .

**zanteskuken** · 20/09/2013, 11h55

Bonjour à tous !

Étant en désarroi j'aimerai avoir vos lumières sur la lenteur d'une requête sur doctrine 2.

L’environnement :

Symfony 2 - Doctrine 2 - Annotations - pgsql 9.0

Phpinfo() :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
PDO
PDO support enabled
PDO drivers     mysql, sqlite, sqlite2, pgsql

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
pdo_pgsql
PDO Driver for PostgreSQL   enabled
PostgreSQL(libpq) Version   9.0.3
Module version  1.0.2
Revision    $Id: pdo_pgsql.c,v 1.7.2.11 2006/03/14 10:49:18 edink Exp $

Symfony :

Fichier - parameters.ini

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[parameters]
    database_driver="pdo_pgsql"
    database_host="localhost"
    database_port="5432"
    database_name="tester"
    database_user="postgres"
    database_password="je veux du super rapide : )"
    ;database_driver_class="PgSqlBundle\Doctrine\DBAL\Driver\PDOPgSql\Driver"
    mailer_transport="gmail"
    mailer_host="465"
    mailer_user="tester@gmail.com
    mailer_password="tester@"
    locale="en"
    secret="aaaaaaaaaaaaaaaaaaaaaa"

Mon Entité AllGeonames :

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
use Doctrine\ORM\Mapping as ORM;
 
/**
 * Pld\Bundle\LocalisationBundle\Entity\AllGeonames
 *
 * @ORM\Table(name="all_geonames", indexes={@ORM\Index(name="allgeo_asciiname_idx", columns={"asciiname"}),
 *  @ORM\Index(name="allgeo_fcodecountryadm_idx", columns={"country","fcode","admin1","admin2","admin3","admin4"}),
 *  @ORM\Index(name="allgeo_adminunid_idx", columns={"admin1id"}),
 *  @ORM\Index(name="allgeo_admindeuxid_idx", columns={"admin2id"}),
 *  @ORM\Index(name="allgeo_admintroisid_idx", columns={"admin3id"}),
 *  @ORM\Index(name="allgeo_adminquatreid_idx", columns={"admin4id"}),
 *  @ORM\Index(name="allgeo_country_idx", columns={"country"}),
 *  @ORM\Index(name="idx_vecteur_ascii", columns={"textevectorise"}),
 *  @ORM\Index(name="allgeo_asciifcode_idx", columns={"asciiname","fclass"})})
 *  @ORM\Entity(repositoryClass="Pld\Bundle\LocalisationBundle\Repository\AllGeonamesRepository")
 */
class AllGeonames {
    /**
     * @var integer $geonameid
     *
     * @ORM\Column(name="geonameid", type="integer", length=11, nullable=false, unique=true)
     * @ORM\Id
     */
    private $geonameid;
 
    /**
     * @var string $namegeo
     *
     * @ORM\Column(name="namegeo", type="string", length=200, nullable=false)
     */
    private $namegeo;
 
    /**
     * @var string $asciiname
     *
     * @ORM\Column(name="asciiname", type="string", length=200, nullable=false)
     */
    private $asciiname;
 
...
...
 
    /**
     * @var VilleDuMondePays
     *
     * @ORM\ManyToOne(targetEntity="VilleDuMondePays", cascade={"persist"})
     *   @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="country", referencedColumnName="pays", nullable=true)
     * })
     *
     */
    private $country;
 
...
...
 
    /**
     * @var VilleDuMondeRegion
     *
     * @ORM\ManyToOne(targetEntity="VilleDuMondeRegion", cascade={"persist"})
     *  @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="admin1id", referencedColumnName="geonameid", nullable=true)
     * })
     *
     */
    private $admin1id;
 
    /**
     * @var VilleDuMondeRegion
     *
     * @ORM\ManyToOne(targetEntity="VilleDuMondeRegion", cascade={"persist"})
     *  @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="admin2id", referencedColumnName="geonameid", nullable=true)
     * })
     *
     */
    private $admin2id;
 
...
...
 
    /**
     * @var array|string $textevectorise
     *
     * @ORM\Column(name="textevectorise", type="tsvector", nullable=true)
     */
    private $textevectorise;

Mon entité AlternateNames :

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
use Doctrine\ORM\Mapping as ORM;
 
/**
 * Pld\Bundle\LocalisationBundle\Entity\AlternateNames
 *
 * @ORM\Table(name="alternate_names", indexes={
 * @ORM\Index(name="alternatename_idx", columns={"alternatename"}),
 * @ORM\Index(name="alternate_geoname_idx", columns={"geonameid"})})
 * @ORM\Entity(repositoryClass="Pld\Bundle\LocalisationBundle\Repository\AlternateNamesRepository")
 */
class AlternateNames
{
    /**
     * @var integer $alternatenameid
     *
     * @ORM\Column(name="alternatenameid", type="integer", length=11, nullable=false)
     * @ORM\Id
     */
    private $alternatenameid;
 
    /*ORM\GeneratedValue(strategy="SEQUENCE")
     *ORM\SequenceGenerator(sequenceName="alternate_names_alternatenameid_seq", allocationSize="1", initialValue="1")
     */
 
    /**
     * @var integer $geonameid
     *
     * @ORM\ManyToOne(targetEntity="AllGeonames", cascade={"persist"})
     *   @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="geonameid", referencedColumnName="geonameid", nullable=false)
     * })
     */
    private $geonameid;
 
...
...
}

Voila l'ensemble :

Maintenant la requête dans le repository Allgeonames :

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
public function getCity($name)
 
    {   
        $rsm = new ResultSetMapping;
        $rsm->addScalarResult('ngeo', 'geonameid');
        $rsm->addScalarResult('nascii', 'asciiname');
        $rsm->addScalarResult('adm1id', 'admin1id');
        $rsm->addScalarResult('adm2id', 'admin2id');
        $rsm->addScalarResult('adm3id', 'admin3id');
        $rsm->addScalarResult('adm4id', 'admin4id');
        $rsm->addScalarResult('ctry', 'country');
 
        $query = $this->_em->createNativeQuery("
        SELECT  * from (select n.asciiname as nascii, n.geonameid as ngeo, admin1id.asciiname AS adm1id, admin2id.asciiname as adm2id, admin3id.asciiname as adm3id, admin4id.asciiname as adm4id, country.name_fr as ctry
                FROM alternate_names a, all_geonames n
                left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
                left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
                left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
                left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
                        left join ville_du_monde_pays country on country.pays=n.country
                where n.geonameid=a.geonameid
                and a.alternatename like :name
                limit 5) as t
                union
                select * from(
                SELECT n.asciiname as nascii, n.geonameid as ngeo, admin1id.asciiname as adm1id, admin2id.asciiname as adm2id, admin3id.asciiname as adm3id, admin4id.asciiname as adm4id, country.name_fr as ctry
                FROM all_geonames n
                left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
                left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
                left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
                left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
                        left join ville_du_monde_pays country on country.pays=n.country
                where n.asciiname like :name limit 5) as d
                        limit 5;", $rsm);
        $query->setParameter('name', $name.'%');
 
 
        $result =  $query->getArrayResult();
return $result
}

Je fais cette requête sous pgAdmin avec comme nom : 'Rio de', résultat en 340ms

Maintenant dans mon interface par autocomplete : 'Albervi' , résultat en 1m,15s (pour avoir Alberville).

(Et je suis le seul utilisateur).

Pourtant mes jointures sont faits et ma requêtes les utilises.

3M de lignes dans alternatesNames

2,1M dans AllGeonames

et 300 000 dans VilleDuMondeRegion.

Explain sous pgsql :

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
"Limit  (cost=388.28..388.33 rows=5 width=2232)"
"  ->  HashAggregate  (cost=388.28..388.38 rows=10 width=2232)"
"        ->  Append  (cost=10.65..388.11 rows=10 width=2232)"
"              ->  Limit  (cost=10.65..214.70 rows=5 width=82)"
"                    ->  Hash Left Join  (cost=10.65..16049.29 rows=393 width=82)"
"                          Hash Cond: ((n.country)::text = (country.pays)::text)"
"                          ->  Nested Loop Left Join  (cost=0.00..16033.24 rows=393 width=73)"
"                                ->  Nested Loop Left Join  (cost=0.00..12866.18 rows=393 width=63)"
"                                      ->  Nested Loop Left Join  (cost=0.00..9699.11 rows=393 width=53)"
"                                            ->  Nested Loop Left Join  (cost=0.00..6532.05 rows=393 width=43)"
"                                                  ->  Nested Loop  (cost=0.00..3364.99 rows=393 width=33)"
"                                                        ->  Index Scan using alternatename_idx on alternate_names a  (cost=0.00..8.89 rows=393 width=4)"
"                                                              Index Cond: (((alternatename)::text >= 'Rio de'::text) AND ((alternatename)::text < 'Rio df'::text))"
"                                                              Filter: ((alternatename)::text ~~ 'Rio de%'::text)"
"                                                        ->  Index Scan using all_geonames_pkey on all_geonames n  (cost=0.00..8.53 rows=1 width=33)"
"                                                              Index Cond: (n.geonameid = a.geonameid)"
"                                                  ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin1id  (cost=0.00..8.05 rows=1 width=18)"
"                                                        Index Cond: (admin1id.geonameid = n.admin1id)"
"                                            ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin2id  (cost=0.00..8.05 rows=1 width=18)"
"                                                  Index Cond: (admin2id.geonameid = n.admin2id)"
"                                      ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin3id  (cost=0.00..8.05 rows=1 width=18)"
"                                            Index Cond: (admin3id.geonameid = n.admin3id)"
"                                ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin4id  (cost=0.00..8.05 rows=1 width=18)"
"                                      Index Cond: (admin4id.geonameid = n.admin4id)"
"                          ->  Hash  (cost=7.51..7.51 rows=251 width=14)"
"                                ->  Seq Scan on ville_du_monde_pays country  (cost=0.00..7.51 rows=251 width=14)"
"              ->  Limit  (cost=10.65..173.21 rows=5 width=82)"
"                    ->  Nested Loop Left Join  (cost=10.65..9991.69 rows=307 width=82)"
"                          ->  Nested Loop Left Join  (cost=10.65..7499.66 rows=307 width=72)"
"                                ->  Hash Left Join  (cost=10.65..5007.64 rows=307 width=62)"
"                                      Hash Cond: ((n.country)::text = (country.pays)::text)"
"                                      ->  Nested Loop Left Join  (cost=0.00..4992.77 rows=307 width=53)"
"                                            ->  Nested Loop Left Join  (cost=0.00..2500.75 rows=307 width=43)"
"                                                  ->  Index Scan using allgeo_asciiname_idx on all_geonames n  (cost=0.00..8.72 rows=307 width=33)"
"                                                        Index Cond: (((asciiname)::text >= 'Rio de'::text) AND ((asciiname)::text < 'Rio df'::text))"
"                                                        Filter: ((asciiname)::text ~~ 'Rio de%'::text)"
"                                                  ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin1id  (cost=0.00..8.10 rows=1 width=18)"
"                                                        Index Cond: (admin1id.geonameid = n.admin1id)"
"                                            ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin2id  (cost=0.00..8.10 rows=1 width=18)"
"                                                  Index Cond: (admin2id.geonameid = n.admin2id)"
"                                      ->  Hash  (cost=7.51..7.51 rows=251 width=14)"
"                                            ->  Seq Scan on ville_du_monde_pays country  (cost=0.00..7.51 rows=251 width=14)"
"                                ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin3id  (cost=0.00..8.10 rows=1 width=18)"
"                                      Index Cond: (admin3id.geonameid = n.admin3id)"
"                          ->  Index Scan using ville_du_monde_region_pkey on ville_du_monde_region admin4id  (cost=0.00..8.10 rows=1 width=18)"
"                                Index Cond: (admin4id.geonameid = n.admin4id)"

Les index sont bien utilisés.

Qu'est ce qui rate chez doctrine ? ou alors qu'est ce que j'ai oublié de faire ?

Ps : tout ça je n'ai pas de cache et autre mis sous doctrine et tout, je ne sais pas encore faire. Peut importe j'ai envie de dire. J'effectue une requête sous pgqdmin qui n'est pas mis en cache ainsi que le résultat, alors donc peut importe qu'il n'y ais encore de cache sous doctrine. Ils sont à "égalité", si j'arrive à bien faire passer l'idée. Je vois juste une trop grande différence et c'est inquiétant.

J'ai fais exprès d'utilisé une requête native, elle n'est pas sencé utilisé le mapping (si je comprends bien), mais cela reste long. Orm bien, juste pour des minis projets... ?
Bien sur qu'un orm est toujours plus lent qu'un accès direct en sql mais comment à ce point ?

Pour l'optique d'une performance d'utilisation comme "LeBonCoin"/"developpez" à fort taux de lecture et d'écriture et utilisateurs, faut lâcher l'Orm ?.

Merci de votre expertise ; ).

**arnooo999** · 20/09/2013, 19h05

Bonjour,

Pour ma part j'ai déjà eut des temps d'attente trés long en utilisant Doctrine.
Aprés analyse, le problème ne venait pas de Doctrine mais de la base mysql, à cause d'un problème d'index. (en fait lorsqu'il y a trop d'index cela peut avoir l'effet inverse et ralentir les requetes).

A priori ton problème est différent, néammoins je commencerai par faire ce test dans un controller:

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
 
public function testAction(){
$sql="La grosse requete";
 
//objet Doctrine\DBAL\Connection voir http://docs.doctrine-project.org/pro...ation.html#api
$db=$this->get('doctrine.dbal.default_connection');
 
//test 1
$stmt=$db->query($sql);
 
$stmt->fetchAll();
 
//test 2
$stmt=$db->prepare($sql);
 
$stmt->execute();
 
$stmt->fetchAll(); 
 
return new \Symfony\Component\HttpFoundation('<html><body>requete terminée</body></html>');
}

tu peux également faire de même avec un objet PDO.

autre question: les requetes en DQL fonctionnent t'elle correctement sur des requetes + simples?

**zanteskuken** · 23/09/2013, 15h35

Hello Arnaud,

J'ai fait le test et cela réduis un peut l'attente mais n'est pas suffisant.
Je n'ai pas tout compris le test. Exécuter deux fois la requête (la deuxième retournera le résultat déjà exécuté et présent en mémoire et sera donc "instantanée" non ? (c'est ce que j'ai remarqué)). Je me suis dis de faire deux requête avec des noms différents mais du coup la recherche sur l'index peut renvoyer un nombre différents de résultats et peut être différent aussi donc d'un point de vue temps.
Après c'est déjà moins long c'est sur.

Je n'ai pas d'outils pour mesurer tout cela, j'utilise juste des logs et firebug.

J'ai enlevé le premier test alors.

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
$sql = "
    			SELECT  * from (select distinct a.geonameid as geonameid, n.asciiname as asciiname, admin1id.asciiname AS admin1id, admin2id.asciiname as admin2id, admin3id.asciiname as admin3id, admin4id.asciiname as admin4id, country.name_fr as country
    			FROM alternate_names a
    			left join all_geonames n on n.geonameid = a.geonameid
    			left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
    			left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
    			left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
    			left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
    			left join ville_du_monde_pays country on country.pays=n.country
    			where a.alternatename like '".$name."%'
    			limit 5) as t
    			union
    			select * from(
    			SELECT distinct n.geonameid as geonameid, n.asciiname as asciiname, admin1id.asciiname as admin1id, admin2id.asciiname as admin2id, admin3id.asciiname as admin3id, admin4id.asciiname as admin4id, country.name_fr as country
    			FROM all_geonames n
    			left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
    			left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
    			left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
    			left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
    			left join ville_du_monde_pays country on country.pays=n.country
    			where n.asciiname like '".$name."%' limit 5) as d
    			limit 5;";
 
    			$db=$this->get('doctrine.dbal.default_connection');
 
    			$logger->info(' - LocalisationController - autocomplete -start ');
    			//test 1
    			//$stmt=$db->query($sql);
    			//$info = $stmt->fetchAll();
 
    			$logger->info(' - LocalisationController - autocomplete -end t1');
    			$stmt=$db->prepare($sql);
 
    			$stmt->execute();
 
    			$info = $stmt->fetchAll();
    			$logger->info(' - LocalisationController - autocomplete -end t2');

Résultats :

Recherche pour Mas% : 1m02s
Recherche pour Cro% 13,69s
Recherche pour Cheo% 1,35s
Recherche pour Chenoa% 511ms
Recherche pour Covell% 180ms

(temps vu sur firebug (cycle complet donc), serveur en local).

Mais le logger remarque bien le pseudo même temps :
=> Mas :

[2013-09-23 13:18:29] request.INFO: Matched route "search_city" (parameters: "_controller": "\DefaultController::searchCityAction", "name": "Mas", "_route": "search_city") [] []
[2013-09-23 13:18:29] app.INFO: - LocalisationController - autocomplete -end t1 [] []
[2013-09-23 13:19:31] app.INFO: - LocalisationController - autocomplete -end t2 [] []

=> Cro

[2013-09-23 13:19:46] app.INFO: - LocalisationController - autocomplete -end t1 [] []
[2013-09-23 13:20:00] app.INFO: - LocalisationController - autocomplete -end t2 [] []

Remarque :
- Plus le nom est complet plus c'est rapide
- Certains mots de trois lettres ressortent (beaucoup) plus rapidement que d'autres. (C'est étrange quand même, à quoi servirai donc un index dès lors).

Sur des requêtes toutes simple cela marche bien (c'est relatif vu l'autre temps d'attente) : exemple :

Liste pays d'un continent :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
 
public function paysAction($name)
    {
    	$villeDuMondePaysList = $this->getDoctrine()
    	->getRepository('PldLocalisationBundle:VilleDuMondePays')
    	->findByContinent($name);
 
...
...
    }

Réponse 111-270 ms.

Ville d'un département :

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
 
 
public function getCityByDpt($country, $region, $dept)
 
    {
    	$code=array('PPL', 'PPLX', 'PL');
 
    	$qb = $this->createQueryBuilder('c')
 
    	->select('c')
    	->where('c.country = :country')
    	->andWhere('c.admin1 = :admin1')
    	->andWhere('c.admin2 = :admin2')
    	->andWhere('c.fcode = :fcode ')
 
    	->setParameters(array('country'=>$country, 'admin1'=>$region, 'admin2'=>$dept, 'fcode'=>'PPL'))
 
    	->addOrderBy('c.asciiname', "ASC");
 
    	return $qb->getQuery()
 
    	->getResult();
 
    }

réponse 300ms.

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
 
 public function getRegionByCountry($country)
 
    {	 
 
    	$qb = $this->createQueryBuilder('c')
 
    	->select('c')
 
    	->where('c.country = :country')
 
    	->andWhere('c.fcode = :fcode')
 
    	->setParameters(array('country'=>$country, 'fcode'=>"ADM1"))
 
    	->addOrderBy('c.asciiname', "ASC");
 
    	return $qb->getQuery()
 
    	->getResult();
 
    }

1s pour les régions

Note : Étrange que la recherche de ville aille plus vite que les régions
- Plus de ville que de régions
- Moins de tri sur la table pour rechercher des régions

Je ne comprends pas vraiment tout.

Ps : c'est vrai que j'ai déjà remarqué ce problème d'index. Doctrine et la base à l'air de s'empatouiller. Sur mysql c'était long puis un jour paf, rapide (avec fullt text Match() against(), ou avant je ne sais plus très bien ). incompris !

Je ne sais pas trop quoi faire.

ps ps : Surtout que je viens à l'instant de faire c'est requêtes dans pgAdmin avec en mots clé 'Tre' : réponses en 37secondes.

- perdu !!

ps ps ps : J'ai remarqué aussi que mon index sur ma table pays n'est pas utilisé, lors de l'explain :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
 ->  Hash  (cost=7.51..7.51 rows=251 width=14)"
"                                            ->  Seq Scan on ville_du_monde_pays country  (cost=0.00..7.51 rows=251 width=14)"

Alors que j'en ai un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE UNIQUE INDEX villepays_pays_idx
  ON ville_du_monde_pays
  USING btree
  (pays);

**arnooo999** · 23/09/2013, 17h09

Je connais pas trop PostgresSQL, mais comme je te le disais au début, sur Mysql c'était la présence d'index qui posait problème, en fait la requete commençait ses recherches sur un index non pertinent.

De plus, sur une requete LIKE je ne suis pas sur qu'un index sera vraiment utile (il le serait probablement sur une requete name="toto").

ps ps : Surtout que je viens à l'instant de faire c'est requêtes dans pgAdmin avec en mots clé 'Tre' : réponses en 37secondes. - perdu !!

On peut donc en déduire qu'il ne s'agit pas d'un problème Doctrine, mais simplement d'une requète lourde sur une base importante, peut-être auras tu plus de réponses dans le forum SGBD/PostgreSQL

As tu essayé de faire des requetes simple sur la table alternate_names avec un LIKE ?
puis en ajoutant peu à peu les jointures et union.

Chacune des tes sous-requetes a un limit 5 et la requete générale a également un limit 5, vu le nombre d'entrée dans ta base, je doute que les résultats de la deuxième sous-requetes soit souvent affichés.

As tu également tenter d'enlever les subselect à l'intérieur de tes unions

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
 
(
SELECT  
distinct a.geonameid as geonameid, 
n.asciiname as asciiname, 
admin1id.asciiname AS admin1id, 
admin2id.asciiname as admin2id, 
admin3id.asciiname as admin3id, 
admin4id.asciiname as admin4id, 
country.name_fr as country
    FROM alternate_names a
    left join all_geonames n on n.geonameid = a.geonameid
    left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
    left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
    left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
    left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
    left join ville_du_monde_pays country on country.pays=n.country
    where a.alternatename like '".$name."%'
    limit 5
)
union
(
SELECT 
distinct n.geonameid as geonameid, 
n.asciiname as asciiname, 
admin1id.asciiname as admin1id, 
admin2id.asciiname as admin2id, 
admin3id.asciiname as admin3id, 
admin4id.asciiname as admin4id, 
country.name_fr as country
    	FROM all_geonames n
    	left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
    	left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
    	left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
    	left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
    	left join ville_du_monde_pays country on country.pays=n.country
    	where n.asciiname like '".$name."%' limit 5
);

**pmithrandir** · 24/09/2013, 10h09

Bonjour,

J'ai énormément de mal a lire les annotation doctrine, donc je vais peut être dire des bêtises, mais est ce que tu n'utilises pas une sorte d'autojointure avec des pays qui contiennent des villes ou un truc du genre.... avec 4 niveaux de profondeur dans ton arbre ?

Si c'est le cas, il pourrait être intéressant de repenser ta structure de table et tes requêtes...
Il y a un moyen de récupérer tes données en une seule requête presque immédiate sans jointure si tu te débrouilles bien.

En gros, l'idée est d'attribuer un entier minimum et maximum a toutes tes données... tu t'arrange pour que le parent ait toujours un minimum < min du premier enfant et un max > max du dernier enfant... chaque enfant ayant un min > max précedent.

Ta requete ressemble ensuite a :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Select *
FROM matable
WHERE min>'xxx' AND max<'yyy'

Est ce que ca pourrait répondre a ta question ?
Autrement, peux tu decrire vite fait les relations entre tes tables ? y a peut etre plus du travail a faire du coté SQL que doctrine vu que tu n'utilise pas doctrine maintenant...

**zanteskuken** · 26/09/2013, 16h12

Mais je pense bien qu'en premier lieu vous avez tout à fait raison.

Je discerne trop "d'ambiguité" niveau sql. Les requêtes sont parfois hasardeuses dans leur logique de recherche.

J'ai bien lu ton message arnaud la dernière fois, j'essaye plein de truc depuis mais sans "réel" succès.

Je suis passé d'une recherche like à une recherche plein texte (tsvector - to_tsquery), le résultat semble plus rapide un peut.

J'essaye alors progressivement (avec like ):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT * FROM alternate_names a
	where a.alternatename like 'Ma%'
	limit 5;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT *
FROM alternate_names a
left join all_geonames allg on allg.geonameid = a.geonameid
where a.alternatename like 'Dar%'
limit 5;

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
 
SELECT  a.geonameid as geonameid, 
n.asciiname as asciiname, 
admin1id.asciiname AS admin1id, 
admin2id.asciiname as admin2id, 
admin3id.asciiname as admin3id, 
admin4id.asciiname as admin4id, 
    FROM alternate_names a
    left join all_geonames n on n.geonameid = a.geonameid
    left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
    left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
    left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
    left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
    where a.alternatename like '".$name."%'
    limit 5
)

ça va jusque là

Dès que je précise 'Distinct', ça flanche. Mouline mouline. C'est très embêtant car je ne veux pas avoir plusieurs fois une même référence.

Je rajoute la jointure vers pays

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
 left join ville_du_monde_pays paysid on paysid.id=n.paysid

Mais aucun index n'est pris encore.
J'ai changé la jointure, j'ai créé une nouvelle colonne sur 'All_geonames" nommé 'paysid' qui correspond à l "id" de la table 'pays', me disant qu'une jointure serai mieux sur un 'id' qu'une colonne varchar. Mais non, fonctionne pas.
La recherche n'est pas beaucoup plus longue toutefois.

Les résultats sont les mêmes pour la table "All_geonames"

J'ai essayé ta requêtes sans subSelect, mais rien ne change, c'est pire en fait et sans 'distinct'. Je ne s'aurai t'expliquer pourquoi !!

Oui tu as bien raison sur le calcul des limit, j'avais bien vu mais je n'avais pas modifié.

Voila où j'en suis pour le moment :

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
 
SELECT  * from (select a.alternatename as nascii, n.geonameid as ngeo, admin1id.asciiname AS adm1id, admin2id.asciiname as adm2id, admin3id.asciiname as adm3id, admin4id.asciiname as adm4id, country.name_fr as ctry
                FROM alternate_names a
                left join all_geonames n on n.geonameid = a.geonameid
                left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
                left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
                left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
                left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
                        left join ville_du_monde_pays country on country.id=n.paysid
                where a.alternatename like 'Lun%'
                limit 5) as t
                union
                select * from(
                SELECT n.asciiname as nascii, n.geonameid as ngeo, admin1id.asciiname as adm1id, admin2id.asciiname as adm2id, admin3id.asciiname as adm3id, admin4id.asciiname as adm4id, country.name_fr as ctry
                FROM all_geonames n
                left join ville_du_monde_region admin1id on admin1id.geonameid = n.admin1id
                left join ville_du_monde_region admin2id on admin2id.geonameid = n.admin2id
                left join ville_du_monde_region admin3id on admin3id.geonameid = n.admin3id
                left join ville_du_monde_region admin4id on admin4id.geonameid = n.admin4id
                        left join ville_du_monde_pays country on country.id=n.paysid
                where n.asciiname like 'Lun%' limit 5) as d;

Cette requête s'exécute en 136ms sur pgqdmin et tout autant à travers l'appli.

Ce distinct m'embête donc bien. Il ralenti tout le processus hors il reste indispensable.
Cette table alternate_names spécifie en différente langue une même ville, il se peut donc que lors de la recherche
la table alter_names renvoie une référence
la table all_geonames renvoie la même référence
C'est sans doute faible en pourcentage mais il existe, et l'utilisateur on voyant cela, s'en agacera (lequel choisir ? c'est stupide à manipuler ).

Je réfléchirai après pour le tsvector (full search). ça ne renvoie pas les mêmes données. utiles pour les villes composés ou autre, mais déroutant pour un nom unique. (d'ailleurs ts_stat peut être intéressant, juste pour info).

Salut pmithrandir,

ça à l'air vraiment spécifique ce que tu propose, j'essaye de comprendre, pas évident du premier coup.

Pour l'arbre, je ne suis pas beaucoup aiguisé sur le terme, tu voudrai dire clé étrangère ?

Tout tourne autour de la table 'all_geonames'.

- La table alternate_names dispose comme clé étrangère la clé primaire de "all_geonames"

(à l'origine c'était juste une donnée, que j'ai spécifié en clé étrangère)

- La table all_geonames à 6 (un doublons) clé étrangères
-> 4 vers la table "Ville_du_monde_region", pour référencer Région / département / arrondissement/(celui là je ne sais pas bien)
-> 2 vers la table pays, mais je n'utiliserai plus qu'un seul

- La table pays à une clé étrangère vers "UneRegionContinentale", mais que je n'utilise pas (je l’enlèverai).

je ne vois pas bien ce qu'est une autoJointure.

Pour l'autocomplétion, je monte du plus local au plus large (ville ->->-> pays).

J'ai mis un certain temps à comprendre ta logique. Je ne sais pas si ça me correspond mais en tout cas j'ai saisi un minimum l’intérêt et c'est intéressant oui.

Tu te dis en fait que tout est dans une même table et que je fais des jointures internes ?

A l'origine j'ai débuté ainsi, mais j'ai scindé en deux ensuite. Ville/région pour la restitution des données par peur de blocker la table "all_geonames".

ça me fait donc

table 'alternate_names' -> (fk) ->table 'all_geonames' ->(fk4) (fk2) -> tables 'ville_du_monde_region' / 'Ville_du_monde_pays'

C'est très compliqué ton histoire min max, je pensais avoir compris mais plus je lis plus je me perd ; ) .

Tu veux dire que "Voiture" (le parent), dispose de quatre roues (les enfants)

Voiture (min = 1 , max = 5)
Roue 1 (min = 2, max =1)
Roue 2 (min = 3, max =2)
Roue 3 (min = 4, max =3)
Roue 4 (min = 5, max =4)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Select mesEnfantsChéris from mesVoitures mvt
where mvt.min > Voiture.min 
and mvt.max < Voiture.max

J'aurai Roue1/2/3/4

Ps: Comment on sais que roue 1-4 était à voiture auparavant ?

On rajoute

roue 5 (min = 5, max = 5)

car on ne veut pas qu'il fasse partis de "Voiture", mais fasse partie de "Kart".

Kart (min = 1, max = 6)

-> on aura alors d'après la requête en résultats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Select mesEnfantsChéris from mesVoitures mvt
where mvt.min > Kart.min 
and mvt.max < Kart.max

: Roue 1-5 (hors on ne voulais que roue 5)

Il faut donc mettre : Kart (min = 4, max = 6)

ça à l'air très compliqué à maintenir non ?

C'est programmable où ça se fait à la main ?

Et si j'ai un camion avec les roues 2,3et5

Camion (min = 2, max =5)

la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Select mesEnfantsChéris from mesVoitures mvt
where mvt.min > Camion.min 
and mvt.max < Camion.max

Me renverra Roue 2,3,4,5 , hors je ne voulais pas 4 (non ?).

Sinon il me faut transformer :

Roue 4 (min = 5, max =4) en = > Roue 4 (min = 5, max =7)
et
Camion (min = 2, max =5) en => Camion (min = 2, max =8)

Mais aussi
Voiture (min = 1 , max = 5) en => Voiture (min = 1 , max = 8)

Mais qui dès lors me renverra Roue5 qu je ne veux

J'ai l'impression de faire un puzzle très compliqué.

Après j'ai peut-être tout compris de travers et j'en suis navré.

Mais je comprends bien que cette notion de "between" puisse accélérer une recherche, je ne sais pas, mais en revanche, mhhh, cela veut dire alors qu'il faut avoir une clé composé sur les colonnes "min" et "max", pour vraiment être efficace.

En tout cas ta technique fait réfléchir.

Je ne vois pas comment résoudre ce casse tête.

La requête sera plutôt du genre (le plus simple possible)

Select asciiname from all_geonames
where min < (select min from all_geonames where asciiname like 'toutça%')
and max > (select max from all_geonames where asciiname like 'toutça%');

Mais ceci me récupère juste les "roues" (Région/département/arrondissement/truc), Mais, et le nom de la ville associé ?

Et encore, tout ceci dans le désordre. En effet tu me demande de tout refaire ^^.

4 Inner join est plus rapide que 4 left join ?
4 fois plus de boulot sur une table n'est-il pas sans conséquence ?

Je ne sais pas n'ayant jamais mesuré quoi que ce soit, mais c'est ce que je me dis.
J'étais partis sur des innerJoin mais j'ai bifurqué sur des leftJoin pour ne pas toujours bosser sur la même table. Même si après on peut mettre le contenue de la table en mémoire (non ?), pour qu'il n'y ais de lecture table.

En résumé :
Faut que je trouve une parade pour l'aspect "disinct" qui ralentit tout
Comment utiliser mon index sur pays (trop de left join ?)
Ais-je tord d'avoir déchargé ma table (ça semble linéaire pourtant et structuré), tout semble concret.
Que puis-je faire de l'idée min/max

Intéressant vos retour ; )

Ps : Mince, je crois qu'on ne peut sans doute pas joindre deux tables (b (ville_du_monde_region), c (ville_du_monde_pays)) à une table (a (all_geonames)) si b et c n'ont de clause entres elles sans doute ! Ce serai vraiment INADMISSIBLE !!! Je regarde voir

**pmithrandir** · 04/10/2013, 16h51

Bonjour....

je vois que je t'ai fait réfléchir.

je vais essayer de mieux expliquer.
Au lieu de stocker tes localisations dans 4 tables : ville / departement / region / pays par exemple, tu créés juste une localisation générique, eventuellement avec un type définit(parmis ville / dpt / regioon et pays)

On a donc une autojointure(cad une jointure sur la table ou l'on est)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Localisation
_________
id
name
idParent===>fk sur Localisation.id

Le probleme c'est que ta requete va donner un truc du genre :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT * 
FROM localisation pays
LEFT JOIN localisation region ON region.id_parent = pays.id
LEFT JOIN localisation dpt ON dpt.id_parent = region.id
LEFT JOIN localisation dpt ON ville.id_parent = dpt.id
WHERE pays.name LIKE ("%test%")
...

Si maintenant tu utilise l'autre méthode qui dailleur ne fonctionne pas tout a fait comme ce que tu as dis, on obtiendras :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT min, max
FROM localisation
WHERE localisation.name LIKE ("%test%");
SELECT *
FROM localisation
WHERE localisation.min>="$min"
AND localisation.max<="$max";

Pas de jointure de grosses tables ensemble, 2 requêtes facilement indexable(min et max sont des entiers)

Pour reprendre l'exemple des pays, on aurait :

France :  min = 1 ; max = 15
Bretagne : min = 2 max = 11
Finistere : min = 3, max = 4
cote d'armor : min = 5, max = 6
morbihan : min = 7 max = 8
ile et vilaine : min = 9 max = 10
Poitou charente : min = 11; max = 14
charente : min = 12, max = 13

un simple :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
FROM localisation
WHERE min>=2 AND max <=11

te donnera toutes les localisations en bretagne

La même avec un jointure sur un objet externe :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT *
FROM localisation
LEFT JOIN clients ON clients.localisation=id = l.id
WHERE min>=2 AND max <=11

Te donne tous les clients présent en bretagne, associé a une ville bretonne, a un departement ou a la region.

Si ca peut t'aider, tant mieux... ca peut réduire la complexité de tes requêtes... la difficulté tient dans le fait qu'on doit générer les min et max, et qu'il est très difficile et couteux de les changer(donc il faut commencer avec la liste complete directement de préférence)

Mais je suis peut etre parti dans un solution trop complexe c'est ce que j'utilise sur mon site(en signature) quand les gens affiche les idées d'une zone géographique.

**zanteskuken** · 22/10/2013, 12h43

Je reviens tard, désolé.

Hello,

Oui je comprends bien la notion d'auto-jointure. Je me dis simplement que travailler 3 fois sur 3 tables serai moins lent que 10 fois sur 1 table. Je ne le sais pas réellement.

Oui c'est bien cela que j'ai comme requête et ton idée est intéressante mais comment fait-on pour récupérer :

Ville - dept - région - Pays

Avec ta solution, dans cette ordre ?

Le "select *" va peut-être me récupérer le département, puis la ville etc, il me faut reconnaitre les informations. De plus, associé chaque ville à son bon département,etc.

I.e : Pour le test : Mont%

résultat possible : Montfort, Montauban de bretagne, Monterfil, Montauban...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
France :  min = 1 ; max = 15
Bretagne : min = 2 max = 11
ille et vilaine : min = 9 max = 10
Montfort : min = 9 max = 10
Montauban-de-bretagne : min = 9 max = 10
Monterfil : min = 9 max = 10
Midi-Pyrénées : min=15 max = 18
Tarn-et-garonne : min=15 max=17
Montauban : min=15 max=17

Comment afficher Ville-dep-reg-pays ?

On peut mettre des codes
Code : pays =1, reg = 2, dept = 3, ville =4

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
1 -France :  min = 1 ; max = 15
2 Bretagne : min = 2 max = 11
3 ille et vilaine : min = 9 max = 10
4 Montfort : min = 9 max = 10
4 Montauban-de-bretagne : min = 9 max = 10
4 Monterfil : min = 9 max = 10
2 Midi-Pyrénées : min=15 max = 18
3 Tarn-et-garonne : min=15 max=17
4 Montauban : min=15 max=17

Monterfil (4) rattaché à Ille-et-vilaine(3) ou Tarn-et-garonne(3), il faut donc regarder le min et max. Donc une autre requête. Et ceux pour chaque ligne Ville, Dept, Reg.
Les min/max des ville à quels min/max des depts, puis des reg et pays.
Ou alors faire une boucle dans une fonction pour faire les associations. Au final serai-ce plus rapide que des lefts joins ?

J'ai regardé ton site http://www.jaiuneidee.net/, qui est intéressant "comme idée" au passage. Et dans ton autocomplete des villes il n'y a cette notion d'associations ville-dept-reg-pays.

Donc en fait tu utilise ta méthode surtout, pour récupérer les idées issue d'une localisation plus ou moins ciblée, émises par des personnes.

Parceque je pense qu'il te faudra te pencher sur cette notion ville-dept etc

1 : "Montpeyroux" est présent dans plusieurs département de France.
2 : Comment savoir laquelle me correspond via cette autocompletion proposé ?

As-tu peut-être été plus loin dans tout ceci ?

Doctrine - Pourquoi es-tu si long. . .

Symfony PHP

Discussions similaires

Partager

Partager