Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Exclamation Unanswered: SQL Updates take too long!

    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
    Last edited by MattGill; 12-20-09 at 20:18. Reason: added os info and db2 version

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by ARWinner View Post
    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!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •