Re: slow speeds after 2 million rows inserted

From: Frank Finner <postgresql(at)finner(dot)de>
To: James Neff <jneff(at)tethyshealth(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: slow speeds after 2 million rows inserted
Date: 2006-12-29 19:28:25
Message-ID: 20061229202825.3b2d199a.postgresql@finner.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

In Java, assuming you have a Connection c, you simply say "c.commit();" after doing some action on the database. After every commit, the transaction will be executed and closed and a new one opened, which runs until the next commit.

Regards, Frank.

On Fri, 29 Dec 2006 13:23:37 -0500 James Neff <jneff(at)tethyshealth(dot)com> thought long, then sat down and wrote:

> I'm sorry to ask a stupid question, but how do I 'commit' the transactions?
>
> Thanks,
> James
>
>
>
> Frank Finner wrote:
> > When do you commit these inserts? I occasionally found similiar problems, when I do heavy inserting/updating within one single transaction. First all runs fast, after some time everything slows down. If I commit the inserts every some 1000 rows (large rows, small engine), this phenomenon does not occur. Maybe some buffer chokes if the transaction ist too big.
> >
> > In your case I'd recommend to commit after every one or two million rows (if possible).
> >
> > Regards, Frank.
> >
> >
> > On Fri, 29 Dec 2006 12:39:03 -0500 James Neff <jneff(at)tethyshealth(dot)com> thought long, then sat down and wrote:
> >
> >
> >> Greetings,
> >>
> >> Ive got a java application I am reading data from a flat file and
> >> inserting it into a table. The first 2 million rows (each file
> >> contained about 1 million lines) went pretty fast. Less than 40 mins to
> >> insert into the database.
> >>
> >> After that the insert speed is slow. I think I may be able to type the
> >> data faster than what is being done by the java application on the third
> >> file.
> >>
> >
> >
>
>
> --
>
> James Neff
> Technology Specialist
>
> Tethys Health Ventures
> 4 North Park Drive, Suite 203
> Hunt Valley, MD 21030
>
> office: 410.771.0692 x103
> cell: 443.865.7874
>
>

--
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606 Mail: frank(dot)finner(at)invenius(dot)de
Telefax: 0271 231 8608 Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message James Neff 2006-12-29 19:36:44 Re: slow speeds after 2 million rows inserted
Previous Message Bob Pawley 2006-12-29 19:26:52 Re: Backup Restore