Обсуждение: 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@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.
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.
----- 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...
> ----- 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.
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
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
Вложения
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
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
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
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