INSERTS/UPDATES are historically slow especially with autocommit is on (implied autocommit on) the Database writer actually stops any processing and applies that one record to the database Most bulk operations such as import/export and copy are well worth their weight as they apply en-masse before any commit ..remember the DB actually stops flushes its buffers to Disk and then resumes..the only solution here is to disable autocommit but be wary you may have 100's of statements waiting to be commited and then someone does a quit on your session..all your work is lost
good call on copy http://www.postgresql.org/docs/8.1/interactive/populate.html cheers, Martin Gainty ______________________________________________ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Mon, 28 Sep 2009 21:52:36 +0100 > From: s...@samason.me.uk > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] bulk inserts > > On Mon, Sep 28, 2009 at 10:38:05AM -0500, Dave Huber wrote: > > Using COPY is out of the question as the file is not formatted for > > that and since other operations need to occur, the file needs to be > > read sequentially anyway. > > Just to expand on what Martin said; if you can generate a set of EXECUTE > commands, you can certainly generate a COPY command to insert the same > data. The advantage is a large drop in parse time for inserting larger > numbers of rows. As you're saying you want to insert 500 rows, I'd > suggest at least trying to get COPY working. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _________________________________________________________________ Bing™ brings you maps, menus, and reviews organized in one place. Try it now. http://www.bing.com/search?q=restaurants&form=MLOGEN&publ=WLHMTAG&crea=TEXT_MLOGEN_Core_tagline_local_1x1
