Slowing update on table input please 1

[JtheRipper]

Hi,

We are upgrading our labware system to a later version which requires a few fields to be added to existing tables. these tables have quite a few records in them ranging from 1million to 30million. We then started a job to update all the null records in these new fields to a not null value in batches.

The problem now is that when we started the batch update it took approximately 2 minutes. Now the more records are updated the slower the update/batch job takes. I have updated the database statistics (sp_updatestats). We are currently also using performance monitor to gather some information and so far it looks like there might be some disk I/O. we have the same setup on our DEV server (obviously not the same load as on the prd server), and the updates take the same time as in the beginning. The explain plans on the dev and prd servers are the same.

declare @x int
declare @repeat_cnt     int
declare @sample_row_cnt int
declare @test_row_cnt   int
declare @result_row_cnt int


select  @x = QPC_VALUE
from    qpc_details
where   QPC_CODES = 'XXX'

select  @repeat_cnt = QPC_VALUE
from    qpc_details
where   QPC_CODES = 'REPEAT_CNT'

select  @sample_row_cnt = QPC_VALUE
from    qpc_details
where   QPC_CODES = 'SAMPLE'

select  @test_row_cnt = QPC_VALUE
from    qpc_details
where   QPC_CODES = 'TEST'

select  @result_row_cnt = QPC_VALUE
from    qpc_details
where   QPC_CODES = 'RESULT'


WHILE @x < @repeat_cnt
BEGIN
--- SAMPLE TABLE--------------------------------------------
  set rowcount @sample_row_cnt
  UPDATE  labware_database.dbo.sample 
    set   approved = 'F'
    WHERE approved is null;


  set rowcount @sample_row_cnt
  UPDATE  labware_database.dbo.sample 
    set   ready_for_approval = 'F'
    WHERE ready_for_approval is null;


  set rowcount @sample_row_cnt
  UPDATE  labware_database.dbo.sample 
    set   modified_results = 'F'
    WHERE modified_results is null;


  set rowcount @sample_row_cnt
  UPDATE  labware_database.dbo.sample 
    set   composite = 'F'
    WHERE composite is null;


  set rowcount @sample_row_cnt
  UPDATE  labware_database.dbo.sample 
    set   parent_composite = 0
    WHERE parent_composite is null;


  set rowcount @sample_row_cnt
  UPDATE  labware_database.dbo.sample 
    set   investigated = 'F'
    WHERE investigated is null;



--- TEST TABLE----------------------------------------------
  set rowcount @test_row_cnt
  UPDATE  labware_database.dbo.test
    set   re_tested = 'F'
    WHERE re_tested is null;


  set rowcount @test_row_cnt
  UPDATE  labware_database.dbo.test
    set   modified_results = 'F'
    WHERE modified_results is null;


  set rowcount @test_row_cnt
  UPDATE  labware_database.dbo.test
    set   aliquoted_to = 0
    WHERE aliquoted_to is null;


  set rowcount @test_row_cnt
  UPDATE  labware_database.dbo.test
    set   on_worksheet = 'F'
    WHERE on_worksheet is null;


  print cast(getdate() as varchar) + ' After Test...'

---RESULT TABLE---------------------------------------------
  set rowcount @result_row_cnt
  UPDATE  labware_database.dbo.result
    set   spec_override = 'F'
    WHERE spec_override is null;

  print cast(getdate() as varchar) + ' After Result...'

  set @x = @x + 1

END

Any ideas on where one can look for problems, or is using performance monitor the right way to go?

Thanks,
J.

 

[nigelrivett]

When you started all the data in the column was null so if rowcount was 1000 it would update the first 1000 rows it read then stop.
Next timee it has to read 2000 recs to update the batch and so on getting progressively slower.
The only way round this would be to add an index to the column.
This would slow down updates but not getting the data.

What you are doing would fragment that index and also the data page and hence all other indexes due to page splitting.

I don't recommend doing this the way you are. see

http://www.nigelrivett.net/AlterTableProblems.html

And you won't be able to make the column not nullable.

I would bcp the data out and in to a new table.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JtheRipper]

Thanks for the reply Nigel, don't know why I haven't thought of this (the progressive slower update because of the rowcount!) It makes a lot of sense.

J.