How to speed up appends from A97 to Oracle 8i

[antomack]

I have migrated (using OMWB) some of the tables from an Access 97 application to Oracle 8i and am now trying to get it to run quicker. The application when completely in A97 took between 30 and 40 minutes to run, now with two of the larger tables in Oracle it is taking 160 to 200 minutes. This time difference is due really to two particular queries which append data from 'temporary' tables in Access to the Oracle tables. These appends involve about 300,000 records and are currently taking just over 60 minutes each compared to less than 2 minutes when appending to an Access backend. Since the Oracle tables only contain 5 weeks worth of data so far it seems that it's only going to take even longer when I put the 2 years worth of data into them.

The move to Oracle is a definite requirement since we need to store over 2 years of data at a time, c.31 million records. Within Access this involves dividing the backend database into 5 seperate databases.

Has anyone out there got any advice on how to speed the processing of the two append queries, the SQL for them is given below.

INSERT INTO PolicyPremium ( Year_no, Week_no, System_Type, Policy_no, Agent_no, Kind_Code, Version, Product_type, Status, Premium, Freq, Cobe, Add_Con, Commence_Date )
SELECT DISTINCT DPOLICY.Year_no, DPOLICY.Week_no, "DUB" AS Expr1, DPOLICY.POL_NUMBER, DPOLICY.AGENT_NUMB, CobeType.Kind_Code, CobeType.Version, CobeType.Product_Type, StatusCodes.Indicator, DPOLICY.ANNUAL_P, DPOLICY.FREQUENCY, DPOLICY.COBE, DPOLICY.ADDL_CONT AS Add_Con, DPOLICY.COMMENCE_DATE
FROM (DPOLICY LEFT JOIN CobeType ON DPOLICY.COBE = CobeType.COBE) LEFT JOIN StatusCodes ON DPOLICY.STATUS = StatusCodes.Code
WHERE ((DPOLICY.Year_no=[Year?]) AND (DPOLICY.Week_no=[Week?]));

INSERT INTO Max_Archive
SELECT Max.*
FROM Max;

 

[fej7yhdoq7]

An well-tuned Oracle server is generally very fast. Do you have many indexes on the destination table?

 

[antomack]

There is a Primary Key of Year_no, Week_no and Policy_no on both tables. Then each has an index on Policy_no also.

 

[rcurva]

Are all of these tables sitting in the Oracle database but linked to A97 via ODBC?

Although they are Oracle tables, I still think that the entire SQL processing will be done in the access engine. Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[antomack]

The two tables involved are stored in Oracle now and linked by ODBC, Oracle ODBC Driver version 8.00.06.00. I have also tried writing code using OO4O to do the append but can't seem to get it to work, it seems to be doing the append without any errors but just hangs and even after being left running overnight it is still processing the next day.

Anyone out there ever been in a similar situation, how do you do appends from Access to Oracle?

 

[rcurva]

We usually create a stored procedure in Oracle to do all these insert and then call this procedure from Access.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

Are both tables in the same Oracle database?

 

[antomack]

Yes, both tables are contained in the same Oracle database within the same tablespace.

I have no experience really with Oracle, how do you create the stored procedures and how do you call them? How do they link to the Access tables, since this is where they have to get the data from in order to do the append? I'm afraid I'm going to need fairly specific instructions as it's all new to me, so thanks to all for your help and patience so far.

If there are any particular details I can provide that may help in determining a solution let me know. I really need to get this working a lot quicker than currently as I have two other Access applications which will require similar migration and all of these impact on the overnight job schedule. Since they are the last jobs run if they significantly increase in time taken to run it will mean additional time requirements on the overnight schedule and thus further money, etc..

 

[rcurva]

Here's how you create a stored procedure, from sqlplus, run the following;

CREATE OR REPLACE insert_proc(v_year NUMBER, v_week NUMBER)
IS
BEGIN
INSERT INTO PolicyPremium
( Year_no, Week_no, System_Type, Policy_no, Agent_no, Kind_Code, Version, Product_type, Status, Premium, Freq, Cobe, Add_Con, Commence_Date )
SELECT DISTINCT DPOLICY.Year_no, DPOLICY.Week_no, 'DUB', DPOLICY.POL_NUMBER, DPOLICY.AGENT_NUMB, CobeType.Kind_Code, CobeType.Version, CobeType.Product_Type, StatusCodes.Indicator, DPOLICY.ANNUAL_P, DPOLICY.FREQUENCY, DPOLICY.COBE, DPOLICY.ADDL_CONT, DPOLICY.COMMENCE_DATE
FROM DPOLICY, CobeType, StatusCodes
WHERE DPOLICY.COBE(+) = CobeType.COBE
AND DPOLICY.STATUS(+) = StatusCodes.Code
AND DPOLICY.Year_no=v_year
AND DPOLICY.Week_no=v_week;

INSERT INTO Max_Archive
SELECT Max.*
FROM Max;

COMMIT;
END;
/


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[antomack]

Robbie,
Thanks for the details on creating the stored procedure but my question now is how does one call this from Access. Also as the tables I am appending from are in Access how does the Oracle procedure read from these tables in order to do the append. Another thing is that the two queries are run at different stages of the application run so will they have to be done as two seperate stored procedures?

 

[rcurva]

PolicyPremium and Max_Archive is presumed to be Oracle tables in my example.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[antomack]

PolicyPremium and Max_Archive are tables within the Oracle database but the tables these are being populated from are in Access, i.e. DPOLICY, CobeType, StatusCodes for Policy Premium and Max for Max_Archive. So how do I speed up the append taking this inot account, will stored procedures still work?