Performance et updates de 7M d'entrees

Version imprimable

13/01/2010, 09h13
azpublic

Performance et updates de 7M d'entrees

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:

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

" ; "

2e strategie : "

Code:

SELECT id, country FROM matable;

"

puis iteration sur les resultats suivi de

"

Code:

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
13/01/2010, 09h21
CinePhil
Citation:

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

Citation:

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:

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.
13/01/2010, 10h35
azpublic
Merci Philippe pour la réponse rapide.

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

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:

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

?

Merci
13/01/2010, 11h05
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 ?
13/01/2010, 11h10
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:

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

@CinePhil :

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

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:

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:

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 :)

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

Code:

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 ?

13/01/2010, 12h05
estofilo

Citation:

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.

Citation:

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.
13/01/2010, 15h38
azpublic

@CinePhil

Non la table n'est pas normalisée, mais cela est volontaire.
Je m'explique mais tout d'abbord je répond aux autres questions:

Un gazetteer esst un dictionaire géographique

les admin_code contiennent les codes des divisions géographiques dans lesquels se trouvent la localisation géographique.

et admin_names contiennent une concaténation de noms des divisions géographiques dans lesquels se trouvent la localisation geographique.

Exemple Annecy :

coutry_code = FR

country_names =

Code:

an fhrainc;an fhraing;bro-c'hall;falanisē;falansia;farāni;faransiis;faransiiska;fasygu'e;ffrainc;frakkland;frakland;franca;frança;france;francë;francëjô;franchiya;francia;franciaország;francie;francija;francio;francja;francland;francogallia;francoska;francujo;francuska;francúzsko;frankräich;frankreich;frankriek;frankrig;fránkriika;frankrijk;frankrike;frankrish;frankryk;frans;fransa;fransän;fransia;fransiya;fransya;franţa;frantsa;frantscha;frantza;frantzia;franza;franzia;franzsa;fraunce;huák-guók;perancis;pháp;pow frynk;prancis;prancūzija;pranis;pransiya;pransya;prantsusmaa;ranska;republic of france;république française;ufaransa;vrankriek;yn rank;γαλλία;фаронса;франц;франц улс;франци;франција;франция;франція;француска;францыя;ֆրանսիա;საფრანგეთი;フランス;フランス共和国;צרפת;فرانس;فرانسه;فرانسىيە;فرنسا;ܦܪܢܣܐ;ފަރަންސޭސިވިލާތް;फ़्राँस;फ्रांस;फ़्रांस;फ्रान्स;ফ্রান্স;ફ્રાઁસ;பிரான்ஸ்;ఫ్రాన్స్*;ಫ್ರಾನ್ಸ್;ഫ്രാന്*സ്;ประเทศฝรั่งเศส;ฝรั่งเศส;ཕ་རཱན་སི།;ཕརཱནསི;ຝລັ່ງ;បារាំង;ፈረንሣይ;ፈረንሳይ;ꃔꇩ;프랑스;法国

admin1_names =

Code:

région rhône-alpes;rhône-alpes;rodan-alpy;rodano-alpeak;ródano-alpes;rodano-alpi;rodano-alpoj;rodano alpi;roine-alpes;roine-alps;ron-alpi;rona-alpe;rona-alpės;rôno-arpes;ròse aups;рона-алпи;рона — альпы;რონა-ალპები;ローヌ＝アルプ地域圏;רון-אלפ;רון-אלפס;론알프;罗纳-阿尔卑斯

admin1_code = B9

admin2_names = alta sabóia;alta saboya;alta savoia;alta savojo;alta savoya;département de la haute-savoie;haute-savoie

admin2_code = 74

admin3_name = annecy

admin3_code = 741

admin4_names = null

admin4_code = 74010

Si j'avais voulu normaliser j'aurais utilisé admin1_gn_id pour recuperer , dans cette meme table gazetteer, l'équivalent de ce que continennent admin1_names et admin1_code:

en l'occurence pour Annecy je pourrais utiliser un subselect en faisant SELECT normalized_name FROM gazetteer where gn_id = admin1_gn_id et j'obtiendrais l'equivalent de admin1_names ci dessus.

De même pour tous les autres adminX_name et adminX_code

et ainsi supprimer les colonnes adminX_name et adminX_code de la table.

Mais pour mes besoins, cela impliquerai 2 selects supplémentaire par niveau administratif soit 14 au total puisque mon applic supporte 6 subdivisions administratives + le pays !!!

J'ai bien conscience que la structure que j'ai choisi fait une belle entorse aux regles élémentaires de normalisation et va compliquer la maintenance mais je doute que du point de vue de la performance lors des requetes, les 14 subselects / jointures qu'impliquerait une structure normalisée, soient plus performantes que le rapatriement de l'ensemble des données dont j'ai besoin en un seul

Code:

SELECT mescolonnes from gazetteer where normalized_name = monTerme

mais peut-être que je me trompe :calim2: ...

@estofilo

Citation:

moi je parle de faire un seul UPDATE global

Ok merci :) je ne voyais pas les choses comme ca.
Je vais essayer de faire des benchmarks sur un subset des données de gazetteer et tester différents indexes et explain analyze. Et effectivement je me doutais que la methode iterative etait bien laide mais c'est celle qui lors de mes premiers tests avait bizarrement donné les meilleurs resultats.

Merci encore a tous les deux.
13/01/2010, 16h19
CinePhil

C'est effectivement une horreur cette structure !

Ca va t'obliger à des recherches avec des LIKE ou des index FULLTEXT qui sont probablement moins performants que les idnex sur valeur unique.

Si j'ai bien compris, il s'agit de traduire les entités géographiques en plusieurs langues ?
Je doute qu'on utilise en même temps les X langues !
L'utilisateur utilise une langue et le paramètre de langue peut être facilement récupéré pour paramétrer une requête afin de faire la jointure avec la table de traduction.

Bref... bon courage mais ça me semble mal parti !
13/01/2010, 16h53
azpublic

Non tu n'as pas tres bien compris de quoi il sagissait et il ne sagit aucunement de traduction. :)

Peu importe.

Autre chose relatif a la normalisation et à la parfois nécessaire dénormalisation des données :
[ame]http://en.wikipedia.org/wiki/Denormalization[/ame].
(pour anglophones)
Profites-en pour lire
http://www.hicss.hawaii.edu/HICSS_34/PDFs/DTDMK04.pdf

Je suis sur que tu comprendras un peu mieux pourquoi la normalisation d'une base de données n'est pas toujours la réponse a tous les problèmes.

Au fait :
Comment fais-tu pour connaitre la langue utilisée par un utilisateur dans un champs de recherche type google, avec certitude?

Citation:

Bref... bon courage mais ça me semble mal parti !

Ce n'est pas l'avis des autres chercheurs avec qui je travaille.

Bref.
Merci encore.
13/01/2010, 19h00
Oishiiii

Citation:

Envoyé par azpublic

Autre chose relatif a la normalisation et à la parfois nécessaire dénormalisation des données :
http://en.wikipedia.org/wiki/Denormalization.
(pour anglophones)
Profites-en pour lire
http://www.hicss.hawaii.edu/HICSS_34/PDFs/DTDMK04.pdf

Je suis sur que tu comprendras un peu mieux pourquoi la normalisation d'une base de données n'est pas toujours la réponse a tous les problèmes.

Ce n'est pas tout à fait ce que l'on nous explique sur le lien vers Wikipédia que vous donnez.

Citation:

This means a denormalized database under heavy write load may actually offer worse performance than its functionally equivalent normalized counterpart.

Citation:

[..] and denormalization should only take place after a satisfactory level of normalization has taken place [...]

Quant au document PDF, je n'ai accès qu'à la première page qui à aucun moment ne décrit une éventuelle "parfois nécessaire dénormalisation". Il est dommage que le document soit incomplet.

La dénormalisation, (sous toutes ses formes possible) est un sujet qui a était largement discuté sur ces forums.
A lire par exemple:
http://www.developpez.net/forums/d74...ions-avantage/
http://www.developpez.net/forums/d62...isation-table/
http://www.developpez.net/forums/d82...s-code-client/

Au final, la dénormalisation est très souvent inutile, très dangereuse pour la qualité des données et l'intégrité de la base de données.
Cela semble être dû à une méconnaissance de la normalisation et une attitude un trop "émotionnelle" face à d'éventuels problèmes de performances.

Quoiqu'il en soit pour affirmer de manière objective qu'une solution dénormalisée serait la plus performante dans votre cas il faudrait quantifier cela à l'aide d'un prototypage.
13/01/2010, 20h33
CinePhil

Citation:

Envoyé par Oishiiii

Quoiqu'il en soit pour affirmer de manière objective qu'une solution dénormalisée serait la plus performante dans votre cas il faudrait quantifier cela à l'aide d'un prototypage.

Et donc commencer par normaliser la BDD !
13/01/2010, 23h08
SQLpro
Citation:
Envoyé par azpublic

Merci Philippe pour la réponse rapide.

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

Code:

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:

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 !
13/01/2010, 23h19
SQLpro

Citation:

Envoyé par azpublic

...
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 +
14/01/2010, 02h29
fsmrel

Bonsoir,

Citation:

Envoyé par azpublic

J'ai bien conscience que la structure que j'ai choisi fait une belle entorse aux regles élémentaires de normalisation et va compliquer la maintenance mais je doute que du point de vue de la performance lors des requetes, les 14 subselects / jointures qu'impliquerait une structure normalisée, soient plus performantes que le rapatriement de l'ensemble des données dont j'ai besoin en un seul

SELECT mescolonnes FROM gazetteer WHERE normalized_name = monTerme

mais peut-être que je me trompe :calim2: ...

Un rappel qui n'est peut-être pas inutile...

Concernant l’art des bases de données, la méthode rationnelle consiste par produire d’abord un Modèle Conceptuel de Données (MCD) avec des entités-types normalisées : PAYS, REGION, DEPARTEMENT, VILLE, ARRONDISSEMENT, etc., (ou Admin1, etc.) sans oublier les associations-types qui les lient.

Ensuite, vous produisez un Modèle Logique de Données (MLD) dérivé du MCD. Les tables qui composent de MLD doivent être elles aussi normalisées.

Ensuite, vous produisez un script SQL traduisant ce MLD, et où sont décrites les structures physiques d’accueil des tables, ainsi que les index incontournables selon le SGBD (hébergement des clés par exemple). Puis vous ajoutez ceux que vous savez être absolument nécessaires pour la performance des requêtes.

Ensuite, après avoir chargé les tables et mis à jour les statistiques, vous passez aux séances de prototypage des performances des requêtes (que vous aurez pu héberger dans des vues pour « encapsuler » la difficulté et la lourdeur du code).

Si les EXPLAIN sont satisfaisants (par exemple, jointures n’entraînant pas de balayages de tables) et si malgré tout la performance n’est pas au rendez-vous, vérifiez que les données telles que les communes d’un département donné sont bien physiquement dans les mêmes pages et pas éparpillées n’importe où, car l’éparpillement est la source d’un trop grand nombre d’entrées/sorties, avant que les données ne résident en mémoire. Avec des SGBD comme DB2 ou SQL Server, le regroupement des données est facile à assurer (paramétrage), voyez ce qu’il en est avec PostgreSQL. A défaut, veillez à ce que ces données, par exemple les communes, soient chargées dans l’ordre des départements, ceux-ci (même s’il n’y a pas foule) dans l’ordre des régions et le tout à l’avenant. Discipline et méthode ne sont pas incompatibles (euphémisme...)

Et si malgré tout cela la performance n’est toujours pas au rendez-vous, commencez à « optimiser » et à dénormaliser, ce à quoi pour ma part (je touche du bois) je n’ai jamais eu à me résoudre (même avec des bases de données de deux mille tables dont certaines à quelques centaines de millions de lignes). Mais je reconnais que j’ai toujours été extrêmement vigilant quant au prototypage et au réglage de la partie physique. Il n’y a pas de secret.

Même si ça peut paraître paradoxal, mieux vaut plusieurs petites tables qu’une table unique obèse. Les SGBD dignes de ce nom sont faits pour « percuter » au niveau des jointures.

Bref, nous attendons déjà votre MCD accompagné de son MLD, dans lesquels — à titre d’exemple —, l’attribut Population aura évidemment été expulsé de la table Gazetteer, parce que la dépendance fonctionnelle

Country_code -> Population
y provoque un viol de 3NF (à moins que pour une valeur de Country_code on puisse avoir plus d’une valeur de Population).

En relation avec l’obésité de la table : supposons que l’on ait 200 pays et que la table comporte 8 millions de lignes, cela veut dire qu’en moyenne on apprendra 40000 fois que tel pays a telle population. Lors d’un UPDATE, 40000 modifications fois le nombre de pays, ça fait beaucoup, alors qu’en normalisant le coût est nul. Cela dit, il n’y a pas de raison pour qu’à lui seul ce viol fasse que l’update dure 24 heures.

Sinon, concernant les structures de la table et des index :

Mettre un peu d’ordre dans les attributs, en remplaçant par exemple les VARCHAR de faible amplitude (attributs tels que country_code) par du CHAR et en plaçant les VARCHAR restants derrière les attributs de longueur fixe. Mais, je ne vois pas de raison pour laquelle la structure actuelle serait la cause d’un update durant 24 heures.

Souvent les index sont pénalisants lors des mises à jour en masse et cela suffit à rendre celles-ci effroyablement lentes. Mais si vraiment il s’agit seulement de mettre à jour l'attribut Population, vos index sont hors de cause puisqu’aucun ne comporte cet attribut. Pour en avoir le cœur net, il faudrait exécuter l’update en les ayant d’abord tous supprimés.

Je note en passant que l’index gaz_id_indx n’est pas de type UNIQUE : je ne connais pas PostgreSQL, mais il n’y a pas de raison pour s’en dispenser.

Avec votre structure actuelle, sauf si un prototypage de performances le démontre, je ne vois pas l’intérêt a priori de la mise en œuvre de certains index. Par exemple, l’index gaz_adm4cntry_indx absorbe l’index gaz_adm3cntry_indx qui absorbe l’index gaz_adm2cntry_indx qui absorbe l’index gaz_adm1cntry_indx. Que se passe-t-il au plan de la performance des SELECT si l’on en supprime trois ?

Questions subsidiaires : Quelle est la taille de la table Gazetteer ? (en nombre de pages physiques de tant de Ko chacune) Quelle est la longueur moyenne d'une ligne de la table ? Même chose avec des tables normalisées ?
22/01/2010, 15h09
azpublic

Bonjour a tous et merci pour toutes vos réponses.

J'ai passé quelque temps a normaliser ma structure et a lancer des tests de performance afin d'avoir un point de comparaison.

La version normalisée me donnait de moins bons temps de réponse que la version dénormalisée.
: 128ms par query VS 117 pour la version denormalisée.

J'en ai donc a nouveau dénormalisé une partie, celle qui me prenait le plus de temps, a savoir les strings de noms concaténés.

J'obtiens alors des temps de réponse moyens de l'ordre de 80 ms par query ce qui est vraiment pas mal.

Merci a tous pour votre aide et vos éclaircissements !