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

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

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)

(...)

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

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

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

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

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/

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

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