need help w oracle related methods
below is pseudocode for 2 database related methods that are causing a huge bottleneck.
can anyone suggest a way to reorganize these methods so that they would be more efficient. the part of the code that's probably the bottleneck is ps_queryIndividualid = c.prepareStatement("SELECT INDIVIDUAL_SEQ.CURRVAL AS id FROM DUAL"); (2a)
update_table1()
-
1. a while loop - select * from table3, loop through rows round
ps_lookupTable3 = c3.prepareStatement("SELECT id, firstname, middlename, lastname, birthdate, ssn FROM table3");
2. lookup individual in table1 based on ssn from the query in #1
ps_lookupBySsn = c.prepareStatement("SELECT id, firstname, middlename, lastname, suffix, ison, birthdate, createtime, modifytime FROM table1 WHERE ssn = ?");
-gives you a boolean found_in_table1
A. if found_in_table1 = false, then insert in table1
ps_insertTable1 = c.prepareStatement("INSERT INTO table1 (prefix, firstname, middlename, lastname, suffix, ssn, ison, birthdate, createtime, createuserid, createsourceid, modifytime, modifyuserid, modifysourceid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 2, ?, ?, 2)");
**the value of individualid is populated as an autoincrement value (via trigger) during the above insert
a. after inserting, query for new individualid just inserted
ps_queryIndividualid = c.prepareStatement("SELECT INDIVIDUAL_SEQ.CURRVAL AS id FROM DUAL");
**The above prepared statement causes a bottleneck here as it generates huge oracle result sets**
B. if found_in_table, capture the existing id, then update modification time for the row in table1
ps_updateIndividual = ...
3. end while loop from #1
update_table2()
-
**at this point, id=the value from last time ps_insertTable1 or ps_updateTable1 is executed, it is the id of the person in table1 that was just inserted or updated above**
1. lookup person in table2 whose individualid matches id from above
ps_lookupTable2 = c.prepareStatement("SELECT individualid, createtime, modifytime FROM table2 WHERE individualid = ?")
A. if person doesn't exist, insert into staff table
ps_insertTable2 = ...
B. if person does exist, update the staff table
ps_updateTable2 = ..
