Horton

Thread

  1. Query precompilation?

    Steffen Emil Thorkildsen <steffent@ifi.uio.no> — 2001-02-27T12:25:07Z

    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.)

  2. Re: Query precompilation?

    Robert Schrem <robert.schrem@wiredminds.de> — 2001-02-27T13:11:04Z

    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.)

  3. Re: Query precompilation?

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-27T15:56:33Z

    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

  4. Re: Query precompilation?

    Gunnar R|nning <gunnar@candleweb.no> — 2001-02-27T16:11:16Z

    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)

  5. Re: Re: Query precompilation?

    Mario Weilguni <mweilguni@sime.com> — 2001-02-27T18:39:43Z

    (...)
> >
> > 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
===================================================

  6. Re: Query precompilation?

    jmitchell@greatbridge.com — 2001-02-27T21:29:08Z

    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

  7. Re: Query precompilation? - Off topic

    Mitch Vincent <mitch@venux.net> — 2001-02-27T21:36:28Z

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

  8. Re: Re: Query precompilation?

    Mario Weilguni <mweilguni@sime.com> — 2001-02-27T21:37:46Z

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

  9. Re: Re: Query precompilation?

    Dominic J. Eidson <sauron@the-infinite.org> — 2001-02-27T21:56:47Z

    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/

  10. Re: Re: Query precompilation? - Off topic

    Ned Lilly <ned@greatbridge.com> — 2001-02-27T22:20:10Z

    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

  11. Re: Query precompilation?

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-02-28T03:18:20Z

    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