Poor performance running queries with tables linked to Oracle Back

[stara]

Hi,

Currently we have Microsoft Access database with tables linked to Oracle
views. However with certain queries, it takes about 9 hours to run. The
linked tables have about half- million records. The query uses linked table
and few local tables. I'm not sure how to improve performance. I even tried
ADP. I tried to use ADO programming to link to Oracle back-end. But still the
performance is bad.

Could you please suggest ideas?

Thanks a lot,
Stara

 

[Jerry Whittle]

Are you joining with a primary key field to a foriegn key field?

How fast do the views run in Oracle? If they are slow there, Access won't
make them any faster. In a similar vein if you're using a tool like TOAD,
remember that the speed of a query or view isn't when the first record is
returned. Rather it's when all records are returned that counts.

If nothing else, post the SQL here. Maybe even the view's SQL too.

 

[stara]

Views in oracle run quickly (few seconds) .However, the access query which
uses the view and few local tables runs an append query and takes very long
time to return results.

Sometimes even keyword search on the view runs for more than 4 hours

Here is the SQL:

INSERT INTO Table2 ( PROB_NUMBER, TITLE, UNIT_NAME, OBJID, STAT, UNIT, [QRA
System], PROB_DESC )
SELECT TEST_VIEW.PROB_NUMBER, TEST_VIEW.TITLE, tbl_local_reg.REG_NAME,
TEST_VIEW.OBJID, TEST_VIEW.STAT, TEST_VIEW.UNIT, tbl_local_code.[QRA System
Category], TEST_VIEW.PROB_DESC
FROM ((TEST_VIEW INNER JOIN tbl_local_reg ON TEST_VIEW.UNIT =
tbl_local_reg.REG_CODE) INNER JOIN tbl_local_product ON
TEST_VIEW.PROB_NUMBER= tbl_local_product.PROB_NUMBER) LEFT JOIN
tbl_local_code ON TEST_VIEW.QRA_CODE = tbl_local_code.[QRA System-Sorted];

Appreciate your help!
Stara

 

[Jerry Whittle]

Hummmm. There's nothing that really jumps out in the SQL statement. Here's a
couple of things to try:

If Table2 has any indexes, you might want to drop them before doing the
insert then recreate the indexes afterwards. This is often faster. However if
you have any unique indexes or primary key fields, you may want to keep those
indexes to prevent duplicates.

Conversely make sure that the following fields are indexed:
tbl_local_reg.REG_CODE
tbl_local_product.PROB_NUMBER
tbl_local_code.[QRA System-Sorted]

Really grasping at straws here, but try doing a Compact and Repair before
the append query runs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Views in oracle run quickly (few seconds) .However, the access query which
uses the view and few local tables runs an append query and takes very long
time to return results.

Sometimes even keyword search on the view runs for more than 4 hours

Here is the SQL:

INSERT INTO Table2 ( PROB_NUMBER, TITLE, UNIT_NAME, OBJID, STAT, UNIT, [QRA
System], PROB_DESC )
SELECT TEST_VIEW.PROB_NUMBER, TEST_VIEW.TITLE, tbl_local_reg.REG_NAME,
TEST_VIEW.OBJID, TEST_VIEW.STAT, TEST_VIEW.UNIT, tbl_local_code.[QRA System
Category], TEST_VIEW.PROB_DESC
FROM ((TEST_VIEW INNER JOIN tbl_local_reg ON TEST_VIEW.UNIT =
tbl_local_reg.REG_CODE) INNER JOIN tbl_local_product ON
TEST_VIEW.PROB_NUMBER= tbl_local_product.PROB_NUMBER) LEFT JOIN
tbl_local_code ON TEST_VIEW.QRA_CODE = tbl_local_code.[QRA System-Sorted];

Appreciate your help!
Stara

Are you joining with a primary key field to a foriegn key field?

How fast do the views run in Oracle? If they are slow there, Access won't
make them any faster. In a similar vein if you're using a tool like TOAD,
remember that the speed of a query or view isn't when the first record is
returned. Rather it's when all records are returned that counts.

If nothing else, post the SQL here. Maybe even the view's SQL too.

 

[stara]

It looks like the view is causing a lot of overhead. Is there a way to dump
the view to a local table quickly. I don't want to use the Import./Export
feature since this take a long time too.

So I can run the query off of local table rather than using the Oracle view.

Could you please suggest if there are any tools?

Thanks,
Stara

Hummmm. There's nothing that really jumps out in the SQL statement. Here's a
couple of things to try:

If Table2 has any indexes, you might want to drop them before doing the
insert then recreate the indexes afterwards. This is often faster. However if
you have any unique indexes or primary key fields, you may want to keep those
indexes to prevent duplicates.

Conversely make sure that the following fields are indexed:
tbl_local_reg.REG_CODE
tbl_local_product.PROB_NUMBER
tbl_local_code.[QRA System-Sorted]

Really grasping at straws here, but try doing a Compact and Repair before
the append query runs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Views in oracle run quickly (few seconds) .However, the access query which
uses the view and few local tables runs an append query and takes very long
time to return results.

Sometimes even keyword search on the view runs for more than 4 hours

Here is the SQL:

INSERT INTO Table2 ( PROB_NUMBER, TITLE, UNIT_NAME, OBJID, STAT, UNIT, [QRA
System], PROB_DESC )
SELECT TEST_VIEW.PROB_NUMBER, TEST_VIEW.TITLE, tbl_local_reg.REG_NAME,
TEST_VIEW.OBJID, TEST_VIEW.STAT, TEST_VIEW.UNIT, tbl_local_code.[QRA System
Category], TEST_VIEW.PROB_DESC
FROM ((TEST_VIEW INNER JOIN tbl_local_reg ON TEST_VIEW.UNIT =
tbl_local_reg.REG_CODE) INNER JOIN tbl_local_product ON
TEST_VIEW.PROB_NUMBER= tbl_local_product.PROB_NUMBER) LEFT JOIN
tbl_local_code ON TEST_VIEW.QRA_CODE = tbl_local_code.[QRA System-Sorted];

Appreciate your help!
Stara

Are you joining with a primary key field to a foriegn key field?

How fast do the views run in Oracle? If they are slow there, Access won't
make them any faster. In a similar vein if you're using a tool like TOAD,
remember that the speed of a query or view isn't when the first record is
returned. Rather it's when all records are returned that counts.

If nothing else, post the SQL here. Maybe even the view's SQL too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi,

Currently we have Microsoft Access database with tables linked to Oracle
views. However with certain queries, it takes about 9 hours to run. The
linked tables have about half- million records. The query uses linked table
and few local tables. I'm not sure how to improve performance. I even tried
ADP. I tried to use ADO programming to link to Oracle back-end. But still the
performance is bad.

Could you please suggest ideas?

Thanks a lot,
Stara