Re: Performance over a LAN

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