[PostgreSQL] problème d'optimisation insertion

problème d'optimisation insertion [PostgreSQL] - SQL/NoSQL - Programmation

Marsh Posté le 17-01-2004 à 15:12:30    

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);
  16.         UPDATE consolidate SET count = count + 1
  17.         WHERE consolidate.lserver_id = get_server_id(NEW.server)
  18.         AND consolidate.luser_id = get_user_id(NEW.login_account)
  19.         AND consolidate.ruser_id = get_user_id(NEW.switched_account)
  20.         AND consolidate.hour = NEW.hour
  21.         AND consolidate.date_id = get_date_id(NEW.date)
  22.         AND consolidate.msg_id = get_msg_id('su');
  24.         );


 
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

Reply

Marsh Posté le 17-01-2004 à 15:12:30   

Reply

Marsh Posté le 17-01-2004 à 15:45:22    

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.

Reply

Marsh Posté le 17-01-2004 à 15:52:05    

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

Reply

Marsh Posté le 17-01-2004 à 18:30:00    

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?

Reply

Marsh Posté le 18-01-2004 à 10:52:41    

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.

Reply

Marsh Posté le 18-01-2004 à 11:06:01    

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?

Reply

Marsh Posté le 18-01-2004 à 11:07:33    

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

Reply

Marsh Posté le 18-01-2004 à 11:26:16    

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

Reply

Marsh Posté le 18-01-2004 à 11:28:32    

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.

Reply

Marsh Posté le 18-01-2004 à 11:49:35    

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.

Reply

Marsh Posté le 18-01-2004 à 11:49:35   

Reply

Marsh Posté le 18-01-2004 à 12:11:28    

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

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed