Slow query over ADSL Line

Hello,

I'm using psql ODBC driver with ADO in a visual basic 6 application, and I
have some speed issues when I connect using an adsl line (8M down/1M up) (it
works perfectly well using an ethernet 100Mbps LAN).

The application sends about 300 queries to server, and it takes 150 seconds
to fetch the results of all queries. I have also tested some of the queries
with pgAdmin, and it takes about 1.5s/2s for each query.

The queries are all in the same simple form : "select
myfield1,myfield2,myfield3[...] from table where id=myid",
and they send back only 40/50 rows max.

I have no debug option enabled, and there is no "blob" object in my database.

I use the following options with ADO recordset :

Dim rec as ADODB.Recordset
set rec=New ADODB.Recordset

rec.open myquery, myDbConn, adOpenStatic, adLockReadOnly, adCmdText

Does the slowness is due to ADO or ODBC ?
Is there a simple way to speed up the result fetch ?

Thanks in advance,

Regards

Thomas Chabaud

----- Original Message -----
>From: "Thomas Chabaud" <tc(at)geosys(dot)com>
>To: <pgsql-odbc(at)postgresql(dot)org>
>Sent: Monday, March 06, 2006 12:20 PM
>Subject: [ODBC] Slow query over ADSL Line

>> Hello,
>>
>> I'm using psql ODBC driver with ADO in a visual basic 6 application, and
>> I have some speed issues when I connect using an adsl line (8M down/1M
>> up) (it works perfectly well using an ethernet 100Mbps LAN).
>>
>> The application sends about 300 queries to server, and it takes 150
>> seconds to fetch the results of all queries. I have also tested some of
>> the queries with pgAdmin, and it takes about 1.5s/2s for each query.
>>
>> The queries are all in the same simple form : "select
>> myfield1,myfield2,myfield3[...] from table where id=myid",
>> and they send back only 40/50 rows max.

You must consider how amount of data your application gets from server !

Adnan DURSUN
ASRIN Bilisim Ltd.

Adnan DURSUN a écrit :
> ----- Original Message -----
>> From: "Thomas Chabaud" <tc(at)geosys(dot)com>
>> To: <pgsql-odbc(at)postgresql(dot)org>
>> Sent: Monday, March 06, 2006 12:20 PM
>> Subject: [ODBC] Slow query over ADSL Line
>
>
>>> Hello,
>>>
>>> I'm using psql ODBC driver with ADO in a visual basic 6 application,
>>> and I have some speed issues when I connect using an adsl line (8M
>>> down/1M up) (it works perfectly well using an ethernet 100Mbps LAN).
>>>
>>> The application sends about 300 queries to server, and it takes 150
>>> seconds to fetch the results of all queries. I have also tested some
>>> of the queries with pgAdmin, and it takes about 1.5s/2s for each query.
>>>
>>> The queries are all in the same simple form : "select
>>> myfield1,myfield2,myfield3[...] from table where id=myid",
>>> and they send back only 40/50 rows max.
>
> You must consider how amount of data your application gets from server !
>
> Adnan DURSUN
> ASRIN Bilisim Ltd.
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

The total amount of data fetched from the server is approximately 500 ko.

----- Original Message -----
From: "Thomas Chabaud" <tc(at)geosys(dot)com>
To: <pgsql-odbc(at)postgresql(dot)org>
Sent: Monday, March 06, 2006 12:54 PM
Subject: Re: [ODBC] Slow query over ADSL Line

> The total amount of data fetched from the server is approximately 500 ko.

Turn off the ODBC trace option from ODBC control panel, if it is on...

Thomas Chabaud schrieb:

> Hello,
>
> I'm using psql ODBC driver with ADO in a visual basic 6 application,
> and I have some speed issues when I connect using an adsl line (8M
> down/1M up) (it works perfectly well using an ethernet 100Mbps LAN).

The first thing is: how many statements do you really execute. Then one
has to consider
the latency times of the medium: ADSL against Ethernet is very, very
different !

Marten

--
Marten Feldtmann - Germany - Software Development
Information regarding VA Smalltalk and DMS-system
"MSK - Mien Schrievkrom" at: www.schrievkrom.de

Marten Feldtmann a écrit :
> Thomas Chabaud schrieb:
>
>> Hello,
>>
>> I'm using psql ODBC driver with ADO in a visual basic 6 application,
>> and I have some speed issues when I connect using an adsl line (8M
>> down/1M up) (it works perfectly well using an ethernet 100Mbps LAN).
>
> The first thing is: how many statements do you really execute. Then one
> has to consider
> the latency times of the medium: ADSL against Ethernet is very, very
> different !
>
> Marten
>

Thanks everybody for your helpful advices.

I have optimized my application and I have set the UseDeclareFetchOption 0,
to reduce the total number of sent queries. It seems very efficient, the
queries now takes only 25/30s to fetch.

But during my tests, I've seen strange error in the log files :

conn=143130696, PGAPI_DriverConnect(out)='DRIVER={PostgreSQL ANSI};
DATABASE=aveyron;SERVER=XXX.XXX.XXX.XXX;PORT=5432;
SSLMODE=prefer;UID=mylogin;PWD=mypass;ReadOnly=0;FakeOidIndex=0;ShowOidColumn=0;
RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=10000;Socket=8192;
UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;
Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;
BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;
UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=1;BI=0;ByteaAsLongVarBinary=0;
UseServerSidePrepare=0;LowerCaseIdentifier=0'

DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad
parameter number'

What does it means ? Is it only a warning or an annoying problem ?

Thanks in advance.

Thomas

> DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad
> parameter number'
>
> What does it means ? Is it only a warning or an annoying problem ?

It only means that psqlODBC doesn't support such parameter (the
parameter number is printed few lines above this).
So if everything later works ok, it's only warning :-)

Regards,

Luf

Ludek Finstrle a écrit :
>> DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad
>> parameter number'
>>
>> What does it means ? Is it only a warning or an annoying problem ?
>
> It only means that psqlODBC doesn't support such parameter (the
> parameter number is printed few lines above this).
> So if everything later works ok, it's only warning :-)
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

Ok, thanks a lot :-)

Regards,

Thomas