Re: Performance over a LAN

--- William Carney <wcarney@sa.quiktrak.com.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
expressyourself http://uk.messenger.yahoo.com