Hi Gerry,
do I remember correctly and you said that you had upgraded the database
to a new version and now the report is taking forever where it used to
run quickly on the previous version?
If so, the Access/ODBC part might be the cause. Regardless of how simple
it all is, Access/ODBC might be butchering your query as per something I
discovered years ago with Access 97. (It may still apply!)
-
Basically a single table SELECT passed to Oracle would be fine.
-
Join two tables and it might get passed to Oracle correctly as a
joined pair of tables query, or, it might get passed to Oracle as a
couple of queries as follows:
select stuff from table_one;
for (each row in the above query) loop
select more_stuff from table_two
where table_two.something = :bind_variable;
end loop;
The “:bind_variable” in the second bit is the join column(s) from
table_one. The columns that it should have just joined on. So the
following SQL:
select emp.ename, dept.dname
from emp,dept
where emp.dept_id = dept.dept_id;
Would hit the database as:
select emp.ename, emp.dept_id
from emp;
Followed by a lot of:
select dept.dname
from dept
where dept.dept_id = :dept_id;
And the “:dept_id” bind would be every single one from the list of
employees in the first query. Obviously the problem is that while there
are possibly hundreds (or millions!) of employees, there are only 10
departments, but we need to fetch the department name hundreds (of
millions) of times. Not good for performance.
It does even worse things at times as well. But I won’t go there!
I’m wondering if your query is being abused in a manner similar to the
above? What do you see on the database in database->monitor->session
browser (do you have the DBA option?) when Access is doing it’s thing?
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk