Slow network retrieves

From: <wespvp(at)syntegra(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Slow network retrieves
Date: 2004-05-09 00:07:43
Message-ID: BCC2E07F.8166%wespvp@syntegra.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm seeing what seems like slow retrieval times over the network. I am
retrieving a single field of about 100-120 characters per record. I am
getting about 3 seconds per 1000 records - it takes 30 seconds to retrieve
10,000 records. That's only about 36 KBytes/sec.

This is a 100BT switched network (not sure if it is vlan'd or through a
router). Echo time averages 3ms.

The back end is pretty much idle. It shows 'idle in transaction'.

05-08-2004.23:54:43 Records read: 10000
05-08-2004.23:55:17 Records read: 20000
05-08-2004.23:55:50 Records read: 30000
05-08-2004.23:56:22 Records read: 40000
05-08-2004.23:56:55 Records read: 50000
05-08-2004.23:57:32 Records read: 60000
05-08-2004.23:58:07 Records read: 70000
...

The code is an ecpg program like:

EXEC SQL WHENEVER SQLERROR GOTO sql_error;
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL DECLARE message_cursor CURSOR FOR

SELECT
file_name
FROM
messages
WHERE
system_key=(select system_key from systems where
system_name=:systemName);

EXEC SQL OPEN message_cursor;

count = 0;
while (1) {
EXEC SQL FETCH message_cursor INTO
:fileNameDB;

memcpy (tempstr, fileNameDB.arr, fileNameDB.len);
tempstr[fileNameDB.len] = '\0';

[Action with tempstr removed for testing]

count++;
if ( (count % 10000) == 0) logmsg ("Records read: %d", count);
}

How can I speed this thing up?

Wes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christopher Kings-Lynne 2004-05-09 01:50:00 Re: Adding MERGE to the TODO list (resend with subject)
Previous Message elein 2004-05-08 23:19:05 Re: Adding MERGE to the TODO list (resend with subject)