By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

Slow odbc oracle query using oracle odbc

P: n/a
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I've discovered
that the tables were connected using odbc shipped with version 7.3 of
oracle. the only apparent change is that the connection string visible
in access passing the mouse on the query is changed between something
like "ODBC;DSN=dnsname;DBQ=OdbcName;ASY=OFF;;TABLE=Tabl eName" to
something like
"ODBC;DSN=dnsname;UID=username;DBQ=OdbcName ;DBA=W;APA=F;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=F ;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS =T;MDI=F;CSR=F;FWC=T;FBS=64000;TLO=O;;TABLE=TableN ame"

The query is:

SELECT QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, Sum(([SHOCQTRC]/100)) AS
QtaOk, QGDLN00.GDLNDESC
FROM TabellaOrdini INNER JOIN (QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM) ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE (((QSHOC00.SHOCDTIV)<DateValue("31/12/4712")) AND
((QSHOC00.SHOCDTFV)=DateValue("31/12/4712")) AND ((QGDLN00.GDLNFINI)="
") AND ((QGDLN00.GDLNLANG)="I"))
GROUP BY QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, QGDLN00.GDLNDESC,
QSHOC00.SHOCFINI
HAVING (((QSHOC00.SHOCITEM)<>" ") AND ((QSHOC00.SHOCFINI)=" "));

The tables are the following:
1)TabellaOrdini: it contains only two records and has "Ordine" as
primary key
2) QSHOC00: it contains about 1870000 records and has a composite
primary key composed by fields SOCIETA, SHOCPORD, SHOCFASE, SHOCITEM,
SHOCPORD, SHOCFINI, SHOCLRIN
3) QGDLN00: : it contains about 75000 records and has a composite
primary key composed by fields SOCIETA, GDLNITEM, GDLNFINI, GDLNLANG

The query takes about 2 minutes to run and before it took only few
seconds. If I change the query putting the two values of "ordini" in
a where clause is again very fast so the problem must be in the join
with TabellaOrdini.
A last thing: I've also tried to use client 10.2.3 but it changes
nothing, unless I reconnect the tables using old odbc and then it runs
all fine.

Sorry for the long explanation and thanks in advance for the answers
given.

Mar 23 '07 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Mar 23, 12:56 am, "funky" <marco.ronc...@gmail.comwrote:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I've discovered
that the tables were connected using odbc shipped with version 7.3 of
oracle. the only apparent change is that the connection string visible
in access passing the mouse on the query is changed between something
like "ODBC;DSN=dnsname;DBQ=OdbcName;ASY=OFF;;TABLE=Tabl eName" to
something like
"ODBC;DSN=dnsname;UID=username;DBQ=OdbcName ;DBA=W;APA=F;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=F ;RST=T;BTD=F;BAM=IfAllSuc*cessful;NUM=NLS;DPM=F;MT S=T;MDI=F;CSR=F;FWC=T;FBS=64000;TLO=O;;TABLE=Table N*ame"

The query is:

SELECT QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, Sum(([SHOCQTRC]/100)) AS
QtaOk, QGDLN00.GDLNDESC
FROM TabellaOrdini INNER JOIN (QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM) ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE (((QSHOC00.SHOCDTIV)<DateValue("31/12/4712")) AND
((QSHOC00.SHOCDTFV)=DateValue("31/12/4712")) AND ((QGDLN00.GDLNFINI)="
") AND ((QGDLN00.GDLNLANG)="I"))
GROUP BY QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, QGDLN00.GDLNDESC,
QSHOC00.SHOCFINI
HAVING (((QSHOC00.SHOCITEM)<>" ") AND ((QSHOC00.SHOCFINI)=" "));

The tables are the following:
1)TabellaOrdini: it contains only two records and has "Ordine" as
primary key
2) QSHOC00: it contains about 1870000 records and has a composite
primary key composed by fields SOCIETA, SHOCPORD, SHOCFASE, SHOCITEM,
SHOCPORD, SHOCFINI, SHOCLRIN
3) QGDLN00: : it contains about 75000 records and has a composite
primary key composed by fields SOCIETA, GDLNITEM, GDLNFINI, GDLNLANG

The query takes about 2 minutes to run and before it took only few
seconds. If I change the query putting the two values of "ordini" in
a where clause is again very fast so the problem must be in the join
with TabellaOrdini.
A last thing: I've also tried to use client 10.2.3 but it changes
nothing, unless I reconnect the tables using old odbc and then it runs
all fine.

