Re: Improve ODBC Throughput ?

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.

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.

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.

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.

Much Luck to you.

Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg(dot)campbell(at)us(dot)michelin(dot)com

Arnaud Lesauvage
<thewild(at)freesurf
.fr> To
Sent by: "Obe, Regina"
pgsql-odbc-owner@ <robe(dot)dnd(at)cityofboston(dot)gov>
postgresql.org cc
pgsql-odbc(at)postgresql(dot)org
Subject
10/19/2006 10:24 Re: [ODBC] Improve ODBC Throughput
?

Obe, Regina a écrit :
> What kinds of indexes do you have on your tables? I think the way
> Access works to limit transfer it first pulls the indexes and probably
> the primary one first to determine how to fetch and how many to fetch.
> I suspect if you have no indexes performance would be really bad.
>
> Also which version of psqlODBC driver are you using?

I have integer primary keys on most tables, if not all of them.
Even my views have an integer primary key (at least,
something that can be used as an integer pkey in Access).

My version of psqlODBC is 8.1.2.0.

I forgot to mention something important : I obtain the best
performances (500kB / sec, cf my first post) when I am NOT
in access.
In a VBA module (Autocad, not Access), I open an ADO
connection to PostgreSQL and directly query the server (no
linked table involved here). But still, this is "only"
500kB/sec...

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly