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

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

From: David Hartwig <daveh(at)insightdist(dot)com>
To: "Jose' Soares Da Silva" <sferac(at)bo(dot)nettuno(dot)it>
Cc: PostgreSQL Interfaces <pgsql-interfaces(at)postgreSQL(dot)org>
Subject: Re: [INTERFACES] ODBC is slow with M$-Access Report
Date: 1998-05-28 14:06:43
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-interfaces

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: Jackson, DeJuanDate: 1998-05-28 21:20:58
Subject: RE: [GENERAL] Re: [HACKERS] Off-topic: Communication. (was: Connect string again)
Previous:From: Jose' Soares Da SilvaDate: 1998-05-28 10:50:19
Subject: ODBC is slow with M$-Access Report

pgsql-interfaces by date

Next:From: David HartwigDate: 1998-05-28 14:27:39
Subject: Re: [ADMIN] pgsql-interfaces ODBC???
Previous:From: Jose' Soares Da SilvaDate: 1998-05-28 10:50:19
Subject: ODBC is slow with M$-Access Report

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