EXPERT RESPONSE
It sounds like the issue here is simply figuring out how to improve the
performance of the inserts into the table that require encoding. So....what
you want to do is to cut down on how many times you have to pass an insert
statement to the server for execution. From the way it sounds like you have
it coded now, you are doing a single insert for each encoded password row.
You could store the rows of data into a PL/SQL collection (INDEX BY table,
VARRAY or NESTED TABLE) and then use the FORALL statement to "bulk bind" a
whole bunch of inserts together and send the "batch" to the server. This
can be a huge performance benefit as it will send a whole set of rows to be
inserted to the server instead of sending each insert one at a time.
Here's a very simple example that perhaps you can use to base your own code
on:
DECLARE
TYPE PassTab IS TABLE OF number(5) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
p_passwords PassTab;
p_names NameTab;
BEGIN
FOR i in 1..1000 LOOP -- load index-by tables
p_passwords(i) := i ;
p_names(i) := 'Name ' || i ;
END LOOP ;
FORALL i in 1..1000 -- use FORALL statement to INSERT all 1000 rows at
once
INSERT INTO pswd_table (pswd_key, pswd_encoded) VALUES (p_names(i),
p_passwords(i));
END;
/
For your application, you'd need to create the index by table to hold the
correct datatype that your encoded password will be (e.g. VARCHAR2(30)).
Then, where I simply used a loop to store the numbers 1-1000 into the index
by table, you would actually create your loop to take the original password
value and do whatever you need to handle the encoding of it and then store
that encoded value into the index by table. Obviously, if the table where
you are storing the encoded password has a key field, you'll need to create
an index by table to store that value also so that when you do the insert
you'll have all the values you need for the values clause (similar to how I
used p_names).
Anyway, using this method, you can DRASTICALLY cut back on the time required
to process the inserts. I've seen timings in my own tests that take 50000
rows and insert them 1 at a time and it takes let's say 2 minutes. Using
the FORALL to do the inserts in bulk, it reduces the time to around 15
seconds! Quite a performance boost. Try it out for yourself and see if it
doesn't help immensely.
For more info on bulk binding and the use of the FORALL statement, see the
Oracle PL/SQL Users Guide and Reference document.
For More Information
|
