K
Klaus Guntermann
I have a problem, which seems rather strange to me.
We need to run some queries on Access, while the database is on a remote
server. Now we are planning to move from a remote Informix database to a
remore PostgreSQL database.
Running the same query on both database one can expect different timing
results.
But looking deeper into what happens, I learned, that Access changes the
query from what was shown in the SQL command screen. (I am sorry for not
picking the proper names here, but I run a localized German Access version;
there it is named SQL-Ansicht). And these modifications are not the same for
the two databases. Namely, while there are restricting where clauses in the
Informix database queries, the queries to the PostgreSQL database do not
contain conditions, such that large tables are completely transferred to
Access and processed locally. I would to get rid of this different behaviour.
Because the result is processed further in an Access application I cannot
switch to a pass through query, which would speed up everything by
magnitudes. I need to run a "make table query" (if that is the proper term
here: "Tabellenerstellungsabfrage" in German).
Is there a chance to persuade Access to not split the query?
Or can someone help to redesign the query for better processing with Access?
For more information I add the query below. I know, it is huge, but the
remote databases can handle that without pain. All the tables (with the
exception of the result table t_t are remote). The numerical value 999999999
is normaly added/modified for personalizing the query.
SELECT lab.mtknr, stg.stg, stg.abschl, sos.nachname, sos.vorname,
sos.gebdat, sos.gebort, sos.geschl, lab.labnr, lab.pnr, lab.ppruef1,
lab.psem, lab.panerk, lab.pdatum, lab.pnote, lab.pstatus, lab.bonus,
labzuord_1.labnr, labzuord_1.labnrzu, labzuord_1.artzuordnung,
labzuord.labnr, labzuord.artzuordnung, labzuord.pordnrzu, labzuord_2.labnr,
labzuord_2.artzuordnung, 1 AS Sort1, pzutxt.pzutxt, pzutxt.art, lab.pvermerk,
lab.part, lab.pform, lab.res1, lab_2.pnr AS Kontopnr, lab_2.pversuch AS
KontoVersuch, lab_2.pstatus AS KontoPstatus, lab_2.pnote AS KontoPnote,
lab_2.pdatum AS KontoPdatum, lab_2.pform AS Kontopform, lab_2.psem AS
KontoPsem, lab_2.ppruef1 AS KontoPpruef1, lab_2.part AS KontoPart,
lab_2.bonus AS KontoBonus, lab_2.pvermerk AS KontoPvermerk, lab_2.res1 AS
Kontores1, lab_2.pordnr AS KontoPordnr, lab_2.ppflicht AS KontoPflicht,
lab_2.panerk AS KontoPanerk, lab_1.pnr AS LVpnr, lab_1.pversuch AS
LVpversuch, lab_1.pstatus AS LVpstatus, lab_1.pnote AS LVpnote, lab_1.pdatum
AS LVpdatum, lab_1.pform AS LVpform, lab_1.psem AS LVpsem, lab_1.ppruef1 AS
LVppruef1, lab_1.pvermerk AS LVpvermerk, lab_1.part AS LVpart, lab_1.bonus AS
LVbonus, lab_1.res1 AS LVres1, lab_1.pordnr AS LVpordnr, lab_1.ppflicht AS
LVPflicht, lab_1.panerk AS LVPanerk, pord.pdtxt, pord.pltxt1 INTO t_t
FROM ((((((((lab LEFT JOIN labzuord AS labzuord_1 ON lab.labnr =
labzuord_1.labnrzu) LEFT JOIN labzuord ON labzuord_1.labnr =
labzuord.labnrzu) LEFT JOIN labzuord AS labzuord_2 ON lab.labnr =
labzuord_2.labnr) LEFT JOIN lab AS lab_2 ON labzuord_1.labnr = lab_2.labnr)
LEFT JOIN lab AS lab_1 ON labzuord.labnr = lab_1.labnr) LEFT JOIN pzutxt ON
lab.labnr = pzutxt.labnr) INNER JOIN sos ON lab.mtknr = sos.mtknr) INNER
JOIN stg ON (sos.semester = stg.semester) AND (sos.mtknr = stg.mtknr)) INNER
JOIN pord ON lab.pordnr = pord.pordnr
WHERE ((lab.mtknr=999999999) AND ((IsNull(labzuord_2.labnrzu) And
IsNull(labzuord.labnr)) Or (IsNull(labzuord_2.labnrzu) And Not
IsNull(labzuord.labnr))));
Using Informix the selections from lab_1 and lab_2 are made after selecting
from lab with the condition "where labnr=?".
With PostgreSQL the selects from lab_1 and lab_2 are made just after
fetching labzuord and labzuord_1 without any conditions resulting in a
transfer of more than 30 MB for each.
Is there any switch or option to make Access use the where clause also with
PostgreSQL?
If there are further questions to clarify the problem, I would happily try
to answer these.
Thank you for your attention and in advance for any suggestions.
Klaus
We need to run some queries on Access, while the database is on a remote
server. Now we are planning to move from a remote Informix database to a
remore PostgreSQL database.
Running the same query on both database one can expect different timing
results.
But looking deeper into what happens, I learned, that Access changes the
query from what was shown in the SQL command screen. (I am sorry for not
picking the proper names here, but I run a localized German Access version;
there it is named SQL-Ansicht). And these modifications are not the same for
the two databases. Namely, while there are restricting where clauses in the
Informix database queries, the queries to the PostgreSQL database do not
contain conditions, such that large tables are completely transferred to
Access and processed locally. I would to get rid of this different behaviour.
Because the result is processed further in an Access application I cannot
switch to a pass through query, which would speed up everything by
magnitudes. I need to run a "make table query" (if that is the proper term
here: "Tabellenerstellungsabfrage" in German).
Is there a chance to persuade Access to not split the query?
Or can someone help to redesign the query for better processing with Access?
For more information I add the query below. I know, it is huge, but the
remote databases can handle that without pain. All the tables (with the
exception of the result table t_t are remote). The numerical value 999999999
is normaly added/modified for personalizing the query.
SELECT lab.mtknr, stg.stg, stg.abschl, sos.nachname, sos.vorname,
sos.gebdat, sos.gebort, sos.geschl, lab.labnr, lab.pnr, lab.ppruef1,
lab.psem, lab.panerk, lab.pdatum, lab.pnote, lab.pstatus, lab.bonus,
labzuord_1.labnr, labzuord_1.labnrzu, labzuord_1.artzuordnung,
labzuord.labnr, labzuord.artzuordnung, labzuord.pordnrzu, labzuord_2.labnr,
labzuord_2.artzuordnung, 1 AS Sort1, pzutxt.pzutxt, pzutxt.art, lab.pvermerk,
lab.part, lab.pform, lab.res1, lab_2.pnr AS Kontopnr, lab_2.pversuch AS
KontoVersuch, lab_2.pstatus AS KontoPstatus, lab_2.pnote AS KontoPnote,
lab_2.pdatum AS KontoPdatum, lab_2.pform AS Kontopform, lab_2.psem AS
KontoPsem, lab_2.ppruef1 AS KontoPpruef1, lab_2.part AS KontoPart,
lab_2.bonus AS KontoBonus, lab_2.pvermerk AS KontoPvermerk, lab_2.res1 AS
Kontores1, lab_2.pordnr AS KontoPordnr, lab_2.ppflicht AS KontoPflicht,
lab_2.panerk AS KontoPanerk, lab_1.pnr AS LVpnr, lab_1.pversuch AS
LVpversuch, lab_1.pstatus AS LVpstatus, lab_1.pnote AS LVpnote, lab_1.pdatum
AS LVpdatum, lab_1.pform AS LVpform, lab_1.psem AS LVpsem, lab_1.ppruef1 AS
LVppruef1, lab_1.pvermerk AS LVpvermerk, lab_1.part AS LVpart, lab_1.bonus AS
LVbonus, lab_1.res1 AS LVres1, lab_1.pordnr AS LVpordnr, lab_1.ppflicht AS
LVPflicht, lab_1.panerk AS LVPanerk, pord.pdtxt, pord.pltxt1 INTO t_t
FROM ((((((((lab LEFT JOIN labzuord AS labzuord_1 ON lab.labnr =
labzuord_1.labnrzu) LEFT JOIN labzuord ON labzuord_1.labnr =
labzuord.labnrzu) LEFT JOIN labzuord AS labzuord_2 ON lab.labnr =
labzuord_2.labnr) LEFT JOIN lab AS lab_2 ON labzuord_1.labnr = lab_2.labnr)
LEFT JOIN lab AS lab_1 ON labzuord.labnr = lab_1.labnr) LEFT JOIN pzutxt ON
lab.labnr = pzutxt.labnr) INNER JOIN sos ON lab.mtknr = sos.mtknr) INNER
JOIN stg ON (sos.semester = stg.semester) AND (sos.mtknr = stg.mtknr)) INNER
JOIN pord ON lab.pordnr = pord.pordnr
WHERE ((lab.mtknr=999999999) AND ((IsNull(labzuord_2.labnrzu) And
IsNull(labzuord.labnr)) Or (IsNull(labzuord_2.labnrzu) And Not
IsNull(labzuord.labnr))));
Using Informix the selections from lab_1 and lab_2 are made after selecting
from lab with the condition "where labnr=?".
With PostgreSQL the selects from lab_1 and lab_2 are made just after
fetching labzuord and labzuord_1 without any conditions resulting in a
transfer of more than 30 MB for each.
Is there any switch or option to make Access use the where clause also with
PostgreSQL?
If there are further questions to clarify the problem, I would happily try
to answer these.
Thank you for your attention and in advance for any suggestions.
Klaus