On 10/21/06, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx> 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
