Discussion :

[archer]

Bonjour,

J'ai besoin de mettre à jour plusieurs lignes dans une table:

Code :

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

1
2
3
4
5
6
7
contact(id,
nom,
prenom,
mobile,
tel,
email,
fax)

. la mise à jour se fait chaque fois par rapport à une colon différente ( par fois la mise à jour se fait en prenant "mobile" comme référence, par fois email ...).
Au lieux de lancer une requête pour chaque mise à jour j'ai créé une table temporaire

Code :

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

1
2
3
4
5
6
7
contact_temp(id,
nom,
prenom,
mobile,
tel,
email,
fax)

dans laquelle j’insère les contacts à jour puis je lance la requête suivante (cas où le critère d'identification est l'email):

Code :

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

update contact c, contact_temp c_t  set c.nom=c_t.nom, c.prenom=c_t.prenom, c.mobile=c_t.mobile, c.tel=c_t.tel, c.email=c_t.email, c.fax=c_t.fax where c.email=c_t.email

Le problème c'est que cette requête est trop lente surtout si le nombre de contacts à mettre à jour est de l'ordre de 20000.
Y a t il d'autres alternative ou devrais-je modifier la requête pour améliorer les performance?
BN: j'utilise java/JDBC.
Merci d'avance.

[CinePhil]

D'où proviennent les données de mise à jour ?

Si toutes les colonnes de la table temporaire sont renseignées pour chaque ligne avec le mixage adéquat des données à conserver et des données à mettre à jour, alors il faut faire la jointure sur l'identifiant et mettre à jour toute la table.

Et au passage, les jointures s'écrivent depuis 1992 avec l'opérateur JOIN !

[archer]

Merci pour la réponse.
Les données proviennent d'un fichier CSV, La mise à jours ne s’effectue pas sur toutes les données. et l'identifiant varie selon le choix de l'utilisateur.
Concernant votre réponse, pouvez vous m'expliquer un peu plus votre idée?

[CinePhil]

Peux-tu être plus précis ?

Les données proviennent d'un fichier CSV,

La structure du fichier et quelques lignes exemples avec des données anonymisées aideraient donner la solution.

La mise à jours ne s’effectue pas sur toutes les données.

Veux-tu dire que :
1) Toutes les lignes de la table ne sont à mettre à jour ?
2) Des colonnes du fichier CSV sont vides pour certaines lignes ?

et l'identifiant varie selon le choix de l'utilisateur.

Là je ne comprends plus ! Qu'est-ce que l'utilisateur vient faire là dedans ?

[archer]

Tout d'abord merci pour l'intérêt que vous portez à mon problème.
En fait l'application permet aux utilisateurs d’insérer des contacts puis de les mettre à jour en cas de besoin.
chaque contact fait référence à l'utilisateur qui l'a insérer (relation père fils entre les tables user et contact)
parfois l'utilisateur veut apporter des mises à jour à sa liste de contacts. donc il sélectionne le fichier CSV qui contient les contacts à jour. le fichier ressemble à ça:

Code :

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

1
2
nom;prenom;mobile;tel;email;fax
kim;archer;xxx;yyy;a@gmail.com;zzz

pour mettre à jour un contact, l'utilisateur ne connait pas les identifiants attribué par notre base de donnée à chaque contact. Donc on lui laisse le libre choix de définir l'un des champs: mobile,tel, email ou fax comme identifiant (puisqu'il est unique) du contact.
Si je ne suis pas bien exprimé, veuillez me le mentionner.

[CinePhil]

C'est là que ça devient bizarre ton truc !

donc il sélectionne le fichier CSV qui contient les contacts à jour.

Pourquoi utiliser un fichier CSV alors que tu as une BDD ?

Les contacts de l'utilisateur sont bien enregistrés en BDD ?
Donc ton interface utilisateur doit simplement interroger la BDD et permettre de modifier un contact ! Classique quoi ! Je ne vois pas pourquoi tu t'embêtes avec un fichier CSV ! Il vient d'où ? Généré par quoi ?

[archer]

Bon, oubliant le fichier CSV.
J'ai deux tables: contact et contact_temp. je veux mettre à jour les contacts contenues dans la table contact à partir de la table contact_temp avec une requête optimisée qui ne consomme pas beaucoup de temps. J'ai essayé

Code :

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

UPDATE contact c, contact_temp c_t  SET c.nom=c_t.nom, c.prenom=c_t.prenom, c.mobile=c_t.mobile, c.tel=c_t.tel, c.email=c_t.email, c.fax=c_t.fax WHERE c.id=c_t.id

mais elle est très lente.

[CinePhil]

Tes tables sont correctement indexées bien sûr ?

[gene69]

est -ce que la table temporaire contient toutes les données ou seulement une partie des données?

parce que dans ce cas, il y a peut-etre moyen de jouer sur le nom des tables (C'est vrai que les contraintes n'aimeront pas ça)..

apres CinePhil a raison, si tes clées primaires sont sur id.. tu peux lancer quelques opérations de tris avant l'update, mais ya pas de raison que...

C'est quoi pour toi une requête lente?

[archer]

La table contact_temp peux contenir certaines données à mettre à jour mais sa reste comme même énorme si par exemple on veux mettre à jour 20000 contacts alors que la table contact contient 1 ou 2 millions.
Le problème c'est qu'on est contraint de mettre parfois

Code :

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

WHERE c.id=c_t.id

comme je peux mettre

Code :

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

WHERE c.mobile=c_t.mobile

ou

Code :

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

WHERE c.email=c_t.email

Poux l'indexation j'ai dû indexer les colonnes id, mobile, email, tel et fax. mais ça reste toujours lent

[gene69]

il va falloir se mettre d'accord: Si tu identifies tes données via la clé primaire, normalement c'est le meilleur index et le plus performant pour la mise a jour (vu que les données sont rangées par la clé primaire).

pourquoi tu dis que tu peux avoir plusieurs conditions de WHERE possible? Est-ce que tu n'aurais pas des données qui doublonnent dans tous les sens?

apres les indexes et le FK, il faut voir, en théorie mettre des bcp l'indexes différents ralenti les écritures des données au profit de la lecture.

[archer]

pourquoi tu dis que tu peux avoir plusieurs conditions de WHERE possible? Est-ce que tu n'aurais pas des données qui doublonnent dans tous les sens?.

ce sont des contraintes de l'application. Je dois donner la possibilité de mettre à jour les contacts à partir du numéro de téléphone fixe ou mobile ou email ou fax.
C'est pour cette raison que j'ai besoin d'utiliser une requête qui soit la plus optimisée.

[gene69]

oui mais non.

J'avais compris que ta table temporaire contient toutes les données "à jour" avec leur clée primaire. Donc pour les recopier tu n'aurais besoin que de la clé primaire.

sinon le reste se tiens... je sais pas quoi te proposer.

[archer]

En tout cas, merci pour l'intérêt et l'effort que vous avez fournit pour comprendre mon problème.

[CinePhil]

J'ai quand même du mal à piger le processus de ton machin !

D'abord, tu sembles nous parler de mise à jour en masse, comme le suggère le titre de ton premier message :

J'ai besoin de mettre à jour plusieurs lignes dans une table:
...
Les données proviennent d'un fichier CSV

Puis le processus semble plutôt correspondre à une mise à jour des contacts un par un :

pour mettre à jour un contact, l'utilisateur ne connait pas les identifiants attribué par notre base de donnée à chaque contact. Donc on lui laisse le libre choix de définir l'un des champs: mobile,tel, email ou fax comme identifiant (puisqu'il est unique) du contact.

La manière classique pour un utilisateur de gérer ses contacts est à mon avis plutôt celle-ci :
1) => L'utilisateur demande l'affichage de la liste de ses contacts.
2) <= Le système interroge la BDD et affiche la liste avec les identifiants masqués.
3) => L'utilisateur sélectionne un contact et demande l'affichage de son détail.
4) <= Le système utilise l'identifiant masqué du contact choisi et interroge la BDD pour afficher le détail du contact.
5) => L'utilisateur met à jour de une à plusieurs données du contact et demande l'enregistrement du contact.
6) <= Le système récupère toutes les données du formulaire et met à jour le contact en BDD (encore grâce à son identifiant caché) puis affiche un message de succès à l'utilisateur.

La mise à jour en masse correspondrait plutôt au processus suivant :
1) <= Extraction de la liste des contacts de l'utilisateur dans un fichier CSV lisible par un tableur, y compris l'identifiant de chaque contact qui est cette fois-ci visible par l'utilisateur.
2) => L'utilisateur modifie le fichier avec son tableur préféré, l'enregistre et redonne le fichier à celui qui a les droits pour le réinjecter dans la BDD.
3) => Importation du fichier dans une table temporaire.
4) => Mise à jour de la table des contacts à partir de la table temporaire en faisant une jointure sur l'identifiant.

Quel est ton processus ?

[archer]

Bonjour,
le deuxième processus: La mise à jour en masse.

[CinePhil]

Et donc pourquoi ne pas faire la jointure sur l'identifiant ?

[archer]

comment faire?

[CinePhil]

Ben c'est ta requête, modifiée avec les jointures normalisées et remise en forme, ça donne ça :

Code :

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

1
2
3
4
5
6
7
8
UPDATE contact c
INNER JOIN contact_temp c_t ON c.id = c_t.id 
SET c.nom=c_t.nom, 
    c.prenom=c_t.prenom, 
    c.mobile=c_t.mobile, 
    c.tel=c_t.tel, 
    c.email=c_t.email, 
    c.fax=c_t.fax

[peufeu]

On dirait que pour une raison quelconque, ton fichier CSV ne peut pas contenir l'id. Donc tu utilises une autre clé (email, téléphone, etc).

Ton problème est que dans ce cas il faudrait faire un merge join ou un hash join entre les deux tables, mais MySQL ne connaît pas ces opérations (contrairement à tous les autres SGBDR open source quoique j'aie un doute sur sqlite).

Donc il te faut absolument un index sur le champ que tu utilises comme clé.

Tu peux créer l'index sur la table principale ou sur la table temporaire, cela ne fait aucune différence. A toi de voir.

Démonstration :

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
 
DROP TABLE one;
DROP TABLE two;
 
CREATE TABLE one ( 
 id INT PRIMARY KEY AUTO_INCREMENT, 
 a INT, 
 b INT );
 
INSERT INTO one (a) SELECT n*10 FROM serie LIMIT 2000;
 
CREATE TABLE two (
 a int, 
 b int 
 ); 
 
INSERT INTO two (a,b) SELECT n*10,n FROM serie LIMIT 1000;
 
-- CREATE INDEX two_a ON two(a);
 
UPDATE one,two SET one.b=two.b WHERE one.a=two.a;
 
-- sans le CREATE INDEX
Query OK, 1000 rows affected (2.35 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0
 
-- avec le CREATE INDEX
Query OK, 1000 rows affected (0.02 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

Avec un index on passe de 2.3s à 0.02s, mais attention, comme le temps sans index est en O( lignes dans la table 1 * lignes dans la table 2 ) cela peut tout à fait prendre une semaine si tes tables sont bien grosses.

Avec une BDD qui maîtrise le hash join c'est une autre histoire, l'index est superflu dans ce scénario :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
EXPLAIN ANALYZE UPDATE one SET b=two.b FROM two WHERE one.a=two.a;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=27.50..74.00 rows=1000 width=18) (actual time=0.532..2.038 rows=1000 loops=1)
   Hash Cond: (one.a = two.a)
   ->  Seq Scan on one  (cost=0.00..29.00 rows=2000 width=14) (actual time=0.008..0.602 rows=2000 loops=1)
   ->  Hash  (cost=15.00..15.00 rows=1000 width=8) (actual time=0.488..0.488 rows=1000 loops=1)
         ->  Seq Scan on two  (cost=0.00..15.00 rows=1000 width=8) (actual time=0.018..0.238 rows=1000 loops=1)
 Total runtime: 9.634 ms

Donc, indexe !

Personnellement, je mettrais par précaution des index UNIQUE sur la table temporaire : cela permet de vérifier que le fichier csv ne contient pas de doublon.

De plus si tu as un moteur de recherche, tu dois indexer ta table principale.

Enfin, il n'y a aucun moyen de garantir que les clés que tu utilises (téléphone, email...) soient uniques dans ta table principale, si tu n'as pas posé de contrainte UNIQUE dessus (ce qui créé un index). Donc, ajoute les immédiatement.