Hi,

I have an application which has an queue of data it has to insert into
a table in a local database. the insert-queries syntax is all the same,
and the values are the only thing that differs. The insert-query looks
like this:

 INSERT INTO "table" VALUES(a, b, c, d, e, f, g, h)

...but I cannot insert more than 200/sec, and that is much too slow for
me. Are there ways to precompile a sqlquery or do other tricks to get the
*fastest* insertion-rate, since the data-queue is growing faster than
200/sec... I don't care about integrity etc!

I'm using PostgreSQL 7.0.3, RH 6.2 Linux 2.2.4, and the pq library with
gcc.


Regards,

Steffen E. Thorkildsen

(PS! Please reply to my e-mail aswell.)


Tue, 27 Feb 2001 13:25:07 +0100 (MET)
Steffen Emil Thorkildsen <steffent@ifi.uio.no>
Query precompilation?
On Tue, 27 Feb 2001, you wrote:
> Hi,
> 
> I have an application which has an queue of data it has to insert into
> a table in a local database. the insert-queries syntax is all the same,
> and the values are the only thing that differs. The insert-query looks
> like this:
> 
>  INSERT INTO "table" VALUES(a, b, c, d, e, f, g, h)
> 
> ...but I cannot insert more than 200/sec, and that is much too slow for
> mme. Are there ways to precompile a sqlquery or do other tricks to get the
> *fastest* insertion-rate, since the data-queue is growing faster than
> 200/sec... 

> I don't care about integrity etc!

You should !-)

You can find some valueable tips in the documentation: 
http://www.de.postgresql.org/users-lounge/docs/7.0/user/c4929.htm

> I'm using PostgreSQL 7.0.3, RH 6.2 Linux 2.2.4, and the pq library with
> gcc.
> 
> 
> Regards,
> 
> Steffen E. Thorkildsen
> 
> (PS! Please reply to my e-mail aswell.)

Tue, 27 Feb 2001 14:11:04 +0100
Robert Schrem <Robert.Schrem@WiredMinds.de>
Re: Query precompilation?
Steffen Emil Thorkildsen <steffent@ifi.uio.no> writes:
> I have an application which has an queue of data it has to insert into
> a table in a local database. the insert-queries syntax is all the same,
> and the values are the only thing that differs. The insert-query looks
> like this:

>  INSERT INTO "table" VALUES(a, b, c, d, e, f, g, h)

> ...but I cannot insert more than 200/sec, and that is much too slow for
> me.

Consider using COPY FROM STDIN instead ...

			regards, tom lane

Tue, 27 Feb 2001 10:56:33 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Query precompilation? 
Steffen Emil Thorkildsen <steffent@ifi.uio.no> writes:

> me. Are there ways to precompile a sqlquery or do other tricks to get the
> *fastest* insertion-rate, since the data-queue is growing faster than
> 200/sec... I don't care about integrity etc!
> 
> I'm using PostgreSQL 7.0.3, RH 6.2 Linux 2.2.4, and the pq library with
> gcc.
> 

Apart from the COPY mentioned by Tom Lane, you should also fo through the
obvious checklist: use -F to disable fsync, drop indexes(if possible), use
several connections(could help if you have multiprossessor system)

27 Feb 2001 17:11:16 +0100
Gunnar R|nning <gunnar@candleweb.no>
Re: Query precompilation?
(...)
> >
> > I don't care about integrity etc!
>
> You should !-)
>
> You can find some valueable tips in the documentation:
> http://www.de.postgresql.org/users-lounge/docs/7.0/user/c4929.htm
>

In the docs there is this paragraph:
>Disable Auto-commit
>
> Turn off auto-commit and just do one commit at the end. Otherwise Postgres 
>is doing a lot of work for each record added. In general when you are doing 
>bulk inserts, you want to turn off some of the database features to gain 
>speed. 

This sounds nice, but I've read a lot of postgres documents and still do not 
know how to disable autocommit. Is this possible? And how?

Mario Weilguni

-- 
===================================================
 Mario Weilguni ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½               KPNQwest Austria GmbH
ï¿½Senior Engineer Web Solutions                         Nikolaiplatz 4
ï¿½tel: +43-316-813824 ï¿½ ï¿½ ï¿½ ï¿½                        8020 graz, austria
ï¿½fax: +43-316-813824-26 ï¿½ ï¿½ ï¿½              http://www.kpnqwest.at
ï¿½e-mail: mario.weilguni@kpnqwest.com
===================================================

Tue, 27 Feb 2001 19:39:43 +0100
Mario Weilguni <mweilguni@sime.com>
Re: Re: Query precompilation?
Mario,

> This sounds nice, but I've read a lot of postgres documents and still do not
> know how to disable autocommit. Is this possible? And how?

Yes, you can disable autocommit.  All you have to do is wrap your SQL
statements within an explicit BEGIN ... COMMIT block.

Regards, Joe

--
Joe Mitchell                      joe.mitchell@greatbridge.com
Knowledge Engineer                757.233.5567 voice
Great Bridge, LLC                 757.233.5555 fax
www.greatbridge.com




Mario,
This sounds nice, but I've read a lot of postgres
documents and still do not
know how to disable autocommit. Is this possible? And how?
Yes, you can disable autocommit.  All you have to do is wrap your
SQL statements within an explicit BEGIN ... COMMIT block.
Regards, Joe
-- 
Joe Mitchell                      joe.mitchell@greatbridge.com
Knowledge Engineer                757.233.5567 voice
Great Bridge, LLC                 757.233.5555 fax
www.greatbridge.com
Tue, 27 Feb 2001 16:29:08 -0500
jmitchell@greatbridge.com
Re: Query precompilation?
I'm sorry Joe but I must know.. What exactly does a "Knowledge Engineer" do?
I've never run into a person with that title before.. Perhaps it's because I
live in my office but I'm still curious..

Thanks!

-Mitch

----- Original Message -----
From: <jmitchell@greatbridge.com>
To: <mweilguni@sime.com>; <pgsql-general@postgresql.org>
Sent: Tuesday, February 27, 2001 4:29 PM
Subject: Re: Query precompilation?


> Mario,
>
> > This sounds nice, but I've read a lot of postgres documents and still do
not
> > know how to disable autocommit. Is this possible? And how?
>
> Yes, you can disable autocommit.  All you have to do is wrap your SQL
> statements within an explicit BEGIN ... COMMIT block.
>
> Regards, Joe
>
> --
> Joe Mitchell                      joe.mitchell@greatbridge.com
> Knowledge Engineer                757.233.5567 voice
> Great Bridge, LLC                 757.233.5555 fax
> www.greatbridge.com
>
>
>


Tue, 27 Feb 2001 16:36:28 -0500
"Mitch Vincent" <mitch@venux.net>
Re: Query precompilation? - Off topic
Hi!

Thanks for the answer, but that's not disabling autocommit, it committing by 
hand. What I mean ist Oracle-behaviour --> everthing is a transaction and 
must be commited by "COMMIT". What I ment was something like "SET autocommit 
to OFF" or something like this.

Anyway, thanks for your answer, now I know it's not possible.

Ciao,
ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ Mario

Am Dienstag, 27. Februar 2001 22:29 schrieben Sie:
> Mario,
>
> > This sounds nice, but I've read a lot of postgres documents and still do
> > not know how to disable autocommit. Is this possible? And how?
>
> Yes, you can disable autocommit.  All you have to do is wrap your SQL
> statements within an explicit BEGIN ... COMMIT block.
>
> Regards, Joe
>
> --
> Joe Mitchell                      joe.mitchell@greatbridge.com
> Knowledge Engineer                757.233.5567 voice
> Great Bridge, LLC                 757.233.5555 fax
> www.greatbridge.com

----------------------------------------
Content-Type: text/html; charset="us-ascii"; name="Anhang: 1"
Content-Transfer-Encoding: 7bit
Content-Description: 
----------------------------------------

-- 
===================================================
 Mario Weilguni ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½               KPNQwest Austria GmbH
ï¿½Senior Engineer Web Solutions                         Nikolaiplatz 4
ï¿½tel: +43-316-813824 ï¿½ ï¿½ ï¿½ ï¿½                        8020 graz, austria
ï¿½fax: +43-316-813824-26 ï¿½ ï¿½ ï¿½              http://www.kpnqwest.at
ï¿½e-mail: mario.weilguni@kpnqwest.com
===================================================

Tue, 27 Feb 2001 22:37:46 +0100
Mario Weilguni <mweilguni@sime.com>
Re: Re: Query precompilation?
On Tue, 27 Feb 2001, Mario Weilguni wrote:

> Thanks for the answer, but that's not disabling autocommit, it committing by 
> hand. What I mean ist Oracle-behaviour --> everthing is a transaction and 
> must be commited by "COMMIT". What I ment was something like "SET autocommit 
> to OFF" or something like this.

Everything _is_ a transaction - the BEGIN ... COMMIT is implied, if you
don't wrap your SQL statements in BEGIN ... COMMIT.

Compare:

dominic=# INSERT INTO pages ( page_from, page_to, page_data ) VALUES ( 'Dominic', '555-1212', 'This is a test page');
INSERT 945129 1

[ This was one transaction ]
dominic=# SELECT count(*) FROM pages;
 count 
-------
     1
(1 row)

[ This was the second transaction ]

... for a total of two transactions, as opposed to:

dominic=# BEGIN;
BEGIN
dominic=# INSERT INTO pages ( page_from, page_to, page_data ) VALUES ( 'Dominic', '555-1212', 'Test page number two.' );
INSERT 945130 1
dominic=# SELECT count(*) FROM pages;
 count 
-------
     2
(1 row)

dominic=# COMMIT;
COMMIT

[ This was just _one_ transaction ]


-- 
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/



Tue, 27 Feb 2001 15:56:47 -0600 (CST)
"Dominic J. Eidson" <sauron@the-infinite.org>
Re: Re: Query precompilation?
Hi Mitch,

PMFJI ... Knowledge Engineering is where Great Bridge touches its 
customers- it encompasses engineering support, consulting, and 
training.  Joe and his colleagues support our paying customers and also 
do some development and documentation work.  And of course, they're on 
the project mailing lists like everyone else :-)

<propaganda>
We made up the name to highlight what we think is important in an open 
source company - real engineers, who deal in knowledge of the software.  
So it's not some entry-level operator taking a customer's call, it's 
someone who's a trained user of PostgreSQL himself.  Our competitive 
advantage as a company can't lie in anything like proprietary software 
products- it has to be in the people we hire and retain.
</propaganda>

Regards,
Ned


Mitch Vincent wrote:

> I'm sorry Joe but I must know.. What exactly does a "Knowledge Engineer" do?
> I've never run into a person with that title before.. Perhaps it's because I
> live in my office but I'm still curious..
> 
> Thanks!
> 
> -Mitch


-- 
----------------------------------------------------
Ned Lilly                     e: ned@greatbridge.com
Vice President                w: www.greatbridge.com
Evangelism / Hacker Relations        v: 757.233.5523
Great Bridge, LLC                    f: 757.233.5555


Tue, 27 Feb 2001 17:20:10 -0500
Ned Lilly <ned@greatbridge.com>
Re: Re: Query precompilation? - Off topic
Mario Weilguni wrote:
> 
> (...)
> > >
> > > I don't care about integrity etc!
> >
> > You should !-)
> >
> > You can find some valueable tips in the documentation:
> > http://www.de.postgresql.org/users-lounge/docs/7.0/user/c4929.htm
> >
> 
> In the docs there is this paragraph:
> >Disable Auto-commit
> >
> > Turn off auto-commit and just do one commit at the end. Otherwise Postgres
> >is doing a lot of work for each record added. In general when you are doing
> >bulk inserts, you want to turn off some of the database features to gain
> >speed.
> 
> This sounds nice, but I've read a lot of postgres documents and still do not
> know how to disable autocommit. Is this possible? And how?

At the moment, use a BEGIN/COMMIT block around a set of insert
statements. Someday we'll likely have an explicit command to affect the
behavior.

                     - Thomas

Wed, 28 Feb 2001 03:18:20 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Query precompilation?