K
Klaus Guntermann
I am still struggling with a strange effect on an initially rather
complicated query.
Please take my apologies that this is a rather long posting again.
Nevertheless I hope that someone with knowledge about internals of Access
will take the time to read further.
I have managed to break down the query to a smaller one and to reduce also
the number of tables and fields involved. Also the problem now shows up when
I use the same (remote) database and not only when I switch from Informix to
PostgreSQL as the SQL server.
My query is as follows:
SELECT lab.labnr, lab_2.labnr, labzuord_1.labnrzu, lab_1.labnr,
labzuord_2.labnr 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
WHERE (((lab.mtknr)=999999999) AND ((labzuord_2.labnrzu) Is Null));
But this is not what Access sends to the database when I execute this
make-table query.
In each experiment only two tables from the database are used. I can
understand, that the resulting commands would differ, when I link to
different tables. But I cannot understand, that the commands for accessing
the same table in one query differ in one version and not in another.
Let me first explain some details about the tables.
The table, which is referred to as gunterma.lz1 is a table which has just
the fields
labnr, labnrzu and artzuordnung. The fields labnr and artzuordnung are used
for a unique index. This table is used in both cases.
The second table is linked differently in the two experiments. In the first
experiment the table owsospos.lab has a lot of fields, but only two are used,
namely mtknr and labnr. Among other indices the field labnr has a unique
index and the field mtknr has an index which allows duplicates.
This shows me the following the sequence of select statements in the ODBC
trace for my query:
SELECT labnr ,labnrzu FROM gunterma.lz1 labzuord_1 \ 0
SELECT labnr ,labnrzu FROM gunterma.lz1 \ 0
SELECT owsospos.lab.labnr ,labzuord_2.labnr ,labzuord_2.labnrzu
FROM {oj owsospos.lab LEFT OUTER JOIN gunterma.lz1 labzuord_2 ON
(owsospos.lab.labnr = labzuord_2.labnr ) } WHERE (owsospos.lab.mtknr
= 999999999 ) \ 0
SELECT labnr FROM owsospos.lab lab_2 WHERE (labnr = ?)\ 0
SELECT labnr FROM owsospos.lab lab_1 WHERE (labnr = ?)\ 0
For the second experiment I renamed two tables in Access such that now
another remote table is used. The table gunterma.lab1 has just two fields:
labnr and mtknr. The index on labnr is unique, the one on mtknr not.
SELECT labnr ,labnrzu FROM gunterma.lz1 labzuord_1 \ 0
SELECT labnr ,labnrzu FROM gunterma.lz1 \ 0
SELECT labnr FROM gunterma.lab1 lab_2 \ 0
SELECT gunterma.lab1.labnr ,labzuord_2.labnr ,labzuord_2.labnrzu
FROM {oj gunterma.lab1 LEFT OUTER JOIN gunterma.lz1 labzuord_2 ON
(gunterma.lab1.labnr = labzuord_2.labnr ) } WHERE
(gunterma.lab1.mtknr = 999999999 ) \ 0
SELECT labnr FROM gunterma.lab1 lab_1 WHERE (labnr = ?)\ 0
While the number of columns of the table gunterma.lab1 is different, the
number of rows is the same in the two experiments. But in contrast to the
experiment in our real application the table used for lab, lab_1 and lab_2
currently has more than 300000 rows and given that there are a lot of fields
it makes a huge performance difference, whether lab_2 is read without the
where condition or with it.
I would like to know, why Access thinks it must split the query and why it
processes the selection for lab_2 different in the two setups.
If you need further information, please let me know. BTW, I use Access 2002
SP3 with an Informix database server via ODBC driver version 3.34.0000
2.70.TC3 on Windows XP Pro. That the Informix server is on Windows 2000
Server should not be important.
Thank you very much for your attention and I hope someone can explain all
this.
Klaus
complicated query.
Please take my apologies that this is a rather long posting again.
Nevertheless I hope that someone with knowledge about internals of Access
will take the time to read further.
I have managed to break down the query to a smaller one and to reduce also
the number of tables and fields involved. Also the problem now shows up when
I use the same (remote) database and not only when I switch from Informix to
PostgreSQL as the SQL server.
My query is as follows:
SELECT lab.labnr, lab_2.labnr, labzuord_1.labnrzu, lab_1.labnr,
labzuord_2.labnr 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
WHERE (((lab.mtknr)=999999999) AND ((labzuord_2.labnrzu) Is Null));
But this is not what Access sends to the database when I execute this
make-table query.
In each experiment only two tables from the database are used. I can
understand, that the resulting commands would differ, when I link to
different tables. But I cannot understand, that the commands for accessing
the same table in one query differ in one version and not in another.
Let me first explain some details about the tables.
The table, which is referred to as gunterma.lz1 is a table which has just
the fields
labnr, labnrzu and artzuordnung. The fields labnr and artzuordnung are used
for a unique index. This table is used in both cases.
The second table is linked differently in the two experiments. In the first
experiment the table owsospos.lab has a lot of fields, but only two are used,
namely mtknr and labnr. Among other indices the field labnr has a unique
index and the field mtknr has an index which allows duplicates.
This shows me the following the sequence of select statements in the ODBC
trace for my query:
SELECT labnr ,labnrzu FROM gunterma.lz1 labzuord_1 \ 0
SELECT labnr ,labnrzu FROM gunterma.lz1 \ 0
SELECT owsospos.lab.labnr ,labzuord_2.labnr ,labzuord_2.labnrzu
FROM {oj owsospos.lab LEFT OUTER JOIN gunterma.lz1 labzuord_2 ON
(owsospos.lab.labnr = labzuord_2.labnr ) } WHERE (owsospos.lab.mtknr
= 999999999 ) \ 0
SELECT labnr FROM owsospos.lab lab_2 WHERE (labnr = ?)\ 0
SELECT labnr FROM owsospos.lab lab_1 WHERE (labnr = ?)\ 0
For the second experiment I renamed two tables in Access such that now
another remote table is used. The table gunterma.lab1 has just two fields:
labnr and mtknr. The index on labnr is unique, the one on mtknr not.
SELECT labnr ,labnrzu FROM gunterma.lz1 labzuord_1 \ 0
SELECT labnr ,labnrzu FROM gunterma.lz1 \ 0
SELECT labnr FROM gunterma.lab1 lab_2 \ 0
SELECT gunterma.lab1.labnr ,labzuord_2.labnr ,labzuord_2.labnrzu
FROM {oj gunterma.lab1 LEFT OUTER JOIN gunterma.lz1 labzuord_2 ON
(gunterma.lab1.labnr = labzuord_2.labnr ) } WHERE
(gunterma.lab1.mtknr = 999999999 ) \ 0
SELECT labnr FROM gunterma.lab1 lab_1 WHERE (labnr = ?)\ 0
While the number of columns of the table gunterma.lab1 is different, the
number of rows is the same in the two experiments. But in contrast to the
experiment in our real application the table used for lab, lab_1 and lab_2
currently has more than 300000 rows and given that there are a lot of fields
it makes a huge performance difference, whether lab_2 is read without the
where condition or with it.
I would like to know, why Access thinks it must split the query and why it
processes the selection for lab_2 different in the two setups.
If you need further information, please let me know. BTW, I use Access 2002
SP3 with an Informix database server via ODBC driver version 3.34.0000
2.70.TC3 on Windows XP Pro. That the Informix server is on Windows 2000
Server should not be important.
Thank you very much for your attention and I hope someone can explain all
this.
Klaus