Forums PostgreSQL.fr

ilrico

Membre

Meilleure methode pour mettre a jour une table de correspondance

bonjour,

c'est un problème trivial, mais je ne suis pas certain que les réponses aux questions soient aussi triviales que je le suspecte.

j'ai un univers de catégories, pour simplifier 10, numérotées de 1 à 10.
pour chaque utilisateur, son choix est stocké dans une table de "mapping" à deux colonnes (user_id, cat_id)
pourquoi ? car l'univers de catégories est variable, donc je stocke ses préférences ici et non pas dans la table users ou chaque catégories (en colonne) aurait un flag booleen.

ainsi pour l'utilisateur 25 qui a choisi 3 catégories (2,3,8) j'aurais trois lignes
user_id cat_id
25 2
25 3
25 8

supposons que l'utilisateur change son choix de catégories à (2, 3, 9, 10)

la méthode la plus simple est d'effacer les corespondances existantes pour l'utilisateur 25 et faire 4 INSERT avec les nouvelles:

DELETE FROM prefs WHERE user_id = 25;
INSERT INTO prefs VALUES (25, 2);
INSERT INTO prefs VALUES (25, 3);
INSERT INTO prefs VALUES (25, 9);
INSERT INTO prefs VALUES (25, 10);

soit 5 requetes

j'ai 2 questions (en partant du principe que cette table aura quelques dizaines milliers d'utilisateurs, soit en gros quelques centaines de milliers de lignes) :

- en terme "SQL", il y a-t-il plus simple ?
- sur une table de cette taille, la méthode "DELETE" puis "INSERT" présente-elle des inconvénients à long terme sur les performances de la table ?

une autre méthode, faire la différence entre le mapping actuel et le cible (ie enlever le 25,8 pour ajouter le 25,9 et 25,10) me semble beaucoup plus lourd
(en PHP cela donnerait :
$mapdb = tableau du resultat SELECT cat_id FROM prefs WHERE user_id = 25;
$mapcible = tableau des nouvelles préférences
$remove = array_diff($mapdb, $mapcible);
$add = array_diff($mapcible, $mapdb);
boucle de DELETE FROM prefs WHERE user_id = 25 AND cat_id = $remove[$i]
boucle de INSERT INTO prefs VALUES (25, $add[$i])
)

merci,

Dernière modification par ilrico (08/05/2011 18:54:00)

gleu

Administrateur

Re : Meilleure methode pour mettre a jour une table de correspondance

en terme "SQL", il y a-t-il plus simple ?

Oui, faire un INSERT multiple. Si on suit ton exemple :

DELETE FROM prefs WHERE user_id = 25;
INSERT INTO prefs VALUES (25, 2), (25, 3), (25, 9), (25, 10);

sur une table de cette taille, la méthode "DELETE" puis "INSERT" présente-elle des inconvénients à long terme sur les performances de la table ?

Tout dépend de la fréquence de mise à jour. Ce sera certainement une table qui sera à prendre en compte pour un VACUUM plus fréquent.

faire la différence  entre le mapping actuel et le cible (ie enlever le 25,8 pour ajouter le 25,9 et 25,10) me semble beaucoup plus lourd.

Au niveau applicatif, oui, sans aucun doute (ce n'est pas non plus super compliqué à faire). Au niveau SQL, ça n'est qu'un gros gain.

---

Guillaume.

ilrico

Membre

Re : Meilleure methode pour mettre a jour une table de correspondance

ok j'ai mes réponses, claires & précises.

Dernière question : en terme de performance, sur disons 15 inserts, l'insert multiple est-il vraiment un plus ou c'est plus une simplification syntaxique (ie PostgeSQL décompose-t-il la synthaxe de l'insert multiple en une série d'insert simples (pas de gain de perf dans ce cas) ou optimise-t-il vraiment ?)

merci

Dernière modification par ilrico (08/05/2011 19:26:04)

Marc Cousin

Membre

Re : Meilleure methode pour mettre a jour une table de correspondance

On peut quand même le faire au niveau SQL, de façon pas vraiment plus compliquée que l'insert multiple (par exemple pour mettre 1,2,3,4):

DELETE FROM prefs WHERE user_id = 25 AND cat_id NOT IN (1,2,3,4);

INSERT INTO prefs SELECT * FROM 
    (VALUES (25,1),(25,2),(25,3),(25,4) 
  EXCEPT 
    SELECT user_id,cat_id FROM prefs) AS tmp;

Mais je suis aussi partisan de la méthode simple et lisible précédente.

---

Marc.

gleu

Administrateur

Re : Meilleure methode pour mettre a jour une table de correspondance

Ça optimise vraiment. Une seule transaction (donc une seule synchro disque), au lieu de 3 dans votre cas.

---

Guillaume.

Marc Cousin

Membre

Re : Meilleure methode pour mettre a jour une table de correspondance

Ce n'est pas le fait de la transaction qui fait gagner du temps, sinon un simple begin/commit encadrant les 4 inserts aurait les mêmes performances. On gagne surtout sur 2 choses avec l'insert multiple:
- Un seul ordre SQL à analyser (et donc un seul plan d'exécution à construire)
- Un seul dialogue client serveur

---

Marc.

ilrico

Membre

Re : Meilleure methode pour mettre a jour une table de correspondance

ok très clair à nouveau
merci & bonne soirée

SQLpro

Membre

Re : Meilleure methode pour mettre a jour une table de correspondance

- en terme "SQL", il y a-t-il plus simple ?

Oui, il suffirait de faire un MERGE qui permet de combiner INSERT et/ou UPDATE, DELETE...
Malheureusement cela n'est pas encore implémenté sous PostGreSQL (encore une fonctionnalité de la norme en avance sur les réalisations.... ;-))

De toutes façon, si votre table est organisé en cluster il y a des chances que toutes les lignes manipulées figurent dans la même page de données. De ce fait il n'y aura donc qu'une seule écriture physique !

A +

Dernière modification par SQLpro (10/05/2011 21:17:46)

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *