On 10/21/06, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> Our Windows-based db server has to integrate with users that work regularily
> with Access.When attempting to import user's data from Access MDB files to
> PostgreSQL, we try on eof two things: either import using EMS SQL Manager's
> Data Import from Access utility, or export from Access to Postgresql via an
> odbc-based connectionin both cases, the performance is just awful.
> Performance with Tcl's native postgres driver seems rather fine running from
> Windows a Windows client, BTW.
>
> ODBC is often blamed for this sort of thing - I have the 8.01.02 release
> dated 2006.01.31. Everything appears to be at its default setting.
>
> Is this the reason for the rather depressing performance fromt/to access and
> can anything be done about it?
i suspect the problem might be access...the odbc driver now uses libpq
library over postgresql. first thing to do is to monitor what hundred
sql statements access decides to write when you want to, say, look up
a record. the results might suprise you! one gotcha that pops up now
and then is that odbc clients somtimes experience wierd delays in
certain configurations. afaik this has never been solved.
1. turn on full statement logging (log_statement='all'). i prefer to
redirect everything to pg_log and rotate daily, with a month or so of
log files going back. turning on log_duration helps.
2. tail the log, do random things in access and watch the fireworks.
if nothing odd is really going on, then you may have an odbc issue.
more than likely though, access is generating wacky sql. solution in
this case is to code around that in access.
merlin