Improve ODBC Throughput ?

Hi list !

I mostly use my PostgreSQL as a backend DB with Access2000
frontends.
The tables are linked via psqlODBC.
The problem I have is that even though queries / views run
very fast, the data take a long time to arrive to the client.
In the best situations, data is sent/received at ~500kB per
second (for instance, tables with only one column, which is
a very large text field >10.000 bytes per field).
In bad situations, I am at ~50kB per second.
If the queries / views return large resultsets, it can
seconds for the table to open., even though the query ran in
milliseconds on the server (explain analyze proves this).

What can I do to improve this throughput ? Either on the
server side, or on the ODBC client side ?

As a side note : network congestion, server acitvity is not
a good explanation. This server also is a file server, and I
transfer files at 10MB per second all the time.

Thanks for your advices !
--
Arnaud

Arnaud Lesauvage wrote:
> Hi list !
>
> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
> The tables are linked via psqlODBC.
> The problem I have is that even though queries / views run very fast,
> the data take a long time to arrive to the client.
> In the best situations, data is sent/received at ~500kB per second
> (for instance, tables with only one column, which is a very large text
> field >10.000 bytes per field).
>

How are you setting the "Text As LongVarchar" option ?
Are the columns text or memo using Access ?

regards,
Hiroshi Inoue

Hiroshi Inoue a écrit :
> Arnaud Lesauvage wrote:
>> Hi list !
>>
>> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
>> The tables are linked via psqlODBC.
>> The problem I have is that even though queries / views run very fast,
>> the data take a long time to arrive to the client.
>> In the best situations, data is sent/received at ~500kB per second
>> (for instance, tables with only one column, which is a very large text
>> field >10.000 bytes per field).
>>
>
> How are you setting the "Text As LongVarchar" option ?
> Are the columns text or memo using Access ?

TextAsLongVarchar=0 but the columns are VarChar in Access.

Arnaud Lesauvage wrote:
> Hiroshi Inoue a écrit :
>> Arnaud Lesauvage wrote:
>>> Hi list !
>>>
>>> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
>>> The tables are linked via psqlODBC.
>>> The problem I have is that even though queries / views run very fast,
>>> the data take a long time to arrive to the client.
>>> In the best situations, data is sent/received at ~500kB per second
>>> (for instance, tables with only one column, which is a very large
>>> text field >10.000 bytes per field).
>>>
>>
>> How are you setting the "Text As LongVarchar" option ?
>> Are the columns text or memo using Access ?
>
> TextAsLongVarchar=0 but the columns are VarChar in Access.

AFAIK the max text(VarChar) size in Access is 255.
Can you get the content of fields > 10000 bytes correctly
under the setting ?

regards,
Hiroshi Inoue

Hiroshi Inoue a écrit :
> Arnaud Lesauvage wrote:
>> Hiroshi Inoue a écrit :
>>> Arnaud Lesauvage wrote:
>>>> Hi list !
>>>>
>>>> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
>>>> The tables are linked via psqlODBC.
>>>> The problem I have is that even though queries / views run very fast,
>>>> the data take a long time to arrive to the client.
>>>> In the best situations, data is sent/received at ~500kB per second
>>>> (for instance, tables with only one column, which is a very large
>>>> text field >10.000 bytes per field).
>>>>
>>>
>>> How are you setting the "Text As LongVarchar" option ?
>>> Are the columns text or memo using Access ?
>>
>> TextAsLongVarchar=0 but the columns are VarChar in Access.
>
> AFAIK the max text(VarChar) size in Access is 255.
> Can you get the content of fields > 10000 bytes correctly
> under the setting ?

In fact, the only text fields I have are memo in access, but
I have very fex text fields. Most of them are character
varying of length <= 254.

Arnaud Lesauvage wrote:
> Hiroshi Inoue a écrit :
>> Arnaud Lesauvage wrote:
>>> Hiroshi Inoue a écrit :
>>>> Arnaud Lesauvage wrote:
>>>>> Hi list !
>>>>>
>>>>> I mostly use my PostgreSQL as a backend DB with Access2000 frontends.
>>>>> The tables are linked via psqlODBC.
>>>>> The problem I have is that even though queries / views run very
>>>>> fast, the data take a long time to arrive to the client.
>>>>> In the best situations, data is sent/received at ~500kB per second
>>>>> (for instance, tables with only one column, which is a very large
>>>>> text field >10.000 bytes per field).
>>>>>
>>>>
>>>> How are you setting the "Text As LongVarchar" option ?
>>>> Are the columns text or memo using Access ?
>>>
>>> TextAsLongVarchar=0 but the columns are VarChar in Access.
>>
>> AFAIK the max text(VarChar) size in Access is 255.
>> Can you get the content of fields > 10000 bytes correctly
>> under the setting ?
>
> In fact, the only text fields I have are memo in access, but I have very
> fex text fields. Most of them are character varying of length <= 254.

What is the type of the field > 10000 bytes in PG ?
If it's the text type, you have to turn on the Text As LongVarchar option.

regards,
Hiroshi Inoue

Hiroshi Inoue a écrit :
>> In fact, the only text fields I have are memo in access, but I have very
>> fex text fields. Most of them are character varying of length <= 254.
>
> What is the type of the field > 10000 bytes in PG ?
> If it's the text type, you have to turn on the Text As LongVarchar option.

Ok, good point, the type is text and the option was turned off.
I'm changing this right now.
Thanks !