Q
Problem solve Get help with specific problems with your technologies, process and projects.

Improving performance of uploading a huge data file

Coming to the point directly, I have to upload a huge data file (record number up to 100 thousand) to the Oracle8i database. The file structure corresponds to a table in the database, so all the data from the file goes to a table in the database. But one more table needs to be populated from this file. Data from only one column (Password column) is taken and after applying an algorithm to encode the data it is stored in the second table (used only for login). The problem is that it is taking too much time, and we have to improve the performace. Please suggest me what way/method I can use to upload the data in the main/first table (in the second table we have to insert records one by one because they have to be encoded).

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


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close