Discussion :

[azpublic]

Bonjour,

Je suis acctuellement entrain de manipuler un table de plus de 8Millions d'entrées.
L'une des colonne de cette table contient le code pays relatif a l'entrée.

Une de mes taches consiste a mettre a jour une autre colonne avec la population du pays relatif a l'entére.

J'ai une table de correspondance entre le code pays et la population qui est chargée en mémoire.

J'ai testé 2 stratégies :

1ere strategie: pour chaque code pays
"

Code :

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

UPDATE maTable SET population = "+variablepop+" WHERE country = "+variablepays+

" ; "

2e strategie : "

Code :

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

SELECT id, country FROM matable;

"

puis iteration sur les resultats suivi de

"

Code :

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

UPDATE maTable SET population = "+variablepop+" WHERE id = "+variableId+

" ; "


Dans les deux cas le job prend pratiquement une journée (24 heures) pour être completè.

Lors de mes tests le premier cas a meme mis plus longtemps que le 2e ...


Est-ce que ces temps d'exécution sont des temps normaux ?
Ou est-ce que ca devrait aller bcp plus vite ?

Je travaille avec un pentium dualcore 2.6GO et 4G de ram + un disque 7200 tours minutes;

Le SGBD est PostgreSQL.
Le language est JAVA avec du bon vieux JDBC.


Merci pour vos réponses.

Alex

[CinePhil]

L'une des colonne de cette table contient le code pays relatif a l'entrée.

Quel est le type de la colonne portant le code du pays ?
Si c'est maximum un CHAR(3), ça va. Si c'est plus gros, il vaut mieux passer par un identifiant anonyme de type entier non nul non signé et auto-incrémenté.

Une de mes taches consiste a mettre a jour une autre colonne avec la population du pays relatif a l'entére.

J'ai une table de correspondance entre le code pays et la population qui est chargée en mémoire.

Mauvaise idée !
Puisque la population du pays figure dans la table des pays, inutile de la répéter dans la grosse table. Il vaut mieux faire une jointure entre les deux pour obtenir l'information voulue.

Code :

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

1
2
3
SELECT t.les_colonnes_souhaitees, p.country, p.population
FROM maTable AS t
INNER JOIN pays AS p ON t.code_pays = p.code_pays

Et pour que ce soit optimum, il faut évidemment que les colonnes code_pays des deux tables soient indexées.

[azpublic]

Merci Philippe pour la réponse rapide.

Au niveau performance lequel est le plus performant en terme de rapidité d'exécution ? :

Code :

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

1
2
3
SELECT t.les_colonnes_souhaitees, p.country, p.population
FROM maTable AS t
INNER JOIN pays AS p ON t.code_pays = p.code_pays

ou

Code :

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

SELECT t.mescolonnes WHERE t.normalized_name = "+mavariable+" ;

?

Merci

[CinePhil]

Les deux requêtes ne répondent pas au même besoin.

La première donne toute les lignes de maTable avec le nom du pays et la population.
La seconde donne la ou les lignes dont la colonne normalized_name = la variable.

Si la colonne normalized_name est indexée, ça devrait être rapide, même avec 8 millions de lignes.

On peut avoir la structure de la grosse table et la description du besoin réel ?

[estofilo]

Puisque les données sont déjà en tables, il est possible de faire toute la mise à jour en un seul UPDATE corrélé. L'exemple de la doc est le suivant:

Code :

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

1
2
3
UPDATE employes SET total_ventes = total_ventes + 1 FROM comptes
  WHERE compte.nom = 'Acme Corporation'
  AND employes.id = compte.vendeur;

A transposer avec les pays et les populations.

Par ailleurs pour la vitesse d'exécution, il faudrait faire un index sur le code pays s'il n'existe pas déjà.

[azpublic]

@CinePhil :

Pardon, je me suis mal exprimé.
Je voulais dire :

Code :

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

1
2
3
SELECT t.les_colonnes_souhaitees, p.country, p.population
FROM maTable AS t WHERE t.normalized_name = "+mavar+"
INNER JOIN pays AS p ON t.code_pays = p.code_pays

Versus

Code :

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

SELECT t.les_colonnes_souhaitees, t.countrycode, t.pop WHERE t.normalized_name = "+mavariable+" ;

La colonne Normalized name est indexée.

Les selects sont rapides. Cependant, il sagit d'une table qui va etre utilisée dans un moteur de recherche géographique bien spécifique et grand public.
Je m'attend donc a devoir exécuter des dixaines voir centaines de milliers de requetes (millions ?? espérons-le ) par minute.

L'algorithme qui viens requeter cette table est utilisé pour faire de la "query expansion" et pour identifier les noms de locations géographiques contenu dans une requete "full text" (type google maps). Les termes de la query sont découpés et une serie de requetes SQL sont générées pour aller trouver l'endroit geographique dont le nom correspond le mieux a la requete full text d'origine.

Aussi les temps de réponse de la DB sont tres important et une différence de quelques 50 / 100 ms qui peut paraitre anodine a certaines échelles, a des repercussions au vu du nombre potentiel de requetes par secondes.

En intégrant toutes les données nécessaires a mon algo dans une seule table j'eperais gagner quelqeus millisecondes précieuses, bien entendu, tout en sachant que cela se ferait au détriment de la taille et "maintainability" de la database. Je vise un temps de réponse total de l'algo de recherche inferieur a 500ms et selon les cas, le requeteage de la grosse table mange deja plus de 300ms (sur plusieures requetes SQL bien entendu) ce qui laisse peu de place pour le reste de l'algo.

Pour ce qui est de la structure :

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
 
CREATE TABLE "public"."gazetteer" (
"id" int8 DEFAULT nextval('gazetteer_id_seq'::regclass) NOT NULL,
"gn_id" int8 DEFAULT NULL,
"ascii_name" varchar(255) DEFAULT NULL::character varying,
"latitude" numeric DEFAULT NULL,
"longitude" numeric DEFAULT NULL,
"feature_class" varchar(1) DEFAULT NULL::character varying,
"feature_code" varchar(10) DEFAULT NULL::character varying,
"country_code" varchar(2) DEFAULT NULL::character varying,
"admin1_names" varchar(2000) DEFAULT NULL::character varying,
"admin1_code" varchar(255) DEFAULT NULL::character varying,
"admin2_names" varchar(2000) DEFAULT NULL::character varying,
"admin2_code" varchar(255) DEFAULT NULL::character varying,
"admin3_names" varchar(2000) DEFAULT NULL::character varying,
"admin3_code" varchar(255) DEFAULT NULL::character varying,
"admin4_names" varchar(2000) DEFAULT NULL::character varying,
"admin4_code" varchar(255) DEFAULT NULL::character varying,
"admin5_names" varchar(2000) DEFAULT NULL::character varying,
"admin5_code" varchar(255) DEFAULT NULL::character varying,
"admin6_names" varchar(2000) DEFAULT NULL::character varying,
"admin6_code" varchar(255) DEFAULT NULL::character varying,
"population" int8 DEFAULT NULL,
"country_names" varchar(6000) DEFAULT NULL::character varying,
"normalized_name" varchar(255) DEFAULT NULL::character varying,
"adm1_gn_id" int8 DEFAULT NULL,
"adm2_gn_id" int8 DEFAULT NULL,
"adm3_gn_id" int8 DEFAULT NULL,
"adm4_gn_id" int8 DEFAULT NULL,
"adm5_gn_id" int8 DEFAULT NULL,
"adm6_gn_id" int8 DEFAULT NULL,
"is_country" char(1) DEFAULT NULL,
"country_pop" int4 DEFAULT NULL,
CONSTRAINT "gazeteer_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;
 
ALTER TABLE "public"."gazetteer" OWNER TO "postgres";
 
CREATE INDEX "gaz_adm1_indx" ON "public"."gazetteer" USING btree ("admin1_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_adm1cntry_indx" ON "public"."gazetteer" USING btree ("country_code", "admin1_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_adm2_indx" ON "public"."gazetteer" USING btree ("admin2_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_adm2cntry_indx" ON "public"."gazetteer" USING btree ("country_code", "admin1_code", "admin2_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_adm3_indx" ON "public"."gazetteer" USING btree ("admin3_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_adm3cntry_indx" ON "public"."gazetteer" USING btree ("country_code", "admin1_code", "admin2_code", "admin3_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_adm4_indx" ON "public"."gazetteer" USING btree ("admin4_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_adm4cntry_indx" ON "public"."gazetteer" USING btree ("country_code", "admin1_code", "admin2_code", "admin3_code", "admin4_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_cntrycode_indx" ON "public"."gazetteer" USING btree ("country_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_feat_indx" ON "public"."gazetteer" USING btree ("feature_code") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_geoid_indx" ON "public"."gazetteer" USING btree ("gn_id") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_id_indx" ON "public"."gazetteer" USING btree ("id") WITH (fillfactor = -1);
 
CREATE INDEX "gaz_normname_indx" ON "public"."gazetteer" USING btree ("normalized_name") WITH (fillfactor = -1);

@estofilo

Merci. Relisez mon premier post et vous verrez que cela a été testé.
Ma question initiale n'etait pas tant sur la manière d'arriver a faire mes updates mais plutot sur le temps global que cela prenait.



Merci encore pour votre feed back et vos réponses

[CinePhil]

Ce morceau de structure me laisse à penser que la BDD n'est pas normalisée :

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
"admin1_names" varchar(2000) DEFAULT NULL::character varying,
"admin1_code" varchar(255) DEFAULT NULL::character varying,
"admin2_names" varchar(2000) DEFAULT NULL::character varying,
"admin2_code" varchar(255) DEFAULT NULL::character varying,
"admin3_names" varchar(2000) DEFAULT NULL::character varying,
"admin3_code" varchar(255) DEFAULT NULL::character varying,
"admin4_names" varchar(2000) DEFAULT NULL::character varying,
"admin4_code" varchar(255) DEFAULT NULL::character varying,
"admin5_names" varchar(2000) DEFAULT NULL::character varying,
"admin5_code" varchar(255) DEFAULT NULL::character varying,
"admin6_names" varchar(2000) DEFAULT NULL::character varying,
"admin6_code" varchar(255) DEFAULT NULL::character varying,
...
"adm1_gn_id" int8 DEFAULT NULL,
"adm2_gn_id" int8 DEFAULT NULL,
"adm3_gn_id" int8 DEFAULT NULL,
"adm4_gn_id" int8 DEFAULT NULL,
"adm5_gn_id" int8 DEFAULT NULL,
"adm6_gn_id" int8 DEFAULT NULL,

Surtout avec des colonnes de cette taille !

Si vous voulez des performances à cause d'un gran nombre de requêtes, commencez par normaliser votre BDD à fond.

C'est quoi "gazetteer" ?
Qu'est-censée contenir cette table ?
C'est quoi tous ces admin_code et name et admX_gn_id ?

[estofilo]

Merci. Relisez mon premier post et vous verrez que cela a été testé.

Non le 1er post parle de faire autant d'UPDATE que de codes pays et moi je parle de faire un seul UPDATE global.

Ma question initiale n'etait pas tant sur la manière d'arriver a faire mes updates mais plutot sur le temps global que cela prenait.

Le temps pris peut varier du tout au tout suivant qu'il y a des index placés où il faut et dans le cas de la méthode itérative, suivant le nombre de fois que l'update est exécuté qui n'est pas précisé (mais encore une fois la méthode itérative est la mauvaise méthode).

Ensuite pour améliorer une requête particulière, il faut utiliser EXPLAIN ou mieux EXPLAIN ANALYZE pour voir son plan d'exécution.

[SQLpro]

...
En intégrant toutes les données nécessaires a mon algo dans une seule table j'eperais gagner quelqeus millisecondes précieuses, bien entendu, tout en sachant que cela se ferait au détriment de la taille et "maintainability" de la database. Je vise un temps de réponse total de l'algo de recherche inferieur a 500ms et selon les cas, le requeteage de la grosse table mange deja plus de 300ms (sur plusieures requetes SQL bien entendu) ce qui laisse peu de place pour le reste de l'algo....

Ben oui, mais vous avez tout faux !!!!

1)
Un SGBDR travaille exclusivement en RAM. Jamais sur le disque. Pour faire une requête, les données doivent être en mémoire. Or la RAM n'est pas extensible à l'infini. En faisant sciemment de la dénormalisation sans avoir prouvé son bénéfice (ce qui est une pratique d'une haute stupidité) vous vous êtes tiré une balle dans le pied !
Autrement dit, puisque vous avez maximisé artificiellement le volume des données par le fait de votre redondance, vous aurez moins de données en mémoire que si vous aviez fait un modèle relationnel respectant la théorie...

2)
En faisant des tables longues, vous vous tirez une seconde balle dans le pied du fait des mécanismes transactionnel et des verrous sous jacent. En effet, sachant que tout ordre SQL du DML (SELECT, UPDATE, INSERT, DELETE) est une transaction et pose donc des verrous, le fait que vous avez de tables énormes avec des lignes très longues, fait que statistiquement, la pose des verrous met plus de temps et le temps de verrouillage est plus long (afin de lire les données). Alors qu'en fragmentant votre base en plusieurs tables, comme le voudrait un bon modèle bien normalisé, vous auriez des verrous plus courts, moins longs à poser et successifs !

3)
En faisant un modèle relationnel normalisé, donc de multiples tables, il est facile de créer des index performant qui aident aussi bien les lectures que les mises à jour (toute mise à, jours par INSERT, UPDATE ou DELETE commence par une lecture positionnelle). Alors que dans une seule et unique table vous ne pouvez utiliser qu'un seul index à la fois... C'est votre troisième balle dans le pied....

A +

[SQLpro]

Merci Philippe pour la réponse rapide.

Au niveau performance lequel est le plus performant en terme de rapidité d'exécution ? :

Code :

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

1
2
3
SELECT t.les_colonnes_souhaitees, p.country, p.population
FROM maTable AS t
INNER JOIN pays AS p ON t.code_pays = p.code_pays

ou

Code :

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

SELECT t.mescolonnes WHERE t.normalized_name = "+mavariable+" ;

?

Merci

Comte tenu des cardinalité, la première sans aucun doute :
entre 8 millions de lignes de n octets à scruter et 8 millions de ligne de n - p octets + 100 lignes de quelques octets, il y a une différence considérable au niveau du volume des données manipulées !

Cela s'appelle la normalisation !