On Wed, Nov 23, 2005 at 12:07:01PM +0100, A.j. Langereis wrote: > Dear Martijn, > > The problem with your solution is that the script is meant to process a > log-file real-time. > Therefore the insert should be done immediately, however it is the same > statement over and over agian, just with different parameters i.e. an ideal > case fore PREPARE. But it will be done in real-time. As soon as the insert reaches psql, it will execute it. When in a pipe the two programs run in parallel. My point was, the reason your PREPARE isn't working is because you keep starting new processes. But if you fix that it will probably be fast enough that PREPARE won't make a difference anymore. What I suggested will work with PREPARE also, it just becomes more complicated: (echo "PREPARE blah;" for i in blah ; do echo "EXECUTE blah;" done) | psql -q Or perhaps you should move to a language like Perl where this problem doesn't exist. Have a nice day, > Ps. I recieved your reply as an attechment in the email ? That's your email client not handling MIME properly. Have a nice day, > > I think your speed is being limited by backend startup time and > > transaction commit time more than anything else. I don't think prepared > > statements will help in your case. > > > > The way I usually do it is pipe the output of a whole loop to psql like > > so: > > > > for i in blah ; do > > echo "insert into ..." > > done | psql -q > > > > Or more commonly, just have the script emit all the commands to stdout > > and then run it like so: > > > > ./myscript | psql -q > > > > An important way to increase speed would be to use explicit > > transactions (BEGIN/END). When executing a lot of statements this will > > speed up things considerably. Finally, if it's just INSERTs, consider > > using COPY, for even more efficiency. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment:
pgpRIndR3OVs6.pgp
Description: PGP signature
