Jose' Soares Da Silva wrote:
> We are working on a project that IMHO give more prestige to
> 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.
> 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.
I am currently working on a solution as time will allow. Hopefully part of 6.4
> 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).
This fix is in alpha and will be in the 6.4 release. I do not know when 6.4 is slated
for release, but I am willing to send you a patch if it is critical for you to proceed.
> 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).
The solution to your first item will resolve this also.
> 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.
In response to
pgsql-hackers by date
|Next:||From: Bruce Momjian||Date: 1998-06-02 16:21:48|
|Subject: Re: [HACKERS] Re: [INTERFACES] ODBC is slow with M$-Access Report|
|Previous:||From: Thomas G. Lockhart||Date: 1998-06-02 14:12:43|
|Subject: Re: [HACKERS] An easier way to upgrade (Was: Lots 'o patches)|
pgsql-interfaces by date
|Next:||From: Byron Nikolaidis||Date: 1998-06-02 16:11:44|
|Subject: Where is Interfaces?|
|Previous:||From: Krasnow, Greg||Date: 1998-06-01 21:38:56|
|Subject: RE: [INTERFACES] whoops: VB RDO works just fine with new odbc dri ver....|