Thread: Slow query over ADSL Line

Slow query over ADSL Line

From

Thomas Chabaud

Date:

06 March 2006, 09:20:26

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

Re: Slow query over ADSL Line

From

"Adnan DURSUN"

Date:

06 March 2006, 09:48:41

----- Original Message -----
>From: "Thomas Chabaud" <tc@geosys.com>
>To: <pgsql-odbc@postgresql.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.

Re: Slow query over ADSL Line

From

Thomas Chabaud

Date:

06 March 2006, 09:54:16

Adnan DURSUN a écrit :
> ----- Original Message -----
>> From: "Thomas Chabaud" <tc@geosys.com>
>> To: <pgsql-odbc@postgresql.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.

Re: Slow query over ADSL Line

From

"Adnan DURSUN"

Date:

06 March 2006, 10:03:21

----- Original Message -----
From: "Thomas Chabaud" <tc@geosys.com>
To: <pgsql-odbc@postgresql.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...

Re: Slow query over ADSL Line

From

"Adnan DURSUN"

Date:

06 March 2006, 10:28:51

> ----- Original Message -----
> From: "Thomas Chabaud" <tc@geosys.com>
> To: <pgsql-odbc@postgresql.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...

Adnan DURSUN
ASRIN Bilisim Ltd.

Re: Slow query over ADSL Line

From

Thomas Chabaud

Date:

06 March 2006, 11:26:14

Adnan DURSUN a écrit :
>
>> ----- Original Message ----- From: "Thomas Chabaud" <tc@geosys.com>
>> To: <pgsql-odbc@postgresql.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...
>
> Adnan DURSUN
> ASRIN Bilisim Ltd.
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>

I have checked, but this option is not enabled.

Thomas

Re: Slow query over ADSL Line

From

"Philippe Lang"

Date:

06 March 2006, 12:11:06

Hi,

How fast to you get the results of your 300 queries on the LAN?
Have you maybe tried sniffing the network, for example with tcpdump?

Philippe

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] De la part de Thomas Chabaud
Envoyé : lundi, 6. mars 2006 11:20
À : pgsql-odbc@postgresql.org
Objet : [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.

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

Attachment

smime.p7s

Re: Slow query over ADSL Line

From

Thomas Chabaud

Date:

06 March 2006, 12:22:32

Philippe Lang a écrit :
> Hi,
>
> How fast to you get the results of your 300 queries on the LAN?
> Have you maybe tried sniffing the network, for example with tcpdump?
>
> Philippe
>

On the lan I fetch the result in approximatively 1.5s (for all queries)
I will try to sniff the network to see if there's something wrong.
Thanks for your advice.

Regards,

Thomas

Re: Slow query over ADSL Line

From

Andreas Pflug

Date:

06 March 2006, 13:17:03

Thomas Chabaud wrote:
> Philippe Lang a écrit :
>
>> Hi,
>>
>> How fast to you get the results of your 300 queries on the LAN?
>> Have you maybe tried sniffing the network, for example with tcpdump?
>>
>> Philippe
>>
>
> On the lan I fetch the result in approximatively 1.5s (for all queries)
> I will try to sniff the network to see if there's something wrong.
> Thanks for your advice.

Maybe a latency problem. Check if your roundtrip ping times are
consistent when changing packet sizes.

Regards,
Andreas

Re: Slow query over ADSL Line

From

Eric E

Date:

06 March 2006, 13:48:28

Thomas Chabaud wrote:
> Philippe Lang a écrit :
>> Hi,
>>
>> How fast to you get the results of your 300 queries on the LAN?
>> Have you maybe tried sniffing the network, for example with tcpdump?
>>
>> Philippe
>>
>
> On the lan I fetch the result in approximatively 1.5s (for all queries)
> I will try to sniff the network to see if there's something wrong.
> Thanks for your advice.
>
I'd also check to see exactly what queries are being executed by turning
on query logging on the server.  This might illuminate anything ADO is
doing that you didn't intend.  Along the same lines, have you tried
running the same set of queries across the ADSL line using psql?

Cheers,

Eric

Re: Slow query over ADSL Line

From

Marten Feldtmann

Date:

06 March 2006, 18:07:18

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

Re: Slow query over ADSL Line

From

Thomas Chabaud

Date:

07 March 2006, 09:50:52

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

Re: Slow query over ADSL Line

From

Ludek Finstrle

Date:

07 March 2006, 10:24:18

> 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

Re: Slow query over ADSL Line

From

Thomas Chabaud

Date:

07 March 2006, 10:33:10

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