Re: linked tables in MS Access

pgsql-odbc-owner(at)postgresql(dot)org wrote:

> Hello,
>
> I've read a post [1] about how MS Access is enjoyable and efficient
> when tables are connected to MS SQL.
>
> But I have MS Access front end containing linked tables to Postgres
> via ODBC. And I'm struggling with slow performance. When I want to
> open a table which contains about 8000 records it takes more then 20
> sec. When I want to move cursor at the last record it takes more than
> 60 extra seconds. I'm not able to bind comboboxes directly to larger
> (more than 60 records) linked table because unrolling takes 20 sec.
> All tables has defined primary key, of course.
> I was trying to change indexes without any result.
>
> I've already checked Postgres server log and MS Access queries are
> executed quickly (<500 ms).
>
> Is this normal behavior? Does MS Access cooperates with MS SQL such
> better than with other DBMS via odbc?
>
> Does anybody have better experience?
>
> I was trying to communicate from ASP.NET with Postgres via OLE DB
> driver and it was without any performance problems.

Hi,

I'm using MS Access, ODBC and Postgresql in several places, with big tables as well, and I don't have any performance problem.

I have always been using the default driver parameters, and it always worked for me. But maybe you should play with that a little bit. And I think a few performance tuning tips have been discussed in this list in the past.

One thing you have to check, is wether you are using the MS Access 2000 format, under MS Access 2003. There is huge performance loss in this configuration. Convert your database into the 2003 format, and problems will disappear.

Second thing: have you the opportunity to run tcpdump somewhere between the server and the client? You can activate the log of the server, too. This should give you useful "timing" informations.

Cheers,

Philippe