• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 .

 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"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.
 
harryedi potter
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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!

 
Bartender
Posts: 1952
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Do Re Mi Fa So La Tiny Ad
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic