Re: Slow query through ODBC

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=mydatabase;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