Hello,
I'm a DBA of a staging database, with a table containing several hundred thousand records.

I have written all of the programs to manipulate new and updated data, as it is loaded from it's various sources.

My issue is that a couple of my programs need to run through each row, row by row. For example, I need to work out if a job was executed on time by looking at the day it was s
tarted, the day it was closed and the number of days the job should have taken. So for each row, I execute one update statement... i.e "UPDATE JOBS SET ON_TIME = '1' WHERE JOB_ID = '12345' "

The program runs fine, but my issue is on speed. When I was testing the data, with only a thousand rows or so, these queries used to complete instantly. Now with upwards of 500,000 rows, it can take up to 5 seconds for each query to run.

I have a window of several hours where I will be the only user, so concurrency is not an issue. I am new to DB2, but surely there is a way to run these queries a lot quicker?

Thanks a lot for your time,
Matt


DB2 Windows Server Edition 2003
8.1 fixpack 15

Your problem is that you are doing too many round trips to the database to do the work. You are reading each row and then possibly updating it based on some criteria. The more rows you do this algorithm on the longer it will take. Instead of updating each row individually, update the rows in a large batch with a single update statement.

Andy

Thanks a lot!!