Skip site navigation (1) Skip section navigation (2)

Re: Performance over a LAN

From: Gary Cowell <gary_cowell(at)yahoo(dot)co(dot)uk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance over a LAN
Date: 2004-07-23 16:26:08
Message-ID: 20040723162608.84118.qmail@web25105.mail.ukl.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
--- William Carney <wcarney(at)sa(dot)quiktrak(dot)com(dot)au> wrote:


> The test program is a C program with embedded SQL
> (ecpg). The only
> difference between the tests was the address used in
> the EXEC SQL CONNECT
> .. statement. The inserts are committed to the
> database by performing an
> EXEC SQL COMMIT after every N of them; I tried
> various values of N up to
> several hundred, but it didn't make much difference.
> Using psql I can see
> records appearing in the database in groups of that
> size. I'm not sure about
> all of the protocol versions. I downloaded the
> complete Postgres source and
> built it only a few days ago. Ecpg says that it's
> version is 3.1.1. I'm not
> getting any errors reported anywhere, it's just that
> things are surprisingly
> slow over the LAN for some reason.
> 
> William


It's probably the number of round trips to the server.
 If pg can accept host variable arrays, try using a
thousand element array or something to do your
inserts.

e.g. char mycharhv[1000][10]

then set up the mycharhvs[1][..], [2][...] etc and
fling them at the database with a single insert
statement.

I just tried this with the following program:

#include <stdio.h>
exec sql include sqlca;
exec sql begin declare section;
char    db[10];
char    inserts[5000][10];
exec sql end declare section;
int main(void) {
unsigned int n;
        strcpy(db,"mydb");
        exec sql connect to :db;
        printf("sqlcode connect %i\n",sqlca.sqlcode);
        for(n=0;n<5000;n++) {
                strcpy(inserts[n],"hello");
        }
        exec sql insert into gaz values (:inserts);
        printf("sqlcode insert %i\n",sqlca.sqlcode);
        exec sql commit work;
}


This didn't work on pg, I only got one row inserted.
This is using ecpg 2.9.0, pg 7.2.2

On Oracle with PRO*C this causes 5000 rows to be
written with one insert and is a technique I've used
to  get better network performance with Oracle.

Is this fixed in newer versions? If not, it sounds
like a good feature.


	
	
		
___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com

In response to

pgsql-performance by date

Next:From: Greg StarkDate: 2004-07-23 17:09:47
Subject: Re: Performance over a LAN
Previous:From: Michael AdlerDate: 2004-07-23 15:34:16
Subject: Re: Performance over a LAN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group