Lists: | pgsql-odbc |
---|
From: | Thomas Chabaud <tc(at)geosys(dot)com> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Slow query over ADSL Line |
Date: | 2006-03-06 10:20:10 |
Message-ID: | 440C0CDA.5010007@geosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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
From: | "Adnan DURSUN" <a_dursun(at)hotmail(dot)com> |
---|---|
To: | <tc(at)geosys(dot)com>, <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Slow query over ADSL Line |
Date: | 2006-03-06 10:48:23 |
Message-ID: | BAY106-DAV3649CB4F8C5087B5FA563FAE90@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
----- 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.
From: | Thomas Chabaud <tc(at)geosys(dot)com> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Slow query over ADSL Line |
Date: | 2006-03-06 10:54:10 |
Message-ID: | 440C14D2.8020106@geosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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.
From: | "Adnan DURSUN" <a_dursun(at)hotmail(dot)com> |
---|---|
To: | <tc(at)geosys>, <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Slow query over ADSL Line |
Date: | 2006-03-06 11:03:05 |
Message-ID: | BAY106-DAV9714038054119AC46B431FAE90@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
----- 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...
From: | Marten Feldtmann <m(dot)feldtmann(at)t-online(dot)de> |
---|---|
To: | tc(at)geosys(dot)com |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Slow query over ADSL Line |
Date: | 2006-03-06 19:06:55 |
Message-ID: | 144.20.07.01.06.03.2006@www.schrievkrom.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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
From: | Thomas Chabaud <tc(at)geosys(dot)com> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Slow query over ADSL Line |
Date: | 2006-03-07 10:50:37 |
Message-ID: | 440D657D.8050408@geosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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
From: | Ludek Finstrle <luf(at)pzkagis(dot)cz> |
---|---|
To: | Thomas Chabaud <tc(at)geosys(dot)com> |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Slow query over ADSL Line |
Date: | 2006-03-07 11:24:07 |
Message-ID: | 20060307112407.GA785@soptik.pzkagis.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
> 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
From: | Thomas Chabaud <tc(at)geosys(dot)com> |
---|---|
To: | |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Slow query over ADSL Line |
Date: | 2006-03-07 11:33:08 |
Message-ID: | 440D6F74.7060603@geosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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