I am writing a simple Java app to run weekly. The app need call database to get data, check it and update.

The flow I need is little as following:

• select configure,orgID where status=true from orgs;
  • orgs has thousands of row, configure is blob
• Check blob object configure, and filtered the orgID list
• for filtered orgID list, select * from users where status=true and orgID in (orglist)
  • users is a huge table. for each orgID, there can be as much as 400k users.
• for users information, we update one column of all these users

I have couple questions:

1. when we select configure this field is blob and it can be as large as 1k. Is it good practice to get thousands of rows at one time? or it is better to make multiple db call? time and space, which is more important?

2. users table has millions rows, we need update perhaps 1 column for 1 million rows. what is a good practice to make this update? is it better to make 1 million db call? or as little db calls as possible?

I am writing a simple Java app to run weekly. The app need call database to get data, check it and update.

The flow I need is little as following:

• select configure,orgID where status=true from orgs;
  • orgs has thousands of rows, configure is blob
• Check blob object configure, and filtered the orgID list
• for filtered orgID list, select * from users where status=true and orgID in (orglist)
  • users is a huge table. for each orgID, there can be as much as 400k users.
• for users information, we update one column of all these users

I have couple questions:

1. when we select configure this field is blob and it can be as large as 1k. Is it good practice to get thousands of rows at one time? or it is better to make multiple db call? time and space, which is more important?

2. users table has millions rows, we need update perhaps 1 column for 1 million rows. what is a good practice to make this update? is it better to make 1 million db call? or as little db calls as possible?