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

Re: Slow query over ADSL Line

----- Original Message -----
>From: "Thomas Chabaud"
>To:
>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

Re: Slow query over ADSL Line

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

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


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

Re: Slow query over ADSL Line

----- Original Message -----
From: "Thomas Chabaud"
To:
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...

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Slow query over ADSL Line

> ----- Original Message -----
> From: "Thomas Chabaud"
> To:
> 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

Re: Slow query over ADSL Line

Adnan DURSUN a =E9crit :
>=20
>> ----- Original Message ----- From: "Thomas Chabaud"
>> To:
>> 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=
=20
>>> ko.
>=20
> Turn off the ODBC trace option from ODBC control panel, if it is on=
....
>=20
> 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
>=20
>=20

I have checked, but this option is not enabled.

Thomas

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

Re: Slow query over ADSL Line

This is a multi-part message in MIME format.

------=_NextPart_000_0008_01C64127.C6731A80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=E9 : lundi, 6. mars 2006 11:20
=C0 : 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=3Dmyid", 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=3DNew 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


------=_NextPart_000_0008_01C64127.C6731A80
Content-Type: application/x-pkcs7-signature;
name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIII/zCCAocw
ggHwoAMCAQICEFpaIYarXomyBeuKGjRG04swDQYJKoZIhvcNAQEEBQAwYjEL MAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNV BAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1MTIyMDEzNDAyNloX DTA2MTIyMDEzNDAy
NlowZzENMAsGA1UEBBMETGFuZzERMA8GA1UEKhMIUGhpbGlwcGUxFjAUBgNV BAMTDVBoaWxpcHBl
IExhbmcxKzApBgkqhkiG9w0BCQEWHHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0 ZW0uY2gwgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBAJsLuUD6Q3LdQfy6UKzYyhLAyhzocE3L j7oaJLBvFQk0rQHf
j14K6A39OW5pBil2aw/B93JalSJVkasS3FsjAFs1LvQS8xY0mgB2CtthT3Fa jV0bB424LQdZ2KhG
tHbPEm/68S0ARXlTZaD4I5ixwFX+Zaa0uuPFhWfZems0B7nBAgMBAAGjOTA3 MCcGA1UdEQQgMB6B
HHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQF
AAOBgQA3w0be0GoD+dBCQ0JuV0l2gpBC8KcKaym1zGiRLn7C1ojGuXsw/TSh xor7f63H3CUZq+aL
zdLtUpFm4a/LdO/1GLKs4ZJSY1WKzeNHiyXGjnkvuzKjRwzVA94McLeBpg8f DocaH3Z0Go9dg/f6
d0sMTmKAxRDYMeNtWjUuAGod+DCCAy0wggKWoAMCAQICAQAwDQYJKoZIhvcN AQEEBQAwgdExCzAJ
BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNh cGUgVG93bjEaMBgG
A1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRp b24gU2VydmljZXMg
RGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBD QTErMCkGCSqGSIb3
DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw05NjAxMDEw MDAwMDBaFw0yMDEy
MzEyMzU5NTlaMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBD YXBlMRIwEAYDVQQH
EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYD VQQLEx9DZXJ0aWZp
Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVy c29uYWwgRnJlZW1h
aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0 ZS5jb20wgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBANRp19SwlGRbcelH2AxRtupykbCEXn0t DY97Et+FJXUodDpC
LGMnn5V7S+9+GYcdhuqj3bnOlmQawhRuRKx85o/oTQ9xH0A4pgCjh3j2+ZSG Xq3qwF5269kUo11u
enwMpUtVfwYZKX+emibVars4JAhqmMex2qOYkf152+VaxBy5AgMBAAGjEzAR MA8GA1UdEwEB/wQF
MAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAx+ySfk749ZalZ2IqpPBNEWDQb41g WGGsJrtSNVwIzzD7
qEqWih9iQiOMFw/0umScF6xHKd+dmF7SbGBxXKKs3Hnj524ARx+1DSjoAp3k mv0T9KbZfLH43F8j
JgmRgHPQFBveQ6mDJfLmnC8Vyv6mq4oHdYsM3VGEa+T40c53ooEwggM/MIIC qKADAgECAgENMA0G
CSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVy biBDYXBlMRIwEAYD
VQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgw JgYDVQQLEx9DZXJ0
aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUg UGVyc29uYWwgRnJl
ZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRo YXd0ZS5jb20wHhcN
MDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTEl MCMGA1UEChMcVGhh
d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBl cnNvbmFsIEZyZWVt
YWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSm PFVzVftOucqZWh5o
wHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnw K4Vaqj9xVsuvPAsH
5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e2 0TxhBEAeZBlyYLf7
AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDig NqA0hjJodHRwOi8v
Y3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDAL BgNVHQ8EBAMCAQYw
KQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0G CSqGSIb3DQEBBQUA
A4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQc UCCTcDz9reFhYsPZ
Ohl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u 9uo05RAaWzVNd+NW
IXiC3CEZNd4ksdMdRv9dX2VPMYIC+DCCAvQCAQEwdjBiMQswCQYDVQQGEwJa QTElMCMGA1UEChMc
VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3Rl IFBlcnNvbmFsIEZy
ZWVtYWlsIElzc3VpbmcgQ0ECEFpaIYarXomyBeuKGjRG04swCQYFKw4DAhoF AKCCAdgwGAYJKoZI
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwMzA2MTMx MDUxWjAjBgkqhkiG
9w0BCQQxFgQUbdkapMNuE1y3htbpS7CdeOFZptUwZwYJKoZIhvcNAQkPMVow WDAKBggqhkiG9w0D
BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI KoZIhvcNAwICASgw
BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkG A1UEBhMCWkExJTAj
BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJz
b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBaWiGGq16JsgXriho0RtOLMIGH BgsqhkiG9w0BCRAC
CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0 aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5n IENBAhBaWiGGq16J
sgXriho0RtOLMA0GCSqGSIb3DQEBAQUABIGAILcFmWSbDTs1Q841eNqa8KkU IDLedLNyN6TsFUFY
yOQ8lKKAxruNzRqrTOvWvvxj6abtOQdby0tfEKQ3Kd1Ir5lkJPqF2LpNf5Kj 0WSN1wCv9K6RYuy+
owdY5LN2rlTz6uYzHq/vb5hWJeqpa4KCdhqCkZdc6I0IDFTLn+cLzfcAAAAA AAA=

------=_NextPart_000_0008_01C64127.C6731A80--

Re: Slow query over ADSL Line

Philippe Lang a =E9crit :
> Hi,
>=20
> 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?
>=20
> Philippe
>=20

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Slow query over ADSL Line

Thomas Chabaud wrote:
> Philippe Lang a =E9crit :
>=20
>> 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
>>
>=20
> 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=20
consistent when changing packet sizes.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Slow query over ADSL Line

Thomas Chabaud wrote:
> Philippe Lang a =E9crit :
>> 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=20
on query logging on the server. This might illuminate anything ADO is=20
doing that you didn't intend. Along the same lines, have you tried=20
running the same set of queries across the ADSL line using psql?

Cheers,

Eric

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

Re: Slow query over ADSL Line

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Slow query over ADSL Line

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

Thanks everybody for your helpful advices.

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

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

conn=3D143130696, PGAPI_DriverConnect(out)=3D'DRIVER=3D{PostgreSQL ANSI};
DATABASE=3Daveyron;SERVER=3DXXX.XXX.XXX.XXX;PORT=3D5432;
SSLMODE=3Dprefer;UID=3Dmylogin;PWD=3Dmypass;ReadOnly=3D0;Fak eOidIndex=3D0=
;ShowOidColumn=3D0;
RowVersioning=3D0;ShowSystemTables=3D0;ConnSettings=3D;Fetch =3D10000;Sock=
et=3D8192;
UnknownSizes=3D0;MaxVarcharSize=3D254;MaxLongVarcharSize=3D8 190;Debug=3D1=
;CommLog=3D1;
Optimizer=3D1;Ksqo=3D1;UseDeclareFetch=3D0;TextAsLongVarchar =3D1;Unknowns=
AsLongVarchar=3D0;
BoolsAsChar=3D1;Parse=3D0;CancelAsFreeStmt=3D0;ExtraSysTable Prefixes=3Ddd=
_;;LFConversion=3D1;
UpdatableCursors=3D0;DisallowPremature=3D0;TrueIsMinus1=3D1; BI=3D0;ByteaA=
sLongVarBinary=3D0;
UseServerSidePrepare=3D0;LowerCaseIdentifier=3D0'

DESCRIPTOR ERROR: func=3DPGAPI_SetDescField, desc=3D'', errnum=3D11, errm=
sg=3D'bad=20
parameter number'

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

Thanks in advance.

Thomas


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

Re: Slow query over ADSL Line

> 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

Re: Slow query over ADSL Line

Ludek Finstrle a =E9crit :
>> DESCRIPTOR ERROR: func=3DPGAPI_SetDescField, desc=3D'', errnum=3D11, e=
rrmsg=3D'bad=20
>> parameter number'
>>
>> What does it means ? Is it only a warning or an annoying problem ?
>=20
> 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 :-)
>=20
> Regards,
>=20
> Luf
>=20
> ---------------------------(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
>=20
>=20

Ok, thanks a lot :-)

Regards,

Thomas

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend