Slow query through ODBC

Hi all !

I have a query that runs very fast when executed through pgAdmin
(~2 seconds), but very slowly through the ODBC driver (~30
seconds). Both queries are executed from the same workstation on
the same server.

My workstation is a WinXP Pro, and I use the latest psqlODBC
driver available as an MSI package (version 8.01.0102).
The server is a PostGreSQL 8.1.2, running on a Win2000 server.

The query is very simple, it returns about 1500 lines, made of
only one text field, but this field can be very large (it is the
WKT representation of a spatial object from PostGIS).

I am connecting to the server in a VBA project with a simple
connectionstring with only default options (I have not found a
document explaining how to fine tune the odbc driver).
My connectionstring is :
"DRIVER={PostgreSQL
Unicode};SERVER=myserver;PORT=5432;DATABASE=mydatabase;UID=m yuser;PWD=mypass"

I assume that the problem is with the very large field size that
is returned, but what should I change in my connectionstring to
make this query run faster ?

Thanks for your help !

--
Arnaud



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

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

Re: Slow query through ODBC

> My workstation is a WinXP Pro, and I use the latest psqlODBC
> driver available as an MSI package (version 8.01.0102).

Don't you have problem with geting whole data correctly? There
is bug in psqlodbc driver when reading data from text field larger
then 2048 (+-) bytes (in Unicode driver). The problem was fixed
in 08.01.0106 development snapshot.

> The server is a PostGreSQL 8.1.2, running on a Win2000 server.
>
> The query is very simple, it returns about 1500 lines, made of

Are you sure the PgAdmin returns all rows (I'm not). Maybe Dave
give us the answer.

> only one text field, but this field can be very large (it is the
> WKT representation of a spatial object from PostGIS).
>
> I am connecting to the server in a VBA project with a simple
> connectionstring with only default options (I have not found a
> document explaining how to fine tune the odbc driver).
> My connectionstring is :
> "DRIVER={PostgreSQL
> Unicode};SERVER=myserver;PORT=5432;DATABASE=mydatabase;UID=m yuser;PWD=mypass"
>
> I assume that the problem is with the very large field size that
> is returned, but what should I change in my connectionstring to
> make this query run faster ?

I don't know it exactly. Could you try experiment with parameters
such Use declare/fetch or Server side prepare?
It could help me when you send me the mylog output (enable the mylog
output under Global and DataSource button).

I hope the data isn't confidental (they are listed in mylog output).

Regards,

Luf

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

Re: Slow query through ODBC

Ludek Finstrle a =E9crit :
>> My workstation is a WinXP Pro, and I use the latest psqlODBC
>> driver available as an MSI package (version 8.01.0102).
>=20
> Don't you have problem with geting whole data correctly? There
> is bug in psqlodbc driver when reading data from text field larger
> then 2048 (+-) bytes (in Unicode driver). The problem was fixed
> in 08.01.0106 development snapshot.

No, the data looks good. I am drawing the spatial objects with the=20
query results, and my code would throw an error if some data was=20
incorrect.

>> The server is a PostGreSQL 8.1.2, running on a Win2000 server.
>> The query is very simple, it returns about 1500 lines, made of
>=20
> Are you sure the PgAdmin returns all rows (I'm not). Maybe Dave
> give us the answer.

Yes. Actually, pgAdmin asks me if I want all rows (default is to=20
return 100 rows). I answer yes and the result is almost immediate.
If I ask for an output in a text file, the query runs in ~2=20
seconds also.

>> only one text field, but this field can be very large (it is the
>> WKT representation of a spatial object from PostGIS).
>>=20
>> I am connecting to the server in a VBA project with a simple
>> connectionstring with only default options (I have not found a
>> document explaining how to fine tune the odbc driver).
>> My connectionstring is :
>> "DRIVER=3D{PostgreSQL
>> Unicode};SERVER=3Dmyserver;PORT=3D5432;DATABASE=3Dmydatabase ;UID=3Dmyu=
ser;PWD=3Dmypass"
>>=20
>> I assume that the problem is with the very large field size that
>> is returned, but what should I change in my connectionstring to
>> make this query run faster ?
>=20
> I don't know it exactly. Could you try experiment with parameters
> such Use declare/fetch or Server side prepare?
> It could help me when you send me the mylog output (enable the mylog
> output under Global and DataSource button).

I will try with these parameters, but how do I add them to the=20
connectionstring ?
Should I just append something like ";usedeclarefecth=3D1" at the=20
end of it ? I can't find any documentation on this (maybe I am not=20
searching at the right place...).

> I hope the data isn't confidental (they are listed in mylog output).

Unfortunately the data is confidential.
If we can't find a simple solution, I might just remove the data=20
from the log file ?


Thanks for helping me on this !


--
Arnaud
--
Arnaud


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

> >Don't you have problem with geting whole data correctly? There
> >is bug in psqlodbc driver when reading data from text field larger
> >then 2048 (+-) bytes (in Unicode driver). The problem was fixed
> >in 08.01.0106 development snapshot.
>
> No, the data looks good. I am drawing the spatial objects with the
> query results, and my code would throw an error if some data was
> incorrect.

Ok. I describe the problem better. The problem was that last part
of each row returned from psqlodbc are random data from memory.
So if the data for one column (and row) is larger then N bytes (some
users has problem with N = 2048 bytes) the problem raised.
It means that only last +- 1 .. N bytes for each row are filled
randomely from memory.
You didn't must run in the problem if the data are fetched in one call
of SQLGetData. I only want to notice it. It could help you in the
future ;-)

> >I don't know it exactly. Could you try experiment with parameters
> >such Use declare/fetch or Server side prepare?
> >It could help me when you send me the mylog output (enable the mylog
> >output under Global and DataSource button).
>
> I will try with these parameters, but how do I add them to the
> connectionstring ?
> Should I just append something like ";usedeclarefecth=1" at the
> end of it ? I can't find any documentation on this (maybe I am not
> searching at the right place...).

I'm sorry I don't know it exactly (I use ODBC manager).
Maybe you could configure it through ODBC manager ...

I try to search it in source code and it could be (fullname and
shortcut):
;UseDeclareFetch=1
;B6=1

;UseServerSidePrepare=1
;C8=1

Maybe it's case insensitive. I don't know it exactly.
Don't use it both at the time. You only waste your time if you try it.

> >I hope the data isn't confidental (they are listed in mylog output).
>
> Unfortunately the data is confidential.
> If we can't find a simple solution, I might just remove the data
> from the log file ?

You could remove the data from log (only keep there a note that data
is fetched and the data length could be useful too).
You want to seatch CC_mapping and PGAPI_GetData in the log.
There is at least one line per row. Maybe it could be easier when
you replace your data with 'x' or something like that.

If I can't find the problem in mylog output could you create example
data?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Slow query through ODBC

Ludek Finstrle a =E9crit :
>> >Don't you have problem with geting whole data correctly? There
>> >is bug in psqlodbc driver when reading data from text field larger
>> >then 2048 (+-) bytes (in Unicode driver). The problem was fixed
>> >in 08.01.0106 development snapshot.
>>=20
>> No, the data looks good. I am drawing the spatial objects with the=20
>> query results, and my code would throw an error if some data was=20
>> incorrect.
>=20
> Ok. I describe the problem better. The problem was that last part
> of each row returned from psqlodbc are random data from memory.
> So if the data for one column (and row) is larger then N bytes (some
> users has problem with N =3D 2048 bytes) the problem raised.
> It means that only last +- 1 .. N bytes for each row are filled
> randomely from memory.
> You didn't must run in the problem if the data are fetched in one call
> of SQLGetData. I only want to notice it. It could help you in the
> future ;-)

Good point Ludek. I double checked my data, and it definitively=20
good in the output.
FYI, the largest field returned is 23581 characters long. The=20
dataset is ~1.6MB long (so we have an average of 1kB per row).

>> I will try with these parameters, but how do I add them to the=20
>> connectionstring ?
>> Should I just append something like ";usedeclarefecth=3D1" at the=20
>> end of it ? I can't find any documentation on this (maybe I am not=20
>> searching at the right place...).
>=20
> I'm sorry I don't know it exactly (I use ODBC manager).
> Maybe you could configure it through ODBC manager ...
>=20
> I try to search it in source code and it could be (fullname and
> shortcut):
> ;UseDeclareFetch=3D1
> ;B6=3D1
>=20
> ;UseServerSidePrepare=3D1
> ;C8=3D1
>=20
> Maybe it's case insensitive. I don't know it exactly.
> Don't use it both at the time. You only waste your time if you try it.

I tried both parameters (they look OK because ADO did not throw an=20
error), but they did not help.
Maybe I should add that the query is stored in an ADO recordset,=20
which is read-only and forward-only ?
It is the .open method of this recordset that actually takes 30=20
seconds to run.

> You could remove the data from log (only keep there a note that data
> is fetched and the data length could be useful too).
> You want to seatch CC_mapping and PGAPI_GetData in the log.
> There is at least one line per row. Maybe it could be easier when
> you replace your data with 'x' or something like that.
>=20
> If I can't find the problem in mylog output could you create example
> data?

I sure could !
I'll try some other parameters before (I will use the File-DSN=20
panel to generate connectionstrings, I should have thought about=20
it before !), and I'll tell you whether I found something or not.


--
Arnaud


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

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

Re: Slow query through ODBC

> I tried both parameters (they look OK because ADO did not throw an
> error), but they did not help.

I think it. But we could test it at first :-)

> Maybe I should add that the query is stored in an ADO recordset,
> which is read-only and forward-only ?

This is the best situation for psqlODBC driver.

> It is the .open method of this recordset that actually takes 30
> seconds to run.

..open ADO contains a lot of SQL* commands for ODBC.
I need the mylog or (the better for me) example data to reproduce it
here.

> >If I can't find the problem in mylog output could you create example
> >data?
>
> I sure could !

Great.

> I'll try some other parameters before (I will use the File-DSN
> panel to generate connectionstrings, I should have thought about
> it before !), and I'll tell you whether I found something or not.

Ok. I'm looking for note from you.
If you'll not find the way please send me the parsed log or
example data with SELECT.

Regards,

Luf

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

Re: Slow query through ODBC

Tue, Jan 24, 2006 at 04:47:04PM +0100, Arnaud Lesauvage wrote:
> I went on with my investigations.
>
> I now believe that the problem lies within the network dialog
> (between my client and my server).
> If I transfer this table on my workstation (I have a local
> postgresql installation too), the query runs blindingly fast (less
> than 1 sec).
> I don't understand what the problem could be though.
>
> This thread in the mailing list archive looks like the problem
> mentionned is the same as mine, but they don't really mention a fix :
> http://archives.postgresql.org/pgsql-performance/2005-06/msg 00593.php
>
> My server is on the LAN, so bandwidth cannot be a problem.
> Furthermore, I just migrated from a MySQL 5.0 server. The exact
> same query on this MySQL server, through MyODBC, took less than 1
> second too, so my network is not the problem.
>
> Are there specific client/server protocol tuning parameters ?

I know about nothing like that.

> Have you ever heard of such issues ?

No, I haven't heard it with 08.01 yet.

Regards,

Luf

P.S. I reply mainly for Cc: pgsql-odbc - more heads more knowledge ;-)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Slow query through ODBC

=20

> -----Original Message-----
> From: Ludek Finstrle [mailto:luf@pzkagis.cz]=20
> Sent: 24 January 2006 22:35
> To: Arnaud Lesauvage
> Cc: Dave Page
> Subject: Re: [ODBC] Slow query through ODBC
>=20
> It seems to me I have to kick Dave to take a look at this :-)
> Dave is pgAdmin developer so he could give a light on the problem.
>=20
> Dave: Could you take a look at this problem?

Arnaud,

When you are testing, please make sure that the psqlODBC's mylog and
commlog are switched off, and that SQL Tracing is turned off in the ODBC
control panel applet.

Also, please note that pgAdmin has very lightweight data access classes
written over libpq - much more lightweight than the ODBC driver. Most of
the overhead in pgAdmin comes from rendering the data in the grid
control.

Regards, Dave.

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

Re: Slow query through ODBC

Dave Page a =E9crit :
>> From: Ludek Finstrle [mailto:luf@pzkagis.cz]=20
>> It seems to me I have to kick Dave to take a look at this :-)
>> Dave is pgAdmin developer so he could give a light on the problem.
>>=20
>> Dave: Could you take a look at this problem?
>=20
> Arnaud,
>=20
> When you are testing, please make sure that the psqlODBC's mylog and
> commlog are switched off, and that SQL Tracing is turned off in the ODB=
C
> control panel applet.
>=20
> Also, please note that pgAdmin has very lightweight data access classes
> written over libpq - much more lightweight than the ODBC driver. Most o=
f
> the overhead in pgAdmin comes from rendering the data in the grid
> control.
>=20
> Regards, Dave.

Dave,

All logging is off when I run tests. I only turned them on on=20
Ludek's request for debugging purposes. Tracing is always off in=20
the ODBC pannel.

What looks strange to me is that while the query is running=20
(during the "open" method of the recordset), processor usage is 0%=20
on the server and the workstation. Network usage is very low :=20
~50kB on a 100Mbps connection, during the entire query.

In pgAdmin, the query is immediate and there is just a pike in the=20
network traffic when the data is received. That's what I was=20
expecting to see through psqlODBC also...

Regards
--
Arnaud


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

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

Re: Slow query through ODBC

Hi List !

I have some news about this problem !
Just to remind you the facts :
A query runs very slowly when executed through the psqlodbc
driver, but very fast when executed through psql or pgAdmin.
The query returns very long rows containing only one field, but
this field can be as long as 40.000 characters (it is a text field).

I posted a TCP/IP dump of the dialog between my workstation and
the postgresql server to a TCP/IP newsgroup.
I fixed the problem by changing a parameter in my TCP/IP
configuration (on my workstation, not on the server).
I have set the TcpAckFrequency key to 1, as suggested by someone
on the comp.protocols.tcp-ip NG. The procedure is described in :
http://support.microsoft.com/?scid=kb%3Ben-us%3B328890&x=15& y=10

*But* the interesting thing is that some people suggested that the
problem was lying in the way that the server and the client
talked to each other.

Let me first post the explanation from Bill Meier, on the
Ethereal-users mailing-list, then the one from Chris Marget on the
comp.protocols.tcp-ip newsgroup.

Bill Meier wrote :
> The short answer:
>
> "TCP_NODELAY" should be enabled on the TCP connection to
> the database on both the client and server side of the connection.
> (This is also known as "disabling the Nagle Algorithm").
> (Based upon your capture I can almost guarantee that TCP_NODELAY is
> *not* enabled on your Database TCP connection on the server side).
>
> The use of TCP_NODELAY is a database option for a different database server with
> which I'm familiar. I suggest you consult your DBA with respect to PostgreSQL.
> (Also Google "database tcp_nodelay" & etc for information).
>
> The longer answer:
>
> Delays may be observerd in client/server query/response types
> of applications over a TCP connection with the Nagle Algorithm enabled.
>
> The presence or absence of a delay is dependent upon the length of
> the query and/or the query response; this causes much confusion when trying
> to analyze delay problems because the delays will seem to "come and go"
> depending upon the exact length of the query and/or response.
>
> There is also much confusion as to the exact nature of the problem.
>
> I believe the current edition of Stevens' "TCPIP Illustrated" gives a pretty clear
> explanation of the issue.
>
>
> The details in your case:
>
> I believe that due to the Nagle Algorithm, for the specific query reponse
> shown in the capture, each time your server sends a "short" packet,
> the server waits for an ACK from the client before sending the next packet.
> In your case, the server application is sending the response in chunks of
> 8192 data bytes which results in a "short" packet every 6 packets).
>
> (I would suspect that a capture of a query/response from one of the
> workstations on which the query runs "very fast" may show that the query response
> is of a different length).


Chris Marget wrote :
>> > The developper of the psqlodbc driver told me that he had used the
>> > same library that is used in pgAdmin (libpq.dll) to talk to the
>> > server !
>
> Are these two applications hitting different socket code on the server
> end?
>
> I have an area for you to explore: There's a method of socket
> programming in windows called "io completion ports" (or somesuch).
> Rather than streaming data into the socket UNIX style, the application
> code allocates a buffer somewhere, fills it and then tells the stack:
> "The buffer is here, please send it, then let me know when I can have
> the buffer back."
>
> Some see an advantage to this type of coding because it eliminates a
> copy of data from the application memory area into stack memory area.
>
> The problem comes from the fact that the buffer cannot be reused by the
> application until the receiving TCP has ACKed all the data.
>
> Here's what I think is happening:
>
> Your server is using one of these zero-copy stack methods. It needs to
> send 44078 bytes to the client. The buffer I mentioned is only 8KB, so
> it must be reused 6 times in order to send all 44078 bytes.
>
> So, 8KB is copied in and the stack is asked to send.
> Bad luck causes it to take ~200ms to receive ACKs for all that data
> because of your stack tuning and timing issues.
>
> The buffer becomes available again and another 8KB is copied in.
> ~200ms again.
>
> This cycle repeats 6 times until all 44078 bytes are send and ACKed.
> Nearly 1 second has elapsed.
>
> A smarter application on the server would have used a bigger buffer.
>
> An even smarter application would have used many buffers. 8KB buffers
> are okay if you fill buffer A, notify the stack, fill buffer B, notify
> the stack, etc...
>
> Something else entirely may be going on, but this is my guess.


Both these explanations are far beyond my knowledge !
I am quite confident though that the developers of the libpq.dll
will understand what this is all about.

Was this information of some help to you ?

Best regards,
--
Arnaud



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

Re: Slow query through ODBC

Since it works with different speeds in different clients, it *probably*
isn't on the server side. I'm far from sure on that though, there may be
other paramters that are implicitly changed when ODBC is used.

It's interesting to note that your problem is with a single large field.
If you query for approximatly the same amount of data *but in several
small fields*, do you get the same behaviour? Or does it happen only
with large fields?


> I posted a TCP/IP dump of the dialog between my workstation=20
> and the postgresql server to a TCP/IP newsgroup.
> I fixed the problem by changing a parameter in my TCP/IP=20
> configuration (on my workstation, not on the server).
> I have set the TcpAckFrequency key to 1, as suggested by=20
> someone on the comp.protocols.tcp-ip NG. The procedure is=20
> described in :=20
> http://support.microsoft.com/?scid=3Dkb%3Ben-us%3B328890&x=3 D15&y=3D10

Interesting - I wouldn't have expected that to make a difference.




> > The short answer:=20
> >=20
> > "TCP_NODELAY" should be enabled on the TCP connection to=20
> the database=20
> > on both the client and server side of the connection.
> > (This is also known as "disabling the Nagle Algorithm").
> > (Based upon your capture I can almost guarantee that TCP_NODELAY is
> > *not* enabled on your Database TCP connection on the server side).
> >=20
> > The use of TCP_NODELAY is a database option for a different=20
> database=20
> > server with which I'm familiar. I suggest you consult your=20
> DBA with respect to PostgreSQL.
> > (Also Google "database tcp_nodelay" & etc for information).

We enable TCP_NODELAY on both server and client, when available. And it
is available - I've double-checked and that code is indeed compiled into
both libpq and the server on 8.1 at least.


> > I have an area for you to explore: There's a method of socket=20
> > programming in windows called "io completion ports" (or somesuch).
> > Rather than streaming data into the socket UNIX style, the=20
> application=20
> > code allocates a buffer somewhere, fills it and then tells=20
> the stack:
> > "The buffer is here, please send it, then let me know when=20
> I can have=20
> > the buffer back."
> >=20
> > Some see an advantage to this type of coding because it=20
> eliminates a=20
> > copy of data from the application memory area into stack=20
> memory area.

We don't use this.
We do, however, buffer it in an application side driver and then send it
off with a single call to send(). On the server, that is. Perhaps it can
have a similar effect.

But I don't see how this can make a difference between pgadmin and odbc
and psql, since they all use the same code!
Might be interesting to write up a small test program that uses just
libpq and see what is required to repro the problem there.


//Magnus

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Slow query through ODBC

Magnus Hagander a =E9crit :
> Since it works with different speeds in different clients, it *probably=
*
> isn't on the server side. I'm far from sure on that though, there may b=
e
> other paramters that are implicitly changed when ODBC is used.

Yes, that was my first impression also. But the fact that the=20
server waits for an ACK from the client before it goes on sending=20
packet is quite strange, isn't it ?

> It's interesting to note that your problem is with a single large field=
..
> If you query for approximatly the same amount of data *but in several
> small fields*, do you get the same behaviour? Or does it happen only
> with large fields?

I have splitted the data into 4 fields.
The largest field was ~23.000 characters long, so I made 4 fields=20
containing. The first one contains the first 6000, the second one=20
from 6001 to 12000, etc...
The query took about half the time to run !

> We enable TCP_NODELAY on both server and client, when available. And it
> is available - I've double-checked and that code is indeed compiled int=
o
> both libpq and the server on 8.1 at least.

I run 8.1.2, so I assume that it is enabled on my workstation and=20
on the server.

> But I don't see how this can make a difference between pgadmin and odbc
> and psql, since they all use the same code!

Yes, I really don't understand either !
But even though all use libpq.dll, they might not handle the data=20
the same way. Mayboe ODBC needs to check for data type, field=20
length, etc... and the other frontends do not need this kind of=20
information ?

Regards
--
Arnaud


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

> Magnus Hagander a =E9crit :
> > Since it works with different speeds in different clients, it=20
> > *probably* isn't on the server side. I'm far from sure on=20
> that though,=20
> > there may be other paramters that are implicitly changed=20
> when ODBC is used.
>=20
> Yes, that was my first impression also. But the fact that the=20
> server waits for an ACK from the client before it goes on=20
> sending packet is quite strange, isn't it ?

Yes. Most definitly.


> > It's interesting to note that your problem is with a single=20
> large field.
> > If you query for approximatly the same amount of data *but=20
> in several=20
> > small fields*, do you get the same behaviour? Or does it=20
> happen only=20
> > with large fields?
>=20
> I have splitted the data into 4 fields.
> The largest field was ~23.000 characters long, so I made 4=20
> fields containing. The first one contains the first 6000, the=20
> second one from 6001 to 12000, etc...
> The query took about half the time to run !

Interesting. Did it make any difference to the timings in psql and/or pgadm=
in?


> > We enable TCP_NODELAY on both server and client, when=20
> available. And=20
> > it is available - I've double-checked and that code is=20
> indeed compiled=20
> > into both libpq and the server on 8.1 at least.
>=20
> I run 8.1.2, so I assume that it is enabled on my workstation=20
> and on the server.

Yup.


> > But I don't see how this can make a difference between pgadmin and=20
> > odbc and psql, since they all use the same code!
>=20
> Yes, I really don't understand either !
> But even though all use libpq.dll, they might not handle the=20
> data the same way. Mayboe ODBC needs to check for data type,=20
> field length, etc... and the other frontends do not need this=20
> kind of information ?

Hm. That's an interesting observation. When you do a query with ODBC, does =
it do any "extra queries" automatically on the system tables? Enable statem=
ent logging on the server and see if something weird shows up.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Slow query through ODBC

>> I have splitted the data into 4 fields.
>> The largest field was ~23.000 characters long, so I made 4
>> fields containing. The first one contains the first 6000, the
>> second one from 6001 to 12000, etc...
>> The query took about half the time to run !
>
> Interesting. Did it make any difference to the timings in psql and/or pgadmin?

No, not at all. Actually, it even runs a tiny bit slower in
pgAdmin (2.05 sec vs 1.95 for the "one large field" version).


>> Yes, I really don't understand either !
>> But even though all use libpq.dll, they might not handle the
>> data the same way. Mayboe ODBC needs to check for data type,
>> field length, etc... and the other frontends do not need this
>> kind of information ?
>
> Hm. That's an interesting observation. When you do a query with ODBC, does it do any "extra queries" automatically on the system tables? Enable statement logging on the server and see if something weird shows up.

No, no query on the system tables.
I am not very familiar with the log though, maybe Ludek could tell
us more about that ?

Regards
--
Arnaud


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Slow query through ODBC

> >>Yes, I really don't understand either !
> >>But even though all use libpq.dll, they might not handle the
> >>data the same way. Mayboe ODBC needs to check for data type,
> >>field length, etc... and the other frontends do not need this
> >>kind of information ?
> >
> >Hm. That's an interesting observation. When you do a query with ODBC, does
> >it do any "extra queries" automatically on the system tables? Enable
> >statement logging on the server and see if something weird shows up.
>
> No, no query on the system tables.
> I am not very familiar with the log though, maybe Ludek could tell
> us more about that ?

If I remember it right we measure the time problem in LIBPQ_send_query
function in connection.c (I'm not sure with filename).
Magnus, could you take a look at the code? Maybe you find the problem.
I'm sorry I have no time for it today.
BTW we change the code between 08.01.0102 and 08.01.0200 from PQexec
to PQsendquery and PQgetresult (I write this from my head so it could
be little different names). But I think Arnaud has problem even with
08.01.0102.

Regards,

Luf

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

> > >>Yes, I really don't understand either !
> > >>But even though all use libpq.dll, they might not handle the data=20
> > >>the same way. Mayboe ODBC needs to check for data type, field=20
> > >>length, etc... and the other frontends do not need this kind of=20
> > >>information ?
> > >
> > >Hm. That's an interesting observation. When you do a query=20
> with ODBC,=20
> > >does it do any "extra queries" automatically on the system tables?=20
> > >Enable statement logging on the server and see if=20
> something weird shows up.
> >=20
> > No, no query on the system tables.
> > I am not very familiar with the log though, maybe Ludek=20
> could tell us=20
> > more about that ?
>=20
> If I remember it right we measure the time problem in=20
> LIBPQ_send_query function in connection.c (I'm not sure with=20
> filename).
> Magnus, could you take a look at the code? Maybe you find the problem.
> I'm sorry I have no time for it today.
> BTW we change the code between 08.01.0102 and 08.01.0200 from=20
> PQexec to PQsendquery and PQgetresult (I write this from my=20
> head so it could be little different names). But I think=20
> Arnaud has problem even with 08.01.0102.

That's very interestnig information. Because that was one thing that was
different.

Arnaud, any chance you can giev it a try with 8.01.0200?

//Magnus

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

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

Re: Slow query through ODBC

> > If I remember it right we measure the time problem in
> > LIBPQ_send_query function in connection.c (I'm not sure with
> > filename).
> > Magnus, could you take a look at the code? Maybe you find the problem.
> > I'm sorry I have no time for it today.
> > BTW we change the code between 08.01.0102 and 08.01.0200 from
> > PQexec to PQsendquery and PQgetresult (I write this from my
> > head so it could be little different names). But I think
> > Arnaud has problem even with 08.01.0102.
>
> That's very interestnig information. Because that was one thing that was
> different.
>
> Arnaud, any chance you can giev it a try with 8.01.0200?

Please try both, 08.01.0102 and 08.01.0200. Does it differ?

Thanks,

Luf

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

Ludek Finstrle a =E9crit :
>> That's very interestnig information. Because that was one thing that w=
as
>> different.
>>=20
>> Arnaud, any chance you can giev it a try with 8.01.0200?
>=20
> Please try both, 08.01.0102 and 08.01.0200. Does it differ?

I just tried both versions and there is absolutely no difference.

I'll stick with .0200 of course !

Regards
--
Arnaud


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

TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through ODBC)

Arnaud Lesauvage writes:

> Magnus Hagander a =E9crit :
>> Since it works with different speeds in different clients, it *probabl=
y*
>> isn't on the server side. I'm far from sure on that though, there may =
be
>> other paramters that are implicitly changed when ODBC is used.
>
> Yes, that was my first impression also. But the fact that the server
> waits for an ACK from the client before it goes on sending packet is
> quite strange, isn't it ?

I don't think that's what happens.

Those are two very different but unfortunately related things.

1) the Nagle algorithm (disabled by TCP_NO_DELAY) is an optimization
when sending. It prevents sending many small packets, by waiting a
little bit for them to become bigger. The price is a higher latency.

2) the half-ACK frequency is an similar optimization when
receiving. It cuts the number of sent acknowledgements by two, by
waiting a little bit to receive a 2nd packet, acknowledging the two
packets using only one ACK. You can disable it with TcpAckFrequency in
the registry.


So far, those two have nothing in common. But the thing is, TCP
_piggybacks_ acknowledgements packets of data flowing in one direction
together with data packets flowing in the opposite direction.

So, if you force TCP to send data more frequently with TCP_NO_DELAY,
then it will also send ACKs (of the opposite communication) more
frequently as a side effect. Once you decided to send a packet, then
better put the maximum information in it!

Same thing if you force TCP to send ACKs more frequently using
TcpAckFrequency: then you'll get some TCP_NO_DELAY effect as a
bonus. Once you decided to send an ACK, then better send the staging
data in the same packet!

So you see that TcpAckFrequency can (in some scenarios) play the role
of TCP_NO_DELAY. And vice-versa. That's probably what you experienced.




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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Marc Herbert a =E9crit :
> Same thing if you force TCP to send ACKs more frequently using
> TcpAckFrequency: then you'll get some TCP_NO_DELAY effect as a
> bonus. Once you decided to send an ACK, then better send the staging
> data in the same packet!

OK, but Magnus is affirmative that TCP_NO_DELAY is enabled on both=20
the client and the server.
So sending ACKs more frequently should not change the behaviour of=20
the Nagle algorithm. Or am I still misunderstanding something ?

Regards
--
Arnaud


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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Arnaud Lesauvage writes:
> OK, but Magnus is affirmative that TCP_NO_DELAY is enabled on both
> the client and the server.
> So sending ACKs more frequently should not change the behaviour of
> the Nagle algorithm.

Yeah. The speculations quoted in
http://archives.postgresql.org/pgsql-odbc/2006-02/msg00046.p hp
as to the server behavior were completely wrong: we do use NODELAY
and we don't have any weird zero-copy algorithm. However, it is
true that we push out data in 8K bufferloads, which doesn't match
well to typical TCP MTU sizes. I've occasionally wondered whether
we should not try to make the bufferload a multiple of the MTU
size when using a TCP connection, just so that no "short" packets
get sent unnecessarily. (For a known MTU length, it'd be trivial
to change the hard-wired buffer size on the server side just to
experiment and see if there's any speedup.)

None of this explains why odbc is slow when psql isn't, though,
especially not if you are using one of the recent odbc releases
that relies on libpq. The wire-level behavior certainly ought
to be the same for anything using libpq ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Tom Lane a =E9crit :
> None of this explains why odbc is slow when psql isn't, though,
> especially not if you are using one of the recent odbc releases
> that relies on libpq. The wire-level behavior certainly ought
> to be the same for anything using libpq ...

Yes.
I am trying to figure out what the difference is between an ODBC=20
call and a "direct" call, from another application...
The issue might be at a higher level, but I have no clue right now...

Regards
--
Arnaud


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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

This is a multi-part message in MIME format.

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

Hi,

Why don't you use tcpdump, or another sniffer, in order to check that, =
and
post the result here?

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] De la part de Arnaud Lesauvage
Envoy=E9 : vendredi, 10. f=E9vrier 2006 17:01
=C0 : Tom Lane
Cc : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] TCP_NO_DELAY & TcpAckFrequency (Re: Slow query =
through

Tom Lane a =E9crit :
> None of this explains why odbc is slow when psql isn't, though,=20
> especially not if you are using one of the recent odbc releases that=20
> relies on libpq. The wire-level behavior certainly ought to be the=20
> same for anything using libpq ...

Yes.
I am trying to figure out what the difference is between an ODBC call =
and a
"direct" call, from another application...
The issue might be at a higher level, but I have no clue right now...

Regards
--
Arnaud


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


------=_NextPart_000_0025_01C63079.EA640F80
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
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwMjEzMDc0 NjAwWjAjBgkqhkiG
9w0BCQQxFgQUxUD9NeyltEH7bxRBnC/tj5fKwbwwZwYJKoZIhvcNAQkPMVow WDAKBggqhkiG9w0D
BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI KoZIhvcNAwICASgw
BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkG A1UEBhMCWkExJTAj
BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJz
b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBaWiGGq16JsgXriho0RtOLMIGH BgsqhkiG9w0BCRAC
CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0 aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5n IENBAhBaWiGGq16J
sgXriho0RtOLMA0GCSqGSIb3DQEBAQUABIGAW6mtACmWqiqdJ4yxJr8J3IyO Q5ikc1KokCfBHOQG
tTzBtvlmoOiHuvAvUxfyh1Rpdl8fdch3TwbhhRdGgq9C+euJGtP9g1MJvG+a T04FipJQH+v7sZ7a
0ZenTh8Bwq4UePyD4IiI6fm660I+ZFAiRdwbQP2Vo6qjjeSAeYNs3XoAAAAA AAA=

------=_NextPart_000_0025_01C63079.EA640F80--

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Philippe Lang a =E9crit :
> Why don't you use tcpdump, or another sniffer, in order to check that, =
and post the result here?

Hi,

Sure !
Here are the two dumps.
http://tehwild.free.fr/divers/dumps.zip
One is a TCP dump of the query through pgAdmin, the other one
through psqlODBC.
The first obvious difference is that the window size is not the=20
same, but I don't know how to interpret that.


Regards
--
Arnaud



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Arnaud Lesauvage writes:
>
> Sure !
> Here are the two dumps.
> http://tehwild.free.fr/divers/dumps.zip
> One is a TCP dump of the query through pgAdmin, the other one
> through psqlODBC.
> The first obvious difference is that the window size is not the same,
> but I don't know how to interpret that.

The best free tools I know to analyze such issues is tcptrace + xplot






---------------------------(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: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

This is a multi-part message in MIME format.
--------------090102070603020602060707
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

You may try "ethereal" which can give you more details than the TCP
dumps with a structured view of packets and protocol layers.

Olivier.

Marc Herbert wrote:

>Arnaud Lesauvage writes:
>
>
>>Sure !
>>Here are the two dumps.
>>http://tehwild.free.fr/divers/dumps.zip
>>One is a TCP dump of the query through pgAdmin, the other one
>>through psqlODBC.
>>The first obvious difference is that the window size is not the same,
>>but I don't know how to interpret that.
>>
>>
>
>The best free tools I know to analyze such issues is tcptrace + xplot
>
>
>
>
>
>
>---------------------------(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
>
>
>
>


--------------090102070603020602060707
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit







You may try "ethereal" which can give you more details than the TCP
dumps with a structured view of packets and protocol layers.



Olivier.



Marc Herbert wrote:

Arnaud Lesauvage  writes:

  

Sure !

Here are the two dumps.



One is a TCP dump of the query through pgAdmin, the other one

through psqlODBC.

The first obvious difference is that the window size is not the same,

but I don't know how to interpret that.

    

The best free tools I know to analyze such issues is tcptrace + xplot













---------------------------(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: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

O B a =E9crit :
> You may try "ethereal" which can give you more details than the TCP
> dumps with a structured view of packets and protocol layers.

The included dumps *are* ethereal dumps, actually.
There must be a reason why psqlODBC sets a different RWIN than the=20
other frontends (psql and pgAdmin), but I don't know which one.

Regards
--
Arnaud


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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

O B writes:

> You may try "ethereal" which can give you more details than the TCP
> dumps with a structured view of packets and protocol layers.

ethereal is a great general-purpose tool but is not good at analyzing
TCP performance issues, because you only get numbers. With
tcptrace/xplot you get graphical timelines of sequence numbers and of
window sizes; I don't think ethereal can't match that. It's the same
difference between an excel spreadsheet and the corresponding graph.

Of course you have to be sure that your performance issue is somewhat
related to TCP parameters, else there is no need to go into tcptrace.





---------------------------(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: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Arnaud Lesauvage writes:

> O B a =E9crit :
>> You may try "ethereal" which can give you more details than the TCP
>> dumps with a structured view of packets and protocol layers.
>
> The included dumps *are* ethereal dumps, actually.

I think Olivier was talking about ethereal the browser, not about
ethereal the capture front-end.


> There must be a reason why psqlODBC sets a different RWIN than the
> other frontends (psql and pgAdmin), but I don't know which one.

By the way this is the receive buffer size on the _client_, right? (I
would be surprised that this indirectly changes server settings...)


On linux you can trace/check all modifications of the buffer sizes
like this:

strace -e trace=3Dsetsockopt testcode



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

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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Marc Herbert a =E9crit :
> Arnaud Lesauvage writes:
>> The included dumps *are* ethereal dumps, actually.
>=20
> I think Olivier was talking about ethereal the browser, not about
> ethereal the capture front-end.

OK, sorry for that, I did not know there was another ethereal.

>> There must be a reason why psqlODBC sets a different RWIN than the
>> other frontends (psql and pgAdmin), but I don't know which one.
>=20
> By the way this is the receive buffer size on the _client_, right? (I
> would be surprised that this indirectly changes server settings...)

Yes. As I wrote in my previous post, I don't know how to interpret=20
the logs.
BTW, I just realized that the URL to download the logs was=20
incorrect. The correct URL is http://thewild.free.fr/Divers/dumps.zip

> On linux you can trace/check all modifications of the buffer sizes
> like this:
>=20
> strace -e trace=3Dsetsockopt testcode

I am on a WinXP box. It is pretty bad for debugging purposes of=20
course...

Reagards
--
Arnaud


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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through ODBC)

This is a multi-part message in MIME format.

------=_NextPart_000_0000_01C6B5A1.5A3B5DC0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

pgsql-odbc-owner@postgresql.org wrote:

>> I am still experiencing this behaviour, even though I installed
>> postgresql on a brand new server, and my workstation was also just
>> reinstalled. Since the problem is still there, I'd like to know if
>> newer versions of psqlodbc could have changed things.
>> I am still using the latest official releases (8.01.0200), but maybe
>> there are beta release I could try ?
>
> There is new version 08.02.0002 on pgfoundry.org. And in bug
> entry BiDiMode* you can find the URL for the latest .dll
> files located at Hiroshi pages.
> The 08.02 version is based on 07.03 - not 08.01 so it could helps you.

The link for Hiroshi's website is the following:

http://www.geocities.jp/inocchichichi/psqlodbc/index.html

---------------
Philippe Lang
Attik System


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

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIII/zCCAocw
ggHwoAMCAQICEEdYYafa1wkm0CP4DOffAiswDQYJKoZIhvcNAQEEBQAwYjEL MAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNV BAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MDYyMjE3MzE0NloX DTA3MDYyMjE3MzE0
NlowZzENMAsGA1UEBBMETGFuZzERMA8GA1UEKhMIUGhpbGlwcGUxFjAUBgNV BAMTDVBoaWxpcHBl
IExhbmcxKzApBgkqhkiG9w0BCQEWHHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0 ZW0uY2gwgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBAOfvCPREDwfIbzsNRoyBDoSOWfI2NNWd RRMJXuj5ldF22gSQ
6MiXFUjt4HlRIwPb8x13hrv89GW/SmLjFThxf7NKZ8dPTZzp4ZlvrQBRAYhS uTQOJ8azyzruZu54
Ak7fa3pz26Q3B8kg4knZBz3O9Q4K1tK8AEdjNQpyDg/iDdzjAgMBAAGjOTA3 MCcGA1UdEQQgMB6B
HHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQF
AAOBgQAAzz+I6MrhWjbfWSgngW/DthHvXiTR6x0cCeb4T5mBb5dgueqgLnrh /2blsbCQuLWh0URe
/1l/QQ2dY2elG3PEDPv9cCl/LcwsCsE1Nj3bGM4gdVx9crYvTw5dyyUuDq1K ShV3uziE+o5Lyfx4
qWRYd6/yIBDVarohdpL8J0DJczCCAy0wggKWoAMCAQICAQAwDQYJKoZIhvcN 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
ZWVtYWlsIElzc3VpbmcgQ0ECEEdYYafa1wkm0CP4DOffAiswCQYFKw4DAhoF AKCCAdgwGAYJKoZI
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwODAxMTcz MzIzWjAjBgkqhkiG
9w0BCQQxFgQUFPYvmPt4o20oqydOG+L+bJT58+gwZwYJKoZIhvcNAQkPMVow WDAKBggqhkiG9w0D
BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI KoZIhvcNAwICASgw
BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkG A1UEBhMCWkExJTAj
BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJz
b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBHWGGn2tcJJtAj+Azn3wIrMIGH BgsqhkiG9w0BCRAC
CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0 aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5n IENBAhBHWGGn2tcJ
JtAj+Azn3wIrMA0GCSqGSIb3DQEBAQUABIGAJ/rW5e82kjp1OIIkGOdASljO iWr7utczkTId8qsZ
AixLUQ2eqUr3D+1I7v8bbwNIkDGqh1I94HY4F+p4Il9edT0X8EzLiQSsbGnL BwIvmlc7EUrWbcYt
xCyFBtIdCqAirIa1ImXacupJkmz5ium2Nl8TILgPID82lwgYykQTFlEAAAAA AAA=

------=_NextPart_000_0000_01C6B5A1.5A3B5DC0--

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through ODBC)

This is a multi-part message in MIME format.

------=_NextPart_000_0000_01C6B5A2.8BB71A00
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

pgsql-odbc-owner@postgresql.org wrote:
> Hi list
>
> This is a follow-up to
> http://archives.postgresql.org/pgsql-odbc/2006-02/msg00067.p hp
>
> Let me sum up the problem :
> I run a query in pgadmin into a file (to be sure that all
> data is transfered). It runs very fast (in about 10sec), and
> data is transfered from the server at a very fast rate (~1MB per
> second). I run the same query via psqlodbc in a VBA application. It
> takes ages to run and data is transfered at a very slow pace (~80kB
> per second).

Arnaud,

What client exactly do you use for querying the server in VBA? Are you maybe using MS Access? If yes, what version of Access are you using? 2003? And are you using an Access 2000 compatible MDB maybe?

Otherwise, what client data access package are you using from VBA? DAO? ADO?

---------------
Philippe Lang
Attik System


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

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIII/zCCAocw
ggHwoAMCAQICEEdYYafa1wkm0CP4DOffAiswDQYJKoZIhvcNAQEEBQAwYjEL MAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNV BAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MDYyMjE3MzE0NloX DTA3MDYyMjE3MzE0
NlowZzENMAsGA1UEBBMETGFuZzERMA8GA1UEKhMIUGhpbGlwcGUxFjAUBgNV BAMTDVBoaWxpcHBl
IExhbmcxKzApBgkqhkiG9w0BCQEWHHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0 ZW0uY2gwgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBAOfvCPREDwfIbzsNRoyBDoSOWfI2NNWd RRMJXuj5ldF22gSQ
6MiXFUjt4HlRIwPb8x13hrv89GW/SmLjFThxf7NKZ8dPTZzp4ZlvrQBRAYhS uTQOJ8azyzruZu54
Ak7fa3pz26Q3B8kg4knZBz3O9Q4K1tK8AEdjNQpyDg/iDdzjAgMBAAGjOTA3 MCcGA1UdEQQgMB6B
HHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQF
AAOBgQAAzz+I6MrhWjbfWSgngW/DthHvXiTR6x0cCeb4T5mBb5dgueqgLnrh /2blsbCQuLWh0URe
/1l/QQ2dY2elG3PEDPv9cCl/LcwsCsE1Nj3bGM4gdVx9crYvTw5dyyUuDq1K ShV3uziE+o5Lyfx4
qWRYd6/yIBDVarohdpL8J0DJczCCAy0wggKWoAMCAQICAQAwDQYJKoZIhvcN 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
ZWVtYWlsIElzc3VpbmcgQ0ECEEdYYafa1wkm0CP4DOffAiswCQYFKw4DAhoF AKCCAdgwGAYJKoZI
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwODAxMTc0 MTU2WjAjBgkqhkiG
9w0BCQQxFgQURAsmXB0RKwvM7mfC5TBAGeWdZykwZwYJKoZIhvcNAQkPMVow WDAKBggqhkiG9w0D
BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI KoZIhvcNAwICASgw
BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkG A1UEBhMCWkExJTAj
BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJz
b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBHWGGn2tcJJtAj+Azn3wIrMIGH BgsqhkiG9w0BCRAC
CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0 aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5n IENBAhBHWGGn2tcJ
JtAj+Azn3wIrMA0GCSqGSIb3DQEBAQUABIGAifi7cPZ8//WoP3A7kM0gMHFB CH6C0A94fr+76T08
5hZzP+v+HpKJ+18O1ZmTnZDN088ty7N3UZ7/jd5HSU7GlzT7nHM1KsaECJrC snUpkL9+pRsOqK9h
eQzKF6YknqKxROoQgWZpffk0vIJm4a+axDWUfgnGrq5F5f1syUDyNpoAAAAA AAA=

------=_NextPart_000_0000_01C6B5A2.8BB71A00--

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Philippe Lang a =E9crit :
> What client exactly do you use for querying the server in VBA? Are you =
maybe using MS Access? If yes, what version of Access are you using? 2003=
? And are you using an Access 2000 compatible MDB maybe?
> Otherwise, what client data access package are you using from VBA? DAO?=
ADO?


Philippe, the client is an Autocad2000 VBA macro.
I am using ADO, with a DSN-less connection (basic connection=20
string, nothing but server address, port, username and password).

Do you have a clue ?

Regards
--
Arnaud

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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through ODBC)

This is a multi-part message in MIME format.

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

Arnaud Lesauvage wrote:
> Philippe Lang a =E9crit :
>> What client exactly do you use for querying the server in
> VBA? Are you maybe using MS Access? If yes, what version of
> Access are you using? 2003? And are you using an Access 2000
> compatible MDB maybe?
>> Otherwise, what client data access package are you using from VBA?
>> DAO? ADO?=20
>=20
>=20
> Philippe, the client is an Autocad2000 VBA macro.
> I am using ADO, with a DSN-less connection (basic connection
> string, nothing but server address, port, username and password).
>=20
> Do you have a clue ?
>=20
> Regards

Hi,

I suggest you try another client, like for example MS Access 2003 with a =
2003 MDB, for example, just to make sure the problems comes from the =
driver. What you experience might be a problem that come from Autocad...

Can you make a test with DAO as well? Quite old, but might be faster as =
well...

---------------
Philippe Lang
Attik System

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

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIII/zCCAocw
ggHwoAMCAQICEEdYYafa1wkm0CP4DOffAiswDQYJKoZIhvcNAQEEBQAwYjEL MAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNV BAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MDYyMjE3MzE0NloX DTA3MDYyMjE3MzE0
NlowZzENMAsGA1UEBBMETGFuZzERMA8GA1UEKhMIUGhpbGlwcGUxFjAUBgNV BAMTDVBoaWxpcHBl
IExhbmcxKzApBgkqhkiG9w0BCQEWHHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0 ZW0uY2gwgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBAOfvCPREDwfIbzsNRoyBDoSOWfI2NNWd RRMJXuj5ldF22gSQ
6MiXFUjt4HlRIwPb8x13hrv89GW/SmLjFThxf7NKZ8dPTZzp4ZlvrQBRAYhS uTQOJ8azyzruZu54
Ak7fa3pz26Q3B8kg4knZBz3O9Q4K1tK8AEdjNQpyDg/iDdzjAgMBAAGjOTA3 MCcGA1UdEQQgMB6B
HHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQF
AAOBgQAAzz+I6MrhWjbfWSgngW/DthHvXiTR6x0cCeb4T5mBb5dgueqgLnrh /2blsbCQuLWh0URe
/1l/QQ2dY2elG3PEDPv9cCl/LcwsCsE1Nj3bGM4gdVx9crYvTw5dyyUuDq1K ShV3uziE+o5Lyfx4
qWRYd6/yIBDVarohdpL8J0DJczCCAy0wggKWoAMCAQICAQAwDQYJKoZIhvcN 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
ZWVtYWlsIElzc3VpbmcgQ0ECEEdYYafa1wkm0CP4DOffAiswCQYFKw4DAhoF AKCCAdgwGAYJKoZI
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwODAxMTc1 ODMxWjAjBgkqhkiG
9w0BCQQxFgQUv6qij17dDo4cwT8n2StmWiLoO+cwZwYJKoZIhvcNAQkPMVow WDAKBggqhkiG9w0D
BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI KoZIhvcNAwICASgw
BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkG A1UEBhMCWkExJTAj
BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJz
b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBHWGGn2tcJJtAj+Azn3wIrMIGH BgsqhkiG9w0BCRAC
CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0 aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5n IENBAhBHWGGn2tcJ
JtAj+Azn3wIrMA0GCSqGSIb3DQEBAQUABIGAqnWZisXeG87jPhY9P2raIQuO s8pkLfkdopQi11oL
eqR9b7h/kXmyQ/5zukmEQJYbwzZdr1V6z9jk1DiGIB0OWZq5j8iBDe74GHnb Wzwvzw+59tG71xYv
QoSInQqQN9XJ10HLmieWOgqoPquKgmhY7dNizjsZOG1in3C+UQb5FksAAAAA AAA=

------=_NextPart_000_0008_01C6B5A4.DD0D2CD0--

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Philippe Lang a =E9crit :
> I suggest you try another client, like for example MS Access 2003 with =
a 2003 MDB, for example, just to make sure the problems comes from the dr=
iver. What you experience might be a problem that come from Autocad...
>=20
> Can you make a test with DAO as well? Quite old, but might be faster as=
well...

I can try from within Access2000, but I don't have Access2003.
I'll try DAO as well.

Thanks for your suggestions !

--
Arnaud

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

Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Arnaud Lesauvage wrote:
> Philippe Lang a =E9crit :
>> I suggest you try another client, like for example MS Access 2003 with=
a 2003 MDB, for example, just to make sure the problems comes from the d=
river. What you experience might be a problem that come from Autocad...
>>=20
>> Can you make a test with DAO as well? Quite old, but might be faster a=
s well...
>=20
> I can try from within Access2000, but I don't have Access2003.
> I'll try DAO as well.

I found the problem !
I just used UseDeclareFetch=3D1 in the connection string, and now my=20
queries are blindingly fast !!!

I am quite amazed, I am pretty sure I tried this before... I even=20
thin I removed this setting when trying to find the cause of the=20
problemm !...

Thanks a lot to all the people who helped me !

Regards
--
Arnaud


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