Skip site navigation (1) Skip section navigation (2)

Re: Slow query through ODBC

From: "Magnus Hagander" <mha(at)sollentuna(dot)net>
To: "Arnaud Lesauvage" <thewild(at)freesurf(dot)fr>,<pgsql-odbc(at)postgresql(dot)org>
Cc: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>,"Ludek Finstrle" <luf(at)pzkagis(dot)cz>
Subject: Re: Slow query through ODBC
Date: 2006-02-10 09:58:33
Message-ID: 6BCB9D8A16AC4241919521715F4D8BCE6C7FD9@algol.sollentuna.se (view raw or flat)
Thread:
Lists: pgsql-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 
> 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

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

<snip>


> > 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).

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

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

Responses

pgsql-odbc by date

Next:From: Ludek FinstrleDate: 2006-02-10 10:24:26
Subject: Re: Problem using ODBC from .NET framework
Previous:From: Arnaud LesauvageDate: 2006-02-10 09:23:27
Subject: Re: Slow query through ODBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group