Discussion :

[nine]

bonjour

je n'arrive pas a faire un update de table a partir d'une requete sur une 2eme table.

table 1 :
x,y,i_pr,ii_pr,iii_pr ...xii_pr
x et y coordoonnes point
i_pr : pluvio pour mois de janvier
ii_pr : pluvio pour mois de fevrier
...

table 2 :
mois,x,y,pr

en sachant que : il y 550 000 points identiques / mois

je voudrais faire un update sur la table 1 de maniere à mettre a jour le champs pr se trouvant dans table 2 pour chaque mois et chaque point
exemple pour le mois de janvier mettre a jour la table 1 col i_pr a partir de la table 2 quand le mois = 1 et ceci pour tous les points identiques
j'ai essaye ca :

Code :

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

1
2
3
4
5
6
update table1 
set ii_pr = (
  select pr 
  from table2 
  where mois=2 and x=table1.x and y= table1.y
)

mais ca tourne depuis maintenant 2300000 ms env. ....
table2 comprend plus de 7 million de lignes et table1 comprend 550 000 env

j'ai peur du produit cartesien !

merci d'avance!
n

[pacmann]

Salut !

Ton SGBD ?
As-tu des indexes sur la Table2 ?

[nine]

hello

je sous postgresql/postgis
pas d'index....

[pacmann]

Pose un index sur la table2, colonnes x, y, mois.
(par exemple)

[Waldar]

Vous n'avez pas mis de where au niveau de votre update... donc il va mettre à jour toute la table table1, en mettant null quand il ne trouve pas de correspondance.

[Waldar]

D'ailleurs en relisant votre sujet, vous mettez à jour toute la table 1 à partir de la table 2 ?

Si oui, tronquez votre table1 et insérez le select qui va bien depuis la table2, ça ira nettement plus vite.

[nine]

hola tu es un ange!!
j'ai créé un index sur x et y : resultat : update de 551716 lignes en moins d'une minutes!

encore merci,
n

[nine]

waldar

ma table 1 comporte env 550 000 lignes que je vais updater a coup sur sur le champs ii_pr qui correspond au champs pr de la table 2 et mois = 2 pour chaque x et y correspondant.

d'ailleurs je sollicite a nouveau un avis :
au depart j'ai une grosse table :
mois, x , y , pr

avec pour mois=1 : 550 000 point x,y avec val pr different
...pour les 12 mois de l'année, j'ai donc 550 000 points x,y qui se redondent pour tous les mois.

j'ai donc créé la table 2 avec la structure :
x, y, i_pr, ii_pr, iii_pr ....xii_pr
ou je ne stocke finalement qu'une seule fois mes 550 000 points x,y (que j'ai effectievement intégré avec insert select )

mon but est de mettre a jour tous les mois ii_pr, iii_pr, ... a partir de la tabel 1

voila , je prend toutes les bonnes idees!
merci,

n

[Yanika_bzh]

J'avoue ne pas bien comprendre ce que vous avez exactement dans votre base

Pour chaque mois vous avez 550 000 lignes redondantes ???

[pacmann]

Yaki => c'est pas vraiment redondant : ce sont des points de mesure différents. Ce qui est redondant, c'est que c'est répété pour chaque moi.

Waldar => Vous êtes également un fan des méthodes barbares à la Tom Kyte
Mais je pense que dans ce cas, on fait carrément CTAS de la requête, drop de la table, puis rename...

Le seul truc, c'est que pour construire la requête, il faut le faire en récursif (puisqu'il y a une colonne par moi)

nine => je voooollle

[Waldar]

Oui et non car le nombre de mois par an ne devrait pas trop varier dans l'année.

Je ferai effectivement ceci à la place de nine :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
truncate table table1;
 
insert into table1 (x, y, i_pr, ii_pr, ... xii_pr)
select
    x, y,
    sum(case mois when 1 then pr else 0 end) as i_pr,
    sum(case mois when 2 then pr else 0 end) as ii_pr,
...
    sum(case mois when 12 then pr else 0 end) as xii_pr,
from table2
group by x, y
order by x, y;

Vu que vous faites au final un full scan de la table2, l'index sur table2 devient inutile pour ce chargement.

Si votre SGBD le supporte vous pouvez aussi utiliser l'instruction PIVOT.

[Yanika_bzh]

Truncate table est a proscrire !! d'ailleurs je ne pense pas que son administrateur lui donne le droit de le faire. Cela by pass les journaux de log et de transactions, en cas de plantage durant la transaction la base est a mettre a la poubelle !!

[Waldar]

Ce qui ne pose aucun problème vu que Table1 n'est qu'une modification visuelle de Table2.

