Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: Update in Sybase is super slow (again)

    Hi guys,

    I know this has been discussed before but I don't think it was answered completely. I got a st. proc. which does a fairly simple update to 1 single table. I am NOT using cursors (knowing how bad it is), just 1 single update. The statements is required to update 700,000 rows And it takes 1 hr 28 mins to complete. The table being updated is indexed. I've already created a #temp table to 1st determine which rows to be updated (which only takes 3.163 secs to do) then I simply join the #temp table with the real table for the update. Code as below:

    declare @tmp_CurrentDate datetime
    select @tmp_CurrentDate = getdate()
    create table #temp_of_Table1
    (Col1 varchar(10),
    Col2 varchar(10),
    Col3 varchar(10),
    Col4 varchar(10),
    NeedsToBeUpdated tinyint)

    -- ok, the select for this was a bit more complicated then this
    -- but this is just a simplified version of it for arguments sack
    -- I know if I am just filtering by datetime then I don't need this temp table
    -- and I don't need a join. But this is just an example.
    insert into #temp_of_Table1
    select Col1,Col2,Col3,Col4,1 from Table1
    where Col5 < "20080101"
    -- where Col5 is the Last Updated date time stamp of the row in Table1
    -- and Col1,Col2,Col3 is a long key of Table1
    -- up to this point it only takes a few secs to run
    -- and #temp_of_Table1 is about 700,000 rows in size.

    update Table1
    set Col5 = @tmp_CurrentDate
    from Table1 a, #temp_of_Table1 b
    where b.NeedsToBeUpdated = 1
    and a.Col1 = b.Col1
    and a.Col2 = b.Col2
    and a.Col3 = b.Col3

    -- ok

    Now, this update took 1 hr 28 mins. Help please??? any one?

    Thank You in Advance.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Your problem is the join to the temp table with no index
    But I think you don't need a temp table. How many rows in Table1?
    Code:
    update Table1
    set Col5 = getdate() 
    where Col5 < '20080101'

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Hi,

    As i was stating in the code comments, the reason for the #temp table was because of a more complicated preprocessing to choose which rows in Table1 to update with. The select into #temp was ONLY a sample but NOT the real code for building the #temp table. Of course if I am simply updating using the date on Col5 then I don't need the #temp table.

    Ok ok. The #temp table was actually created using bcp in of a set of new data from data files coming in from 3rd parties. And these 80% of data in these files are already in Table1 from previous imports. So for these 80% of rows, instead of reinserting them into Table1, I just want to update the date time that their are reupdated. And this is the update statement for.

    Table1 got 2.5 Million rows in it.

    So you think I should index Col1, Col2, Col3 of the #temp table? Table1 already indexed with Col1, Col2, Col3 in 1 index.

    Thx for ur help.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by alphaprime
    required to update 700,000 rows
    Quote Originally Posted by alphaprime
    So you think I should index Col1, Col2, Col3 of the #temp table?
    Yes I think so
    Last edited by pdreyer; 06-26-09 at 06:48.

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    OK. Let me try. I'll be back.....

    Thx

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    How many rows are there in the temp table with
    NeedsToBeUpdated != 1
    When last was statistics updated
    When last did you do a reorg
    or recreate the index on Table1
    What is the output from a showplan and optdiag

    Can't help but think there is something you are not telling us.
    Quote "The #temp table was actually created using bcp in"
    It is not possible to bcp in to a #temp table

  7. #7
    Join Date
    Jun 2009
    Posts
    5
    hi,
    ok, tried to create index on #temp. But optimiser says the #temp table doesn't exist. And I had to try the wrapper st proc approach instead. So I made a wrapper st proc to create the #temp table and populated it and then created the index on it. And then tried to create the 2nd st proc with recompile which has the join and update on Table1 in it with recompile. But it won't let me create the st proc, saying #temp table not declared. So stuck.

    Ok, u r right, I didn't bcp directly into the #temp. I bcp'ed into a permanent table without index on it 1st. Then I insert into the #temp after I created it in the st proc. Sorry, because that part only took 7.7secs to complete, I didn't think that was the problem and didn't need to include that in the post.

    So I insert into #temp table select *,0 from bcp_table
    the 0 was for the NeedToBeUpdated column.
    then I did a join (without any indexes on #temp) with Table1 using Col1, Col2, Col3 like this:

    update #temp_of_Table1
    set NeedsToBeUpdated = 1
    from Table1 a, #temp_of_Table1 b
    where a.Col1 = b.Col1
    and a.Col2 = b.Col2
    and a.Col3 = b.Col3

    And that took only 9.4 secs to run.
    Out of the 700,000 rows in the #temp table, 668,000 rows were updated to 1. And the rest are 0.

    Stats are updated automatically on Table1 every night at 2am.
    Reorg? umm about 5 yrs ago
    Recreate index on Table1? ummm ago 12 months ago.

    Sigh, u r going to ask me to do reorg now, aint ya?
    ok, then worth a try, I'll be back in a few days then.....
    Thx

  8. #8
    Join Date
    Jun 2009
    Posts
    5
    Hi pdreyer,
    ok, done. Stats update had no effect on the update. But you've nailed it with the reorg! took 3 hrs 45 mins to reorg but now the update takes 6 mins 59 secs instead of 1.5 hrs. Well Done!!
    Thank You Very Much for your help.

Posting Permissions

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