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

We are working on a project that IMHO give more prestige to
PostgreSQL.
The Hygea project concern the use of an Unix-like Operating sys-
tem as "back-end" of a Client M$-windows application connected
by ODBC that will be installed in about 80 Italian Helth Depart-
ments for the veterinary controls and prevention.
Therefore...

O.S.: We choose Linux for his proved reliability.

Client: We choose to develop the Client with M$-Access because we
need (unfortunately) a complete integration with Micro$oft World.

Database: We choose PostgreSQL for his reliability and for his
compatibility with SQL/92 standard recommendation and for his ex-
cellent technical support provided by "The PostgreSQL Development
Team" and his mailing lists.

Nevertheless the union among M$-Access and PostgreSQL is quite
suffered for the following reasons:

1. The PostgreSQL doesn't use the index with "OR" operator and
so is not possible to define a multiple key to use with M$-Access
and we need to retreat using OID as primary keys (thanks to Byron
Nikolaidis and David Hartwig of insightdist.com that are doing a
really great job with ODBC driver), but with the obvious consequences.

2. As PostgreSQL doesn't allow an "ORDER BY" on columns not
included in the target list of the "SELECT", (I know that it is
SQL/92 standard, but IMO it's a fool thing), therefore, is not possible
to have the "dynaset "sorted for any field that is different from
the key (in our case the useless OIDs).

3. The times required to run complex reports (for example those that
include LEFT JOINS) is very long (about 15 minutes to retrieve
2850 rows).

We hope the PostgreSQL next release v6.4 may have some of these features
otherwise, we have to give up the project.

> 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.

| |
~~~~~~~~~~~~~~~~~~~~~~~~ | | ~~~~~~~~~~~~~~~~~~~~~~~~
Progetto HYGEA ---- ---- www.sferacarta.com
Sfera Carta Software ---- ---- sferac(at)bo(dot)nettuno(dot)it
Via Bazzanese, 69 | | Fax. ++39 51 6131537
Casalecchio R.(BO) Italy | | Tel. ++39 51 591054
-----------------------------------------------------------------------------