Re: [INTERFACES] MS Access 97 and Pass-through SQL - Mailing list pgsql-interfaces

Nice query.   :)       PostgreSQL supports inner joins logicly, but not
using this INNER JOIN syntax.   Perhaps in 6.5.   The Jet, as you probably
know, rewrites this query w/o the INNER JOIN stuff.\

Here is something you can try to speed up your query.   I don't have M$
Access handy to recall the exact place this is set.   But, there is a place
where you can set the property of a query to be a "snapshot" instead of
"dynaset".   ( I think a right button click above the query definition.)

If you are not aware of what is happening with a dynaset vs a snapshot, I
strongly recommend reading up on them.

Also, I suggest you  turn on the CommLog and keep track of the actual
queries that are being generated.  The difference between a dynaset and a
snapshot may interest you.    It may also help you in determiming where
bottlenecks exist - by cut/pasting the generated queries directly into the
psql monitor.





----- Original Message -----
From: Gregg Berkholtz <gregg@gbcomputers.com>
To: David Hartwig <daybee@bellatlantic.net>
Cc: Access Developer listserv <accessd@mtgroup.com>; Postgres interface list
<pgsql-interfaces@postgreSQL.org>
Sent: Saturday, January 23, 1999 6:59 PM
Subject: Re: [INTERFACES] MS Access 97 and Pass-through SQL


>I was aware of the jet engine, but it is so _SLOW_ that I was hoping to
maybe
>find a way to speed it up. The only thing that I could think of was
>pass-through... Which, of course no longer allowed me to do most joins. :-(
>
>I am just beginning to learn SQL and have been given this project to work
on,
>not knowing that much about SQL has forced me to build the queries in the
>builder and then convert them to an SQL pass-through.
>
>Does anyone know of an equivalent SQL statement/syntax that I could use
instead
>of INNER JOINS with PostgreSQL.
>
>This is the statement as it comes out of Access (what it is supposed to do
is
>query the database getting the relevant information about all open Trouble
>Tickets from multiple fields):
>
>SELECT tblTroubTick.troubTickID, tblTroubTick.troubTickNum,
>[tblClientInfo]![firstName] & " " & [tblClientInfo]![lastName] AS
>concatTblClientTbl, tblTroubTick.troubTickOpenDate,
>tblTroubTickProblem.troubTickProblem, tblPriority.priority,
>tblTroubTick.hoursInvested, [tblSupportPersonelInfo]![firstName] & " " &
>[tblSupportPersonelInfo]![lastName] AS concatTblSupportPersonell,
>tblTroubTick.troubTickLastUpdate, tblTroubTick.assetID,
>tblTroubTickStatus.troubTickStatus
>FROM tblSupportPersonelInfo INNER JOIN (tblPriority INNER JOIN
>(tblTroubTickProblem INNER JOIN (tblClientLocation INNER JOIN
(tblClientInfo
>INNER JOIN (tblTroubTickStatus INNER JOIN tblTroubTick ON
>tblTroubTickStatus.troubTickStatusID = tblTroubTick.troubTickStatusID) ON
>tblClientInfo.clientID = tblTroubTick.clientID) ON
tblClientLocation.locationID
>= tblClientInfo.locationID) ON tblTroubTickProblem.troubTickProblemID =
>tblTroubTick.troubTickProblemID) ON tblPriority.priorityID =
>tblTroubTick.priorityID) ON tblSupportPersonelInfo.supportPersID =
>tblTroubTick.supportPersID
>WHERE (((tblTroubTickStatus.troubTickStatus) Not In ("CLOSED")));
>
>
>Thanks for the help!
>
>Gregg Berkholtz
>
>David Hartwig wrote:
>
>> Any legal PostgreSQL statement(s) should work.  However, I don't think
>> multiple resultset are allowed.  The big thing is that you are avoiding
the
>> MS Jet Engine.  This will allow you access any non-standard functions and
>> syntax available to PostgreSQL.
>>
>> If you don't know, Jet has many benifits.  Like performing outer joins
and
>> cross-tabulation.
>>
>> ----- Original Message -----
>> From: Gregg Berkholtz <gregg@gbcomputers.com>
>> To: Access Developer listserv <accessd@mtgroup.com>; Postgres interface
list
>> <pgsql-interfaces@postgreSQL.org>
>> Sent: Saturday, January 23, 1999 3:08 PM
>> Subject: [INTERFACES] MS Access 97 and Pass-through SQL
>>
>> >Does anyone know what pass-through SQL statements cannot be used with
>> >PostgreSQL connections via ODBC.
>> >I am running PostgreSQL 6.4.2.
>> >
>> >Thanks greatly!
>> >
>> >Gregg Berkholtz
>> >
>> >
>