Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
1447 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [PostgreSQL] problème d'optimisation insertion

 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

[PostgreSQL] problème d'optimisation insertion

n°614379
kardiac
God save the kernel
Posté le 17-01-2004 à 15:12:30  profilanswer
 

Bonjour,
       
     Tout d'abord je tiens à préciser que je débute en SQL donc  
mes lignes de codes sont peut-être complètement foireuses et je prend peut-être pas le problème de la bonne façon. Hésitez pas à me le dire ;)
 
Ceci dit, voici mon problème: j'ai créé une rule pour pouvoir insérer facilement des données en passant par une vue. Sur les différentes tables, j'ai créé des triggers pour vérifier que le champ est bien unique. Le problème c'est que j'insére plusieurs milliers de ligne et je trouve que cela met trop de temps. De l'ordre de 20 minutes pour 1500 inserts. Donc si quelqu'un pouvait m'indiquer comment optimiser tout ca ou me donner une piste.
 
Voici la vue

Code :
  1. CREATE VIEW su_view (server, login_account, switched_account, hour, date, count) AS
  2.         SELECT srv.server,
  3.                 usr.username,
  4.                 susr.username,
  5.                 cs.hour,
  6.                 dat.date,
  7.                 cs.count
  8.         FROM server srv,
  9.                 username usr,
  10.                 username susr,
  11.                 date dat,
  12.                 consolidate cs
  13.         WHERE cs.lserver_id = srv.server_id
  14.         AND cs.luser_id = usr.user_id
  15.         AND cs.ruser_id = susr.user_id
  16.         AND cs.date_id = dat.date_id;


 
La rule que j'ai écrit :

Code :
  1. CREATE OR REPLACE RULE insert_su_view AS ON INSERT
  2.         TO su_view
  3.         DO INSTEAD (
  4.         INSERT INTO server (server) VALUES (NEW.server);
  5.         INSERT INTO username (username) VALUES (NEW.login_account);
  6.         INSERT INTO username (username) VALUES (NEW.switched_account);
  7.         INSERT INTO date (date) VALUES (NEW.date);
  8.         INSERT INTO consolidate (msg_id, lserver_id, date_id, luser_id, ruser_id, hour)
  9.         VALUES ( get_msg_id('su'),
  10.                  get_server_id(NEW.server),
  11.                  get_date_id(NEW.date),
  12.                  get_user_id(NEW.login_account),
  13.                  get_user_id(NEW.switched_account),
  14.                  NEW.hour);
  15.         UPDATE consolidate SET count = count + 1
  16.         WHERE consolidate.lserver_id = get_server_id(NEW.server)
  17.         AND consolidate.luser_id = get_user_id(NEW.login_account)
  18.         AND consolidate.ruser_id = get_user_id(NEW.switched_account)
  19.         AND consolidate.hour = NEW.hour
  20.         AND consolidate.date_id = get_date_id(NEW.date)
  21.         AND consolidate.msg_id = get_msg_id('su');
  22.         );


 
Ainsi qu'un couple de fonctions et de triggers que j'utilise :

Code :
  1. CREATE FUNCTION check_server () RETURNS TRIGGER AS '
  2.         DECLARE srv_count INTEGER;
  3.         BEGIN
  4.                 SELECT INTO srv_count COUNT(*) FROM server WHERE server = NEW.server;
  5.                 IF srv_count > 0 THEN
  6.                 RETURN NULL;
  7.                 ELSE
  8.                 RETURN NEW;
  9.                 END IF;
  10.         END;
  11.         ' LANGUAGE 'plpgsql';
  12. CREATE TRIGGER if_server_exists
  13.         BEFORE INSERT ON server FOR EACH ROW
  14.         EXECUTE PROCEDURE check_server ();

 
 
J'ai bien pensé à supprimer les triggers en me contentant des clés primaires comme cela ca générerait juste une erreur lors de l'insert dans la table mais ca ne fonctionne pas non plus :fou:
J'ai l'impression que la rule s'arrète lorsqu'il y a une erreur.  
Je me suis aussi renseigné sur la gestion des exceptions mais il parait que c'est pas encore tout à fait au point sous PostgreSQL. Ma base est en 7.3.4.  
 
Je suis ouvert à toutes propositions :sweat:
 
Merci d'avance,
 
Kardiac

mood
Publicité
Posté le 17-01-2004 à 15:12:30  profilanswer
 

n°614385
gizmo
Posté le 17-01-2004 à 15:45:22  profilanswer
 

bah, on fait, tu fais mal ton calcul. 1500 inserts dans ta vue, ca correspond à (1 rules 5 insert + 1 update + 1 trigger) * 1500. Soit 12000 opérations dont les triggers et les rules qui sont très lourds (surtout les triggers). Et si tu as autant de trigger que tu as de table, ce n'est pas étonnant que tes performances s'écroulent.

n°614387
kardiac
God save the kernel
Posté le 17-01-2004 à 15:52:05  profilanswer
 

Oui je ne suis pas trop surpris par les perfs que j'obtiens par ma méthode. J'ai surtout montrer le code SQL pour qu'on me dise si j'ai écrit une grosse bétise.
 
Mais je cherche surtout une méthode plus rapide. J'aimerais en fait me passer des triggers pour utiliser les clés primaires. Ce qui dans mon cas, améliorerait grandement les performances à mon avis. Mais ca ne fonctionne pas du tout quand je ne mets pas les triggers. J'ai l'impression que la rule s'arrète à la première erreur....

n°614468
gizmo
Posté le 17-01-2004 à 18:30:00  profilanswer
 

oui, elle s'arrète et c'est normal vu que la clef primaire renvoie une erreur. Mais que veux-tu faire exactement? pourquoi remplaces-tu tes doublons par des null?

n°614726
kardiac
God save the kernel
Posté le 18-01-2004 à 10:52:41  profilanswer
 

Oups je pensais que retourner NULL arréter l'insertion... En fait ce que je veux c'est qu'il n'insère rien dans la table si la valeur existe déjà. Et ce pour chacune de mes insertions. Je voudrais en fait que la rule continue à faire toutes les insertions sans tenir compte des erreurs en fait.

n°614731
gizmo
Posté le 18-01-2004 à 11:06:01  profilanswer
 

ah, ok. Dans ce cas, que considères-tu comme une erreur? L'ajout d'un tuple complet dans la vue ou juste l'ajout de valeurs doublons dans les tables?

n°614732
kardiac
God save the kernel
Posté le 18-01-2004 à 11:07:33  profilanswer
 

L'ajout de valeurs doublons dans les tables. En fait dans certaines tables pour être précis.

n°614740
gizmo
Posté le 18-01-2004 à 11:26:16  profilanswer
 

ok. Et comment sont stockées les valeurs qui doivent être insérées dans la vue?

n°614741
kardiac
God save the kernel
Posté le 18-01-2004 à 11:28:32  profilanswer
 

En fait je parse un fichier syslog avec un petit script perl pour récupérer mes infos. J'utilise le module DBI pour ca.

n°614750
gizmo
Posté le 18-01-2004 à 11:49:35  profilanswer
 

ah. Dans ce cas, si c'est de tout façon un traitement automatisé, je ne vois pas trop l'intérêt de faire la vue, et surtout la règle que tu mets dessus.
 
Surtout que je n'avais pas fait bien attention à ta règles, mais le update est totalement redondant avec le dernier insert et pourrait être inclus dedans.
 
Voici un canevas que je te propose, c'est ce que l'on utilise +- au boulot quand on doit insérer 1 millon d'objets à la fois par batch (soit un peu plus de 12 millons d'insert en moyenne).  
On stocke toutes les valeurs extraites avec le script perl dans des tables temporaires, ca ca va assez vite, surtout si tu ne met les indexs qu'après (si nécessaire).
Pour l'insertion, tu va déjà pour utiliser un insert sur un select distinct dans ta table temporaire que tu peux coupler avec une jointure externe sur la table final, ce qui te permet de ne choisir que les valeurs qui ont un pendant NULL dans la table final.
Maintenant, si tu as des volumes plus importants que ceux que tu me cites, cela risque de devenir un peu trop lourd comme mécanisme. Chez nous, on utilise toute une série de filtres intermédiaire rapides qui permettent de jeter tout un tas de mauvaises valeurs.
 
Deux derniers petits conseils: utilise les jointures explicites plutôt que les clauses WHERE, c'est plus rapide avec la 7.4 (pour quand tu upgraderas) et n'oublie pas les vacuum sur les tables temporaires si tu choisi cette technique.

mood
Publicité
Posté le 18-01-2004 à 11:49:35  profilanswer
 

n°614765
kardiac
God save the kernel
Posté le 18-01-2004 à 12:11:28  profilanswer
 

Ok c'est exactement ce que je voulais savoir.
 
Merci pour tout


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [PostgreSQL] problème d'optimisation insertion

 

Sujets relatifs
[JDBC] problème d'encodage de caractères.problème de socket client --> bloquantes ou pas
Problème de "!" dans un mail envoyé en php[java swing] probleme de taille avec un JTextField
[PostgreSQL] - Recup l'id du dernier enregistrement dans ma fct[XML/XSL]génération d'un XML a partir d'un XML par XSL probleme
Problème install VirtualTreeViewOracle insertion de caractère spéciaux
Probleme de Charset sous Windows et pas sous Unixurl rewriting --> problème (avec mon serveur).
Plus de sujets relatifs à : [PostgreSQL] problème d'optimisation insertion


Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR