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=mydata base;UID=myuser;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

> 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=mydata base;UID=myuser;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

Ludek Finstrle a écrit :
>> 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.

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.

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

Yes. Actually, pgAdmin asks me if I want all rows (default is to
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
seconds also.

>> 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=mydata base;UID=myuser;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 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 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 ?


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

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

Ludek Finstrle a écrit :
>> >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 ;-)

Good point Ludek. I double checked my data, and it definitively
good in the output.
FYI, the largest field returned is 23581 characters long. The
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
>> 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 tried both parameters (they look OK because ADO did not throw an
error), but they did not help.
Maybe I should add that the query is stored in an ADO recordset,
which is read-only and forward-only ?
It is the .open method of this recordset that actually takes 30
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.
>
> 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
panel to generate connectionstrings, I should have thought about
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

> 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

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...6/msg00593.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