Sorry for the long explanation and thanks in advance for the answers
given.
You did not mention whether you had refreshed the links or not.

Mar 23 '07 #2

P: n/a
On Mar 23, 6:21 pm, eng...@ridesoft.com wrote:
On Mar 23, 12:56 am, "funky" <marco.ronc...@gmail.comwrote:


hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I've discovered
that the tables were connected using odbc shipped with version 7.3 of
oracle. the only apparent change is that the connection string visible
in access passing the mouse on the query is changed between something
like "ODBC;DSN=dnsname;DBQ=OdbcName;ASY=OFF;;TABLE=Tabl eName" to
something like
"ODBC;DSN=dnsname;UID=username;DBQ=OdbcName ;DBA=W;APA=F;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=F ;RST=T;BTD=F;BAM=IfAllSuc**cessful;NUM=NLS;DPM=F;M TS=T;MDI=F;CSR=F;FWC=T;FBS=64000;TLO=O;;TABLE=Tabl e*N*ame"
The query is:
SELECT QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, Sum(([SHOCQTRC]/100)) AS
QtaOk, QGDLN00.GDLNDESC
FROM TabellaOrdini INNER JOIN (QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM) ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE (((QSHOC00.SHOCDTIV)<DateValue("31/12/4712")) AND
((QSHOC00.SHOCDTFV)=DateValue("31/12/4712")) AND ((QGDLN00.GDLNFINI)="
") AND ((QGDLN00.GDLNLANG)="I"))
GROUP BY QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, QGDLN00.GDLNDESC,
QSHOC00.SHOCFINI
HAVING (((QSHOC00.SHOCITEM)<>" ") AND ((QSHOC00.SHOCFINI)=" "));
The tables are the following:
1)TabellaOrdini: it contains only two records and has "Ordine" as
primary key
2) QSHOC00: it contains about 1870000 records and has a composite
primary key composed by fields SOCIETA, SHOCPORD, SHOCFASE, SHOCITEM,
SHOCPORD, SHOCFINI, SHOCLRIN
3) QGDLN00: : it contains about 75000 records and has a composite
primary key composed by fields SOCIETA, GDLNITEM, GDLNFINI, GDLNLANG
The query takes about 2 minutes to run and before it took only few
seconds. If I change the query putting the two values of "ordini" in
a where clause is again very fast so the problem must be in the join
with TabellaOrdini.
A last thing: I've also tried to use client 10.2.3 but it changes
nothing, unless I reconnect the tables using old odbc and then it runs
all fine.
Sorry for the long explanation and thanks in advance for the answers
given.

You did not mention whether you had refreshed the links or not.- Hide quoted text -

- Show quoted text -
Yes, sorry. I've refreshed the links and reconnected all the tables
using save password option

Mar 25 '07 #3

P: n/a
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
same odbc but against tables connected with the old "connection
string" and the new one. Each of the two main tables (QSHOC00 and
QGDLN00) has two index, for some unknown reason the odbc seems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan

--- Qtaord_somma_codici_impiegati ---

- Inputs to Query -
ODBC table 'QSHOC00'
Using index 'QSHOCIX1'
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
Table 'Tabella_ordini'
- End inputs to Query -

453469464) Remote SQL
01) Inner Join result of '00)' to table 'QSHOC00'
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of '01)' to table 'Tabella_ordini'
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of '02)'

Anyone knows why acces might get the wrong index. A thing i've
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the system tables ( like MSysobjects ) . i've
checked and , as a matter of fact, the tables connected using the new
odbc have a null field named Lv on MSysObjects, while connected with
the old one there were datas in it.
i don't know how to get out of this mess so any help would be greatly
appreciate.
- Inputs to Query -
Table 'Tabella_ordini'
ODBC table 'QSHOC00'
Using index 'QSHOCIX'
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
- End inputs to Query -

01) Inner Join table 'Tabella_ordini' to table 'QSHOC00'
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of '01)'
03) Inner Join result of '01)' to result of '02)'
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of '03)'
05) Sort result of '04)'

Mar 28 '07 #4

P: n/a
On 28 Mar, 13:57, "funky" <marco.ronc...@gmail.comwrote:
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
same odbc but againsttablesconnectedwith the old "connection
string" and the new one. Each of the two maintables(QSHOC00 and
QGDLN00) has two index, for some unknown reason the odbc seems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan

--- Qtaord_somma_codici_impiegati ---

- Inputs to Query -
ODBC table 'QSHOC00'
Using index 'QSHOCIX1'
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
Table 'Tabella_ordini'
- End inputs to Query -