[pacmann]

Avec le CTAS, tu ne fais le DROP que si le premier à réussi

[nine]

yanika : peut importe le truncate, pour l'instant je suis sur machine, je cherche la façon la moins couteuse pour restructurer ma table

waldar : un classique que je ne connaissais pas !
est ce couteux/jouable sur des grosses tables ?

j'ai rapidement regarde sur le net, posgresql ne gere pas pivot mais utilise crosstab, je vais faire des tests

Code :

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

1
2
 
Vu que vous faites au final un full scan de la table2, l'index sur table2 devient inutile pour ce chargement.

je ne suis pas sure de comprendre...en faisant comme disait pacmann un index sur x et y , j'ai reellement constaté la différence
je crois que je n'ai jamais vraiment compris les index...

merci a tous, a+
n

[Yanika_bzh]

Pacman vous a proposé de poser des indexes au vu de votre requete qui comporte une clause WHERE.

Waldar vous propose une solution de recreation de table, dans laquelle aucun clause WHERE n'est utilisée, d'ou le full scan sur la table, d'ou sa remarque de non necessité d'index.

@Weldar : les instructions nons loggées ou faiblement loggées n'ont pas une portée limitée sur une table ou une cession concerant le journal de log.
En cas de crash de votre base avant un checkpoint, le recovery de données va etre extrement périlleux apres l'utilisation d'un instruction non loggée.
A vos risques et perils ...

[nine]

merci yanika pour l'eclairage ;-)

j'ai essayé la façon waldar...ça marche super bien --> sur la table de 7 millions de lignes la requete s'est executé en 176093 ms!

c super je vous remercie vraiment tous beaucoup du coup de main.

j'ai lu encore qu'on pouvait faire du crosstab voire utiliser cube, qu'en pensez-vous ? qqu'un l'a t'il deja expérimenté ?

a+
n

[Waldar]

Pour être plus complet et éviter le truncate, il faut prendre en compte que Table1 n'est qu'une "mise en page" de Table2, soit en quelque sorte une vue.

Aussi, il vaudrait peut-être mieux créer Table1 comme étant une vue matérialisée de Table2.

Les vue matérialisées (vues indexées en SQL Server) existent bien sous PostgreSQL : http://jonathangardner.net/tech/w/Po...rialized_Views

[nine]

bonjour,

je travaille toujours sur le meme projet, ca avance... et je dirai même ca avance grace a vous et a votre aide, alors merci.

je bute a nouveau sur un probleme :

maintenant que les donnees sources sont integrees dans mes tables
je souhaite croiser les donnees user aves ces donnees sources

le schema est :

une table A : mois, x, y, i_val, ii_val, ...., xii_val
une table B : mois, x, y, i_val, ii_val, ...., xii_val
une table C : mois, x, y, i_val, ii_val, ...., xii_val
...

un utilisateur upload un fichier et apres verif je l'integre dans une table :

copy tmp_user_data from 'le fichier de l'utilisateur' using ...

avec tmp_user_data : x,y

ensuite je fais un calcul sur x et y pour trouver le point le plus proche (d'un maillage au pas de 1km sur la carte de la France env 550000 mailles)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE tmp_user (
  id serial,
  x_ori double precision,
  y_ori double precision,
  x_cal double precision,
  y_cal double precision  
);
 
--calcul du point le plus proche
INSERT INTO tmp_user (x_ori,y_ori,x_cal,y_cal) SELECT x,y, round(x/10)*10 , round(y/10)*10 from tmp_user_data;

me voila donc avec une table des données de l'utilisateur qui comporte les coordonnées d'origine et les coordonnées calculees pour tomber sur la maille la plus proche.

je voudrais croiser ma table tmp_user avec chacune de mes tables A, B, et C

ce que j'ai fais :

Code :

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

1
2
3
4
create table resultat as 
select b.x_ori,b.y_ori, b.x_cal, b.y_cal, A.i_val, ..., A.xii_val, B.i_val, ...B.xii_val,... 
FROM tmp_user b, A,B, C 
where A.x=b.x_cal and A.y=b.y_cal ...

ceci me retourne tous les points x,y en concordance et je remonte bien les valeurs voulues de chacune des A, B et C pour les points x,y de l'utilisateurs.

mais....je voudrais egalement garder les poitns x,y pour lesquels je n'ai pas de concordance car je dois faire un calcul de distance sur chacun d'eux
je dois aussi garder l'ordre des poitns de l'utilisateur....
est-ce possible ?

le calcul de distance a faire pour chaque poitn non trouve est :
racine carrée((x2-x1)²+(y2-y1)²)

merci d'avance de votre aide
n