Java Data base query/update for large amount of data

Question

What is the best way to implement the following scenario?

I need to call/query a data base table containing millions of records from a java application. Then for each records in the table, my application should call a third party API and get a status field as response. Then my application should again update each row in the table with the information (status) from the API.

Note - I am trying to figure out a method to do this in the best possible way. I understand that querying all the records together is not the best way forward.

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

Do not try to eat the elephant in one bite. Chunk it. Heard of pagination? Use it. See here: MySQL pagination without double-querying?

edited May 23 '17 at 10:34

Community

1
1

answered Jul 19 '12 at 16:25

Nishant

54,584
13
112
127

Yes, I have heard of pagination. I am trying to find out the best possible solution to my problem. It is never my intention to query and fetch all the records in one go. I was trying to figure out whether there is some parallel approach rather than a sequential one. – IS_EV Jul 19 '12 at 19:21
1

parallel would not buy you much. You will see bottlenecks at one or more level -- either sluggish Java performance, memory overhead, or RW slowness at your RDBMS level. If you want to churn out big stuff and fast, NoSQL approaches may help you. Even there I would suggest pagination, but since RW is blazing fast it will give you lot less latency than regular RDBMS. – Nishant Jul 20 '12 at 04:48

score 1 · Answer 2 · answered Jul 19 '12 at 19:12

1

you can use oracle feature such as SQL loader, Data pumping Called via JDBC or script..

answered Jul 19 '12 at 19:12

Shafi Ulla

79
5

you use the below link.. http://www.orafaq.com/wiki/SQL*Loader_FAQ Write into a script & call via JDBC API such execute() – Shafi Ulla Jul 20 '12 at 11:30

score 0 · Answer 3 · edited Jan 31 '18 at 13:41

0

Databases are not designed to update millions of records via Java API repeatedly. This can take many minutes. If this is not enough, you may need to use a dataset embedded in Java (either caching or replacing your database)

edited Jan 31 '18 at 13:41

diginoise

7,352
2
31
39

answered Jul 19 '12 at 16:26

Peter Lawrey

525,659
79
751
1,130

:I understand. I wanted to know what is the best approach for such a task. – IS_EV Jul 19 '12 at 21:26

Java Data base query/update for large amount of data

3 Answers3