Sql, selecting and updating

Question

Hi,

I am trying to select 100s of rows at a DB that contains 100000s of row and update those rows afters.

the problem is I don't want to go to DB twice for this purpose since update only marks those rows as "read".

is there any way I can do this in java using simple jdbc libraries? (hopefully without using stored procedures)

update: ok here is some clarification.

there are a few instance of same application running on different servers, they all need to select 100s of "UNREAD" rows sorted according to creation_date column, read blob data within it, write it to file and ftp that file to some server. (I know prehistoric but requirements are requirements)

The read and update part is for to ensure each instance getting diffent set of data. (in order, tricks like odds and evens wont work :/)

We select data for update. the data transfers through the wire (we wait and wait) and then we update them as "READ". then release lock for reading. this entire thing takes too long. By reading and updating at the same time, I would like to reduce lock time (from time we use select for update to actual update) so that using multiple instances would increase read rows per second.

Still have ideas?

Answer 1

Can't you just use the same connection without closing it?

Answer 2

Going to the DB isn't so bad. If you aren't returning anything 'across the wire' then an update shouldn't do you too much damage and its only a few hundred thousand rows. What is your worry?

Answer 3

If you're doing a SELECT in JDBC and iterating over the ResultSet to UPDATE each row, you're doing it wrong. That's an (n+1) query problem that will never perform well.

Just do an UPDATE with a WHERE clause that determines which of those rows needs to be updated. It's a single network round trip that way.

Don't be too code-centric. Let the database do the job it was designed for.

Answer 4

It seems to me there might be more than one way to interpret the question here.

1. You are selecting the rows for the sole purpose of updating them and not reading them.
2. You are selecting the rows to show to somebody, and marking them as read either one at a time or all as a group.
3. You want to select the rows and mark them as read at the time you select them.

Let's take Option 1 first, as that seems to be the easiest. You don't need to select the rows in order to update them, just issue an update with a WHERE clause:

update table_x
set read = 'T'
where date > sysdate-1;

Looking at option 2, you want to mark them as read when a user has read them (or a down stream system has received it, or whatever). For this, you'll probably have to do another update. If you query for the primary key, in addition to the other columns you'll need in the first select, you will probably have an easier time of updating, as the DB won't have to do table or index scans to find the rows.

In JDBC (Java) there is a facility to do a batch update, where you execute a set of updates all at once. That's worked out well when I need to perform a lot of updates that are of the exact same form.

Option 3, where you want to select and update all in one shot. I don't find much use for this, personally, but that doesn't mean others don't. I suppose some kind of stored procedure would reduce the round trips. I'm not sure what db you are working with here and can't really offer specifics.