Java – How to prevent a long running SQL query from looking stuck

At a previous job, we used what I jokingly called "redundant array of inexpensive computers" {1} to distribute a number of jobs. Some took seconds to run, others took more than a day (quarterly reports). All were designed to be capable of being restarted if they crashed. Some jobs ran on a schedule, some were run only on demand. The schedule was sophisticated enough to include things as simple as "run daily" or "run monthly" or as complicated as "run monthly, except for the last week of the quarter run daily". Any of the regularly scheduled tasks could be run "now" (on demand).

Because the array {2} lacked displays, the "scoreboard" of who was running what job was read from a database table. In order to determine if a job crashed or hung, I took a concept from embedded computers called a "watch dog". In this case, a running job would periodically update a status column with a 0. The monitoring software would periodically increment that status column, and if the number got above a threshold in a certain period of time, would send a message to a person to basically reboot machine X. The idea was to put this "heart beat" at the end of a loop, but not the innermost loop. I planned to have the "pulse" go off about every minute with the checking circuit checking every 5 or 10 minutes.

Notes:
1 - Folks might now call this a "grid" or "cluster", but they were a pile of Pentium 75 and 100 that hadn't been depreciated, so I could use them.
2 - Well, a 1 dimensional array, as they stacked reasonably well and were worthless enough that if they fell over and broke, no one would miss them. They hadn't been fully depreciated yet, so technically could not be discarded, but were slow enough that they were useless as desktop computers.

You should put correctness first. Create your data structures so that they model the domain in question in a correct and effective way that makes it easy for your code to work with.

Beyond that, try to minimize database calls, especially if the database is not local (residing on the same machine as the program calling it). Network latency is a real consideration here, and it can be non-trivial.

Let's say you have an operation that requires 10 database calls. If your network latency is 100 ms, this operation will take 1 second of pure overhead just communicating with the server, in addition to whatever amount of time it takes to actually do the work involved. If your latency is 1 second, it will waste 10 seconds on network latency alone. But if you get that down from 10 calls to 1, suddenly even in really ugly latency conditions, you're not wasting much time on network overhead.

As a general rule of thumb, if you're just retrieving data simply (and not doing heavy processing of the data inside the database server or on the client), the biggest bottleneck by far in a system with a non-local database will be network latency. So if you can reduce the number of calls, even if it means you need to do extra work on the client side once you've retrieved it, you'll still probably come out ahead.

As always, remember the most important rule of optimization: measure first! Optimize by hard data, not by rules of thumb like the one I just described, or you could easily end up doing a lot of hard work that slows things down! But in general, keeping the number of queries down is usually the best route.