Skip site navigation (1) Skip section navigation (2)

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

From: "Jose' Soares Da Silva" <sferac(at)bo(dot)nettuno(dot)it>
To: David Hartwig <daveh(at)insightdist(dot)com>
Cc: PostgreSQL Interfaces <pgsql-interfaces(at)postgreSQL(dot)org>, hackers postgres <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [INTERFACES] ODBC is slow with M$-Access Report
Date: 1998-06-01 17:42:26
Message-ID: Pine.LNX.3.96.980601173507.10343A-100000@proxy.bazzanese.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces
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
-----------------------------------------------------------------------------


In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1998-06-01 19:36:56
Subject: Re: [HACKERS] cvs snapshot comm changes
Previous:From: Brett McCormickDate: 1998-06-01 16:08:09
Subject: some more rambling on the new fe/be communication

pgsql-interfaces by date

Next:From: Thomas LesterDate: 1998-06-01 18:12:23
Subject: Re: [ADMIN] ODBC Call fail? (fwd)
Previous:From: Peter MountDate: 1998-06-01 17:36:01
Subject: Re: [ADMIN] ODBC Call fail? (fwd)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group