Re: [INTERFACES] ODBC is slow with M$-Access Report

Jose' Soares Da Silva wrote:

> Hi,
>
> I'm using PostgreSQL-6.3 / psqlodbc 06.30.0242 / M$-Access97.
> I created a REPORT with a leftjoin that takes a lot of time.
> There are 3850 rows in the main table.
> PostgreSQL takes about..............: 960 secs to print all records.
> The same test using MySQL takes only: 85 secs and the same
> test using M$-Access takes about....: 45 secs.

This is never a simple comparison. Performance using Access/PostgreSQL can be
greatly effected by the driver settings. In particular, if you tell MS Access
that there is a unique index on a table, at link time, or to "Recognize Unique
Indexed" (and there is one), Access will generate queries which the backend will
not respond to very optimally. Especially where outer joins are concerned.
These queries are characterized by numerous OR(s). Unfortunately under these
conditions the backend does make use of the very index that Access is trying to
take advantage of.

So relinking the table without Access's recognition of the primary key (unique
index) may help performance. The down side is that you may not modify a table
from Access without a specified primary key.

There is also another factor. Does MySql support outer joins? PostgreSQL does
not at this time. MS Access will hide this fact from the users and perform the
join within Access. Thus, creating the situation described above.

> I configured ODBC drive to write the log file to sees what ODBC is doing
> but seems that it writes log file only while fetching rows.
> Is there a way to know what ODBC is doing. To know why it takes so long time?
> Thanks, Jose'

The CommLog was created to log SQL statement communication with the server. A
much more detailed log can be activated from the "ODBC Data Source Administrator"
dialog under the "Tracing" tab. If you use this feature you may want to clear it
out first. It will also bring processing to a craw.