[PostgreSQL] problème d'optimisation insertion

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : [PostgreSQL] problème d'optimisation insertion

kardiac

God save the kernel

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 :

CREATE VIEW su_view (server, login_account, switched_account, hour, date, count) AS
SELECT srv.server,
usr.username,
susr.username,
cs.hour,
dat.date,
cs.count
FROM server srv,
username usr,
username susr,
date dat,
consolidate cs
WHERE cs.lserver_id = srv.server_id
AND cs.luser_id = usr.user_id
AND cs.ruser_id = susr.user_id
AND cs.date_id = dat.date_id;

La rule que j'ai écrit :

Code :

CREATE OR REPLACE RULE insert_su_view AS ON INSERT
TO su_view
DO INSTEAD (
INSERT INTO server (server) VALUES (NEW.server);
INSERT INTO username (username) VALUES (NEW.login_account);
INSERT INTO username (username) VALUES (NEW.switched_account);
INSERT INTO date (date) VALUES (NEW.date);
INSERT INTO consolidate (msg_id, lserver_id, date_id, luser_id, ruser_id, hour)
VALUES ( get_msg_id('su'),
get_server_id(NEW.server),
get_date_id(NEW.date),
get_user_id(NEW.login_account),
get_user_id(NEW.switched_account),
NEW.hour);
UPDATE consolidate SET count = count + 1
WHERE consolidate.lserver_id = get_server_id(NEW.server)
AND consolidate.luser_id = get_user_id(NEW.login_account)
AND consolidate.ruser_id = get_user_id(NEW.switched_account)
AND consolidate.hour = NEW.hour
AND consolidate.date_id = get_date_id(NEW.date)
AND consolidate.msg_id = get_msg_id('su');
);

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

Code :

CREATE FUNCTION check_server () RETURNS TRIGGER AS '
DECLARE srv_count INTEGER;
BEGIN
SELECT INTO srv_count COUNT(*) FROM server WHERE server = NEW.server;
IF srv_count > 0 THEN
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER if_server_exists
BEFORE INSERT ON server FOR EACH ROW
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

Publicité

gizmo

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.

kardiac

God save the kernel

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

gizmo

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?

kardiac

God save the kernel

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.

gizmo

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?

kardiac

God save the kernel

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

gizmo

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

kardiac

God save the kernel

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.

gizmo

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.

Publicité

kardiac

God save the kernel

Ok c'est exactement ce que je voulais savoir.

Merci pour tout

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 VirtualTreeView	Oracle insertion de caractère spéciaux
Probleme de Charset sous Windows et pas sous Unix	url rewriting --> problème (avec mon serveur).
Plus de sujets relatifs à : [PostgreSQL] problème d'optimisation insertion

Page générée en 0.092 secondes