aspose file tools*
The moose likes JDBC and the fly likes How to update or insert 1 million record to table B based on table A faster? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to update or insert 1 million record to table B based on table A faster?" Watch "How to update or insert 1 million record to table B based on table A faster?" New topic
Author

How to update or insert 1 million record to table B based on table A faster?

harryedi potter
Greenhorn

Joined: Jan 17, 2011
Posts: 3
Hello,

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???

thank you!
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

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 .


Save India From Corruption - Anna Hazare.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

"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.


OCUP UML fundamental and ITIL foundation
youtube channel
harryedi potter
Greenhorn

Joined: Jan 17, 2011
Posts: 3
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
harryedi potter
Greenhorn

Joined: Jan 17, 2011
Posts: 3
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!

Thank you!

Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1773
    
    7

Oracle does have SUBSTR and REPLACE functions. You could probably do this with a single UPDATE statement and a sub-SELECT.
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.


Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I agree with Jelle. Doing it with a single SQL statement is much much faster than your original approach. There are many factors that influence the performance, but I would not be surprised if the single SQL statement was five to ten times faster than one-by-one JDBC approach.

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to update or insert 1 million record to table B based on table A faster?