I have been trying to solve this problem for several days but still can not have a good result.
I am trying to update the data of table B based on table A
In table A, we have around 1 million records
A(userID, userName, address, country, postcode )
what I need to do is to extract some information based on the selected attributes and with this information, update table B's newValue column
B(user ID, newValue)
the method I have used is that first create rs,
As you can see, that I need to loop 1 million times to finish the upating. The result is that it takes me a long time to wait.
Then i used another method that first create a procedure inside the SQL server: CREATE PROCEDURE updatetable;
then inside the while loop, I use the CallableStatement to call this procedure.
The time is reduced, but still it need to run around about 10 minutes, and I think this is not a good result.
Can any one tell me if there will be any good solutions???
Jan Cumps wrote:"Harry Potter",
Welcome to the Ranch.
please check your private messages for an administrative issue.
When posting code, please use the code tags (available when writing/editing a post) to properly format the code.
I have done this for your first post.
Thank you very much! I will pay attention to it in the future
Ravi Kiran Va wrote:
insert 1 million record
I bet this will be not easy .
As a thumb rule you shouldn't insert so many records at a single instance .
You need to make them into different batches of data and deal with them .
Thank you very much for the reply!
This afternoon, I have spent some time to see if I can speed up based on some methods that I found.
Currently, i have learnt that I can speed up with the following method:
1: within the while loop, I write the resultset into a txt file.
2: then with the txt file, I use BulkInsert funtion to load this txt file into table B
In this way, the time has been reduced dramatically: the exchange from DB to txt cost me around 1 minutes, and to send the txt file back to the DB is only around 40 seconds.
The total time now in my opinion is good.
But if anyone has some more faster method, please let me know!
Redo logging will still probably kill the performance. If need be you could always take the CREATE as SELECT, DROP, RENAME approach with little or no logging to speed things up.
Even better than the subselect might be an updatable join (called sometimes key-preserved join), see this discussion.
The CREATE AS SELECT approach is also promising, but might be more complicated from the administrative point of view, as explained here.
Edit: if you want both INSERT and UPDATE operations in one go, look up the MERGE statement in Oracle documentation for your version.