453469464) Remote SQL
01) Inner Join result of '00)' to table 'QSHOC00'
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of '01)' to table 'Tabella_ordini'
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of '02)'

Anyone knows why acces might get the wrong index. A thing i've
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the systemtables( likeMSysobjects) . i've
checked and , as a matter of fact, thetablesconnectedusing the new
odbc have a null field namedLvonMSysObjects, whileconnectedwith
the old one there were datas in it.
i don't know how to get out of this mess so any help would be greatly
appreciate.

- Inputs to Query -
Table 'Tabella_ordini'
ODBC table 'QSHOC00'
Using index 'QSHOCIX'
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
- End inputs to Query -

01) Inner Join table 'Tabella_ordini' to table 'QSHOC00'
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of '01)'
03) Inner Join result of '01)' to result of '02)'
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of '03)'
05) Sort result of '04)'
Up, anybody can help, i'm realy stuck with this........

Apr 3 '07 #5

P: n/a
On 28 Mar, 13:57, "funky" <marco.ronc...@gmail.comwrote:
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
same odbc but againsttablesconnectedwith the old "connection
string" and the new one. Each of the two maintables(QSHOC00 and
QGDLN00) has two index, for some unknown reason the odbc seems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan

--- Qtaord_somma_codici_impiegati ---

- Inputs to Query -
ODBC table 'QSHOC00'
Using index 'QSHOCIX1'
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
Table 'Tabella_ordini'
- End inputs to Query -

453469464) Remote SQL
01) Inner Join result of '00)' to table 'QSHOC00'
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of '01)' to table 'Tabella_ordini'
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of '02)'

Anyone knows why acces might get the wrong index. A thing i've
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the systemtables( likeMSysobjects) . i've
checked and , as a matter of fact, thetablesconnectedusing the new
odbc have a null field namedLvonMSysObjects, whileconnectedwith
the old one there were datas in it.
i don't know how to get out of this mess so any help would be greatly
appreciate.

- Inputs to Query -
Table 'Tabella_ordini'
ODBC table 'QSHOC00'
Using index 'QSHOCIX'
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
- End inputs to Query -

01) Inner Join table 'Tabella_ordini' to table 'QSHOC00'
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of '01)'
03) Inner Join result of '01)' to result of '02)'
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of '03)'
05) Sort result of '04)'
hi, i up this post to see if anyione can help me. Thanks in any case

Apr 3 '07 #6

P: n/a
On Apr 3, 5:48 am, "funky" <marco.ronc...@gmail.comwrote:
On 28 Mar, 13:57, "funky" <marco.ronc...@gmail.comwrote:
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
same odbc but againsttablesconnectedwith the old "connection
string" and the new one. Each of the two maintables(QSHOC00 and
QGDLN00) has two index, for some unknown reason the odbc seems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan
--- Qtaord_somma_codici_impiegati ---
- Inputs to Query -
ODBC table 'QSHOC00'
Using index 'QSHOCIX1'
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
Table 'Tabella_ordini'
- End inputs to Query -
453469464) Remote SQL
01) Inner Join result of '00)' to table 'QSHOC00'
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of '01)' to table 'Tabella_ordini'
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of '02)'
Anyone knows why acces might get the wrong index. A thing i've
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the systemtables( likeMSysobjects) . i've
checked and , as a matter of fact, thetablesconnectedusing the new
odbc have a null field namedLvonMSysObjects, whileconnectedwith
the old one there were datas in it.
i don't know how to get out of this mess so any help would be greatly
appreciate.
- Inputs to Query -
Table 'Tabella_ordini'
ODBC table 'QSHOC00'
Using index 'QSHOCIX'
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBC table 'QGDLN00'
- End inputs to Query -
01) Inner Join table 'Tabella_ordini' to table 'QSHOC00'
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of '01)'
03) Inner Join result of '01)' to result of '02)'
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of '03)'
05) Sort result of '04)'

Up, anybody can help, i'm realy stuck with this........
Your original post says
>If I change the query putting the two values of "ordini" in a where
clause is again very fast so the problem must be in the join with
TabellaOrdini.
suggesting that it might be better to replace this construct

SELECT ...
FROM Table1 INNER JOIN (Table2 INNER JOIN Table3 ON ...) ON ...)

with something in this form

SELECT ...
FROM Table1, Table2, Table3
WHERE Table1.foo=Table2.bar AND Table2.bar=Table3.baz

Try that. If it doesn't help, consider using a pass-through query
instead. See the Access help for details.

Apr 3 '07 #7

P: n/a
On Apr 3, 4:45 pm, "Gord" <g...@kingston.netwrote:
On Apr 3, 5:48 am, "funky" <marco.ronc...@gmail.comwrote:


On 28 Mar, 13:57, "funky" <marco.ronc...@gmail.comwrote:
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
sameodbcbut againsttablesconnectedwith the old "connection
string" and the new one. Each of the two maintables(QSHOC00 and
QGDLN00) has two index, for some unknown reason theodbcseems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan
--- Qtaord_somma_codici_impiegati ---
- Inputs to Query -
>ODBCtable 'QSHOC00'
Using index 'QSHOCIX1'
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
>ODBCtable 'QGDLN00'
Table 'Tabella_ordini'
- End inputs to Query -
453469464) Remote SQL
01) Inner Join result of '00)' to table 'QSHOC00'
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of '01)' to table 'Tabella_ordini'
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of '02)'
Anyone knows why acces might get the wrong index. A thing i've
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the systemtables( likeMSysobjects) . i've
checked and , as a matter of fact, thetablesconnectedusing the new
>odbchave a null field namedLvonMSysObjects, whileconnectedwith
the old one there were datas in it.
i don't know how to get out of this mess so any help would be greatly
appreciate.
- Inputs to Query -
Table 'Tabella_ordini'
>ODBCtable 'QSHOC00'
Using index 'QSHOCIX'
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
>ODBCtable 'QGDLN00'
- End inputs to Query -
01) Inner Join table 'Tabella_ordini' to table 'QSHOC00'
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of '01)'
03) Inner Join result of '01)' to result of '02)'
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of '03)'
05) Sort result of '04)'
Up, anybody can help, i'm realy stuck with this........

Your original post says
If I change the query putting the two values of "ordini" in a where
clause is again very fast so the problem must be in the join with
TabellaOrdini.

suggesting that it might be better to replace this construct

SELECT ...
FROM Table1 INNER JOIN (Table2 INNER JOIN Table3 ON ...) ON ...)

with something in this form

SELECT ...
FROM Table1, Table2, Table3
WHERE Table1.foo=Table2.bar AND Table2.bar=Table3.baz

Try that. If it doesn't help, consider using a pass-through query
instead. See the Access help for details.- Hide quoted text -

- Show quoted text -
i've already tried this, and it doesn't work. The problem seems to be
in the way access uses the index on the table QSHOC: with the old odbc
driver by oracle only the primary index is seen as:

"QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls "

While linking the table with the new 10.1-10.2 odbc access sees two
index:

"QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls "

the strangest thing is in the different number of values. Another
strange thing is that the unique index is QSHOCIX but access seems
unable to use it, i've tried to drop QSHOCIX1 and also to define a
psuedo index but access choses anyway to use a full table scan
instead of using it. there is something, in the new way in wich the
table is linked, that seems to hint the query optimizer to not use the
primary index in any case.
In my company the policiy is to use odbc and linked tables for all the
access databases that users have. we need to mantain a certain
omogeneity and so we wouldn't use pass through query unless forced
to.
I fear this might be the case, because this query used to run in few
seconds an now it takes 5 minutes, and, like this, many other query on
the same table.

Apr 4 '07 #8

P: n/a
On Apr 4, 8:05 am, "funky" <marco.ronc...@gmail.comwrote:
On Apr 3, 4:45 pm, "Gord" <g...@kingston.netwrote:
On Apr 3, 5:48 am, "funky" <marco.ronc...@gmail.comwrote:
On 28 Mar, 13:57, "funky" <marco.ronc...@gmail.comwrote:
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
sameodbcbut againsttablesconnectedwith the old "connection
string" and the new one. Each of the two maintables(QSHOC00 and
QGDLN00) has two index, for some unknown reason theodbcseems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan
--- Qtaord_somma_codici_impiegati ---
- Inputs to Query -
ODBCtable 'QSHOC00'
Using index 'QSHOCIX1'
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBCtable 'QGDLN00'
Table 'Tabella_ordini'
- End inputs to Query -
453469464) Remote SQL
01) Inner Join result of '00)' to table 'QSHOC00'
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of '01)' to table 'Tabella_ordini'
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of '02)'
Anyone knows why acces might get the wrong index. A thing i've
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the systemtables( likeMSysobjects) . i've
checked and , as a matter of fact, thetablesconnectedusing the new
odbchave a null field namedLvonMSysObjects, whileconnectedwith
the old one there were datas in it.
i don't know how to get out of this mess so any help would be greatly
appreciate.
- Inputs to Query -
Table 'Tabella_ordini'
ODBCtable 'QSHOC00'
Using index 'QSHOCIX'
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
ODBCtable 'QGDLN00'
- End inputs to Query -
01) Inner Join table 'Tabella_ordini' to table 'QSHOC00'
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of '01)'
03) Inner Join result of '01)' to result of '02)'
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of '03)'
05) Sort result of '04)'
Up, anybody can help, i'm realy stuck with this........
Your original post says
>If I change the query putting the two values of "ordini" in a where
>clause is again very fast so the problem must be in the join with
>TabellaOrdini.
suggesting that it might be better to replace this construct
SELECT ...
FROM Table1 INNER JOIN (Table2 INNER JOIN Table3 ON ...) ON ...)
with something in this form
SELECT ...
FROM Table1, Table2, Table3
WHERE Table1.foo=Table2.bar AND Table2.bar=Table3.baz
Try that. If it doesn't help, consider using a pass-through query
instead. See the Access help for details.- Hide quoted text -
- Show quoted text -

i've already tried this, and it doesn't work. The problem seems to be
in the way access uses the index on the table QSHOC: with the old odbc
driver by oracle only the primary index is seen as:

"QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls "

While linking the table with the new 10.1-10.2 odbc access sees two
index:

"QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls "

the strangest thing is in the different number of values. Another
strange thing is that the unique index is QSHOCIX but access seems
unable to use it, i've tried to drop QSHOCIX1 and also to define a
psuedo index but access choses anyway to use a full table scan
instead of using it. there is something, in the new way in wich the
table is linked, that seems to hint the query optimizer to not use the
primary index in any case.
In my company the policiy is to use odbc and linked tables for all the
access databases that users have. we need to mantain a certain
omogeneity and so we wouldn't use pass through query unless forced
to.
I fear this might be the case, because this query used to run in few
seconds an now it takes 5 minutes, and, like this, many other query on
the same table.
You could try the technique described here:

ACC2000: Creating Virtual Indexes with SQL Data-Definition Queries
http://support.microsoft.com/kb/q209123/

Or maybe try Microsoft's variant of the Oracle ODBC driver to see if
that does any better.

Apr 4 '07 #9

P: n/a
Gord wrote:
SELECT ...
FROM Table1, Table2, Table3
WHERE Table1.foo=Table2.bar AND Table2.bar=Table3.baz

Try that. If it doesn't help, consider using a pass-through query
instead. See the Access help for details.
Hi Gord,

I'm pretty sure the Oracle theta style joins won't work properly in a
Jet environment, which I assume Funky is using as he's describing linked
tables (I must confess, I haven't gone through this thread in detail, so
I might be missing something). Equi joins (analogous to the Jet Inner
join) do work for two tables, though - there was a thread I was involved
in the latter half of last year that discussed this.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Apr 4 '07 #10

P: n/a
Gord wrote:
Or maybe try Microsoft's variant of the Oracle ODBC driver to see if
that does any better.
Gord & Funky,

In the user group for the maintenance database app (which runs on
Oracle, MS SQL, Omnis engines) I run, there was a lot of difficulty
experienced by people using the 10g ODBC driver and linked tables in MS
Access. It turned out that many of the users settled on using the MS
driver which seemed to clear up the issues.

Another possiblity would be to try the Oracle ODBC driver for 8i or 9i,
which you should be able to download at OTN somewhere.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Apr 4 '07 #11

P: n/a
On 4 Apr, 18:44, "Gord" <g...@kingston.netwrote:
On Apr 4, 8:05 am, "funky" <marco.ronc...@gmail.comwrote:


On Apr 3, 4:45 pm, "Gord" <g...@kingston.netwrote:
On Apr 3, 5:48 am, "funky" <marco.ronc...@gmail.comwrote:
On 28 Mar, 13:57, "funky" <marco.ronc...@gmail.comwrote:
Ok, after enabling the JETSHOWPLAN flag i found out what the problem
is, now it remains to understand why. here are the two differnet
behaviour of the two identical query, running on the same pc with the
sameodbcbut againsttablesconnectedwith the old "connection
string" and the new one. Each of the two maintables(QSHOC00 and
QGDLN00) has two index, for some unknown reason theodbcseems to take
not the primary index on QSHOC and then perform first the inner join
with QGDLN and the the one with Tabella_ordini thus generating a huge
amount of net traffic and slowing down.
Here are the query plan
--- Qtaord_somma_codici_impiegati ---
- Inputs to Query -
>ODBCtable 'QSHOC00'
Using index 'QSHOCIX1'
Having Indexes:
QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls
>ODBCtable 'QGDLN00'
Table 'Tabella_ordini'
- End inputs to Query -
453469464) Remote SQL
01) Inner Join result of '00)' to table 'QSHOC00'
using parameterized distributed join
join expression "QGDLN00.GDLNITEM=QSHOC00.SHOCITEM"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
02) Inner Join result of '01)' to table 'Tabella_ordini'
using X-Prod join
then test expression "QSHOC00.SHOCPORD=Tabella_ordini.Ordine"
03) Group result of '02)'
Anyone knows why acces might get the wrong index. A thing i've
forgotten to mention is that we work now with a file .mdw for database
protection. A thing that came into my head is that this might mess
with the rights on the systemtables( likeMSysobjects) . i've
checked and , as a matter of fact, thetablesconnectedusing the new
>odbchave a null field namedLvonMSysObjects, whileconnectedwith
the old one there were datas in it.
i don't know how to get out of this mess so any help would be greatly
appreciate.
- Inputs to Query -
Table 'Tabella_ordini'
>ODBCtable 'QSHOC00'
Using index 'QSHOCIX'
Having Indexes:
QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls
>ODBCtable 'QGDLN00'
- End inputs to Query -
01) Inner Join table 'Tabella_ordini' to table 'QSHOC00'
using parameterized distributed join
join expression "Tabella_ordini.Ordine=QSHOC00.SHOCPORD"
then test expression "DateValue([SHOCDTIV])<#12/31/4712# And
DateValue([SHOCDTFV])=#12/31/4712#"
453469464) Remote SQL
02) Sort result of '01)'
03) Inner Join result of '01)' to result of '02)'
using temporary index
join expression "QSHOC00.SHOCITEM=QGDLN00.GDLNITEM"
04) Group result of '03)'
05) Sort result of '04)'
Up, anybody can help, i'm realy stuck with this........
Your original post says
If I change the query putting the two values of "ordini" in a where
clause is again very fast so the problem must be in the join with
TabellaOrdini.
suggesting that it might be better to replace this construct
SELECT ...
FROM Table1 INNER JOIN (Table2 INNER JOIN Table3 ON ...) ON ...)
with something in this form
SELECT ...
FROM Table1, Table2, Table3
WHERE Table1.foo=Table2.bar AND Table2.bar=Table3.baz
Try that. If it doesn't help, consider using a pass-through query
instead. See the Access help for details.- Hide quoted text -
- Show quoted text -
i've already tried this, and it doesn't work. The problem seems to be
in the way access uses the index on the table QSHOC: with the oldodbc
driver byoracleonly the primary index is seen as:
"QSHOCIX 10000 entries, 63 pages, 10000 values
which has 7 columns, fixed, unique, primary-key, no-nulls "
While linking the table with the new 10.1-10.2odbcaccess sees two
index:
"QSHOCIX1 1851162 entries, 2226 pages, 462791 values
which has 7 columns, fixed
QSHOCIX 1851162 entries, 2226 pages, 1851162 values
which has 7 columns, fixed, unique, primary-key, no-nulls "
the strangest thing is in the different number of values. Another
strange thing is that the unique index is QSHOCIX but access seems
unable to use it, i've tried to drop QSHOCIX1 and also to define a
psuedo index but access choses anyway to use a full table scan
instead of using it. there is something, in the new way in wich the
table is linked, that seems to hint the query optimizer to not use the
primary index in any case.
In my company the policiy is to useodbcand linked tables for all the
access databases that users have. we need to mantain a certain
omogeneity and so we wouldn't use pass through query unless forced
to.
I fear this might be the case, because this query used to run in few
seconds an now it takes 5 minutes, and, like this, many other query on
the same table.

You could try the technique described here:

ACC2000: Creating Virtual Indexes with SQL Data-Definition Querieshttp://support.microsoft.com/kb/q209123/

Or maybe try Microsoft's variant of theOracleODBCdriver to see if
that does any better.- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
I've already done it and access refuses to use it. I think, a t this
point, that access, considered the table structure received by the
ODBC linking, decides that the unique index is not convenient or
usable for this query.
I've researched a lot and i've found many topics about ORACLE HINTS,
is there anything similar in access? there is some way in wich i can
"force" jet to use a particular index on a specified table in a query?

Apr 5 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.