Java application - How to optimize database calls and space & time which is more important?

Question

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?

Answer 1

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?

This is totally dependent on the application. The question is: does it meet your performance requirements?

For a simple app that runs once a week, use of time and memory is probably not very important at all. I would write the simple version (get all at once in a single query) first. As long as it performs fine, I wouldn't worry about it. And 1K times several thousand is probably not going to cause a problem.

If you experience any performance problems, only then should you bother to modify it to use multiple DB calls.

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?

As above, your performance requirements should be driving such decisions. But in this case, making millions of DB calls is more likely to be problematic. And in addition, a single update that modifies many rows at once is typically easy to write.

So, I would aim for a single update statement that updates everything, or at most one update statement per orgID.

Answer 2

There is probably some part of pre-emptive micro-optimisation here but I think I can address some general points here.

Prepare realistic tests

However there is quite a lot you can do so let's start by the very beginning

1. Since this is a weekly operation you do not need to run it instant. Define a realistic window of time execution
2. Build realistic test data (or if you can have the true ones it's even better)
3. Benchmark.
4. You can try to change the size of you test data to have a very rough estimation of the time complexity.

Out of Memory ?

Considering the size of your data and your blob you will probably end up with memory errors. This point has been addressed in StackOverflow

If you use an ORM or EntityManager from JPA, you may consider to change for a raw JDBC operation. Otherwise don't forget to flush the cache using the method flush.

Database calls

Multiple network calls can easily become a bottleneck. A full join can cost a lot too to the database. Instead of fetching every user using orgID in (orglist) you may choose to perform one query per orgID. Unless you have 10000ish differents orgID, the cost of this will be nothing. Of course you have to be sure that your amount of memory will follow.

Multithreading

Consider this only if the following requirements are all mets :

• The processing of datas in Java to compute your result take a measurable amount of time in the whole process.
• You don't need all of this to run in a single transaction.
• You can split your process to make sure that each row won't be lock by multiple thread (for instance 1/10 orgID per thread). Be sure that you database will properly apply row-level locking, unless you're dealing with a legacy environment this point should be fine. **
• Unless some micro optimisations are really ugly in terms of maintenancy,..., you're pretty much out of options.

Prepare for the future

If your time windows is something like 5h max and your database is expected to grow and live for instance 10 years you can choose between :

1. Ensure this have good chance to work properly even in 10 years by estimating the expected volume of data.
2. Estimate when (or more likely, at which volume of data) this piece of code will need rework or increase the windows time.

Benchmarks

Yes i already said it but to be sure you won't forget, every modification to improve performance are likely to increase the complexity of your code.

Validate by a benchmark each design/code modification that what you did had a real impact.

Answer 3

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?

There are a few layers to this. When you select a large set of rows from the DB, it doesn't really send you a huge clump of bytes. What happens is that there is a cursor on the DB with a size that you can configure. So if your cursor size is 1000 rows, it's not going to grab all the rows at once. You generally won't need to worry about this except for things I mention later in this answer.

However, the other aspect of this what you do with the rows. If you pull them all into a list on your application (which is what people do with ORMs) you are going to need allocate space for all of these records. I hate this approach. It's the most common cause of bloated Java programs. You really should be looping over the records from the database connection as an iterator.

or it is better to make multiple db call?

This depends on concurrency considerations discussed below.

time and space, which is more important?

Time-space trade-off analysis is something that applies when you have algorithmic complexity. This doesn't seem like one of those cases. If all you are doing is reading a single row and writing an update to it, using more memory won't speed things up. In fact, it will probably slow down your program. It takes time to allocate and manage memory. You try to allocate no more memory than you need to do the next task.

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'm assuming here that you need to look at each row and make an update that cannot simply be done in a one-shot SQL statement. There's not a simple answer to this question and that's because it depends on whether there will be other applications interacting with these tables while you are doing this.

If you are doing 'select for update', you probably don't want to do this in one big commit. The reason you would use that is to prevent other applications from modifying the data between the selection and the update. In other words you would be locking all of these records for the duration.

Even if you are not worried about concurrency or dirty reads, you probably don't want to change a million records and then commit at the end because:

1. If there is an issue at any point in this process, even a minor one, you have to start over from the beginning.
2. Writing a ton of uncommitted changes to a DB puts a lot of stress on it's resources.

So there's a sweet spot for performance for commits. You commit each one and there will be a little overhead. You could commit them in batches but your retry gets a little more complicated. Personally, unless you know there is a performance concern, I'd probably commit each change immediately. It's the easiest to get right. A million records is really not that much per day. And 1K isn't much data. Oracle (for example) won't even bother putting 1K off-table unless you tell it to.