Re: Improve ODBC Throughput ?

[Arnaud Lesauvage <thewild(at)freesurf(dot)fr>]

greg(dot)campbell(at)us(dot)michelin(dot)com a écrit :
> You say that you have a performance issue.
>
> 1. Cache Size
> I would guess that the problem may be in how many records you retrieve at a
> time.
> In VBA code with ADO , it is sometimes helpful to use the Recordset
> CacheSize property to pull more than one row at a time.

I will try this !
I never used this setting, thansk for pointing this out !

> 2.Declare Fetch
> In Access, you do not have this fine control. Some psqlODBC user have
> experimented with the DECLARE/FETCH option in the psqlODBC settngs.I am not
> entirely sure how Declare/Fetch works though, I have never used it.

My tables are linked with these options :
UseDeclareFetch=1
UseServerSidePrepare=1
Fetch=10000
TrueIsMinus1=1
BoolsAsChar=0
TextAsLongVarchar=0
RowVersioning=1
RecognizeUniqueIndexes=1


> 3. General design for performance
> I don't know much about your PostgreSQL database and Access setup, but I
> can imagine a situation where you are trying to open a linked table which
> has millions of rows and some of the fields are of type TEXT, essentially
> BLOBs that Access must treat as MEMO. Simply trying to open something like
> that whole is a pretty bad idea for a transactional application. It is
> usually best to filter,...retrieve the fewest rows to suit the users need,
> and to do other transactions explicitly one records at a time on primary
> keys.

All my text fields are maximum 254 characters long (to be 
recognized as varchar in Access).
I only have very few memo fields, and only when they are 
compulsorey.
I think the DB design is OK in my case.

> Lastly, you could turn on the ODBC trace or the psqlODBC MyLog to looks for
> less obvious issues, since you have ruled out the network.

I'll do that as a last solution, I don't have much time to 
dig into these huge logs. But I will if I don't find 
anything else.

> Much Luck to you.

Thanks, I might need luck ! ;)