Re: Ideas on running a large UPDATE causing Locks...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 06/25/04 

Date: Fri, 25 Jun 2004 10:48:23 -0400

Yep, it's a loop. You could play around with the number of rows to be
updated concurrently. For example, if you don't get much blocking with
10,000 rows, then pump it up to 10,000 for each iteration. Here's a sample:

declare
     @Current int
, @Max int
, @Date datetime

select @Current = min (ID), @Max = max (ID), @Date = getdate ()
from MyTable

while @Current <= @Max
begin
    update MyTable
    set
        MyColumn = @Date
    where
        ID between @Current and @Current + 1000

    set @Current = @Current + 1000
end 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roz" <anonymous@discussions.microsoft.com> wrote in message
news:2148701c45ac0$82268640$a301280a@phx.gbl...
Tom,
Thanks for the reply.  How would I go about updating 1000
rows at a time.  How would I do something like this?  I
imagine I'd do this in a loop?  Any pointers would help.
Thanks again
Roz
>-----Original Message-----
>Updating that many rows is going to have an impact, even
if you have an
>index on the columns involved in the WHERE clause.  If
you want to set all
>those rows to the same date, store it in a variable and
then use a loop to
>update, say, 1000 rows at a time.  This way, you are not
blocking too many
>users at a time.
>
>-- 
>Tom
>
>----------------------------------------------------------
-----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>
>"Roz" <anonymous@discussions.microsoft.com> wrote in
message
>news:213d701c45ab6$e3765fb0$a301280a@phx.gbl...
>Hello, all.  Need help fast.  Yesterday evening, before
>leaving work, I started a job that Updates 37 million rows
>in my table.  The Update is being done on only one
>column...I'm setting getdate() to a datetime column.
>
>Last night from home, about 6 hours later, I checked the
>status, and noticed the job was still running.  This
>morning back at work, it was still running.  So, I stopped
>the job, which in turn has caused a major lock as now a
>Rollback is occuring.  The rollback can probably take as
>long as the initial update was taking before I stopped it.
>
>My question is what's the fastest, most efficient way to
>Update this many records?  and/or is there a way to kill
>this process?  My users are getting restless.
>
>Thanks In advance
>Roz
>
>.
>

Relevant Pages

  • Re: Polling, Interrupts, DMA, Synchronous, Asynchronous I/O Definitions
    ... > Sorry, but even with blocking, polling is still polling ... > (though blocking makes it less costly). ... > Even though a blocking API is called, the loop is still ... "GetMessage" does with messages, but on the condition of "key ...
    (alt.lang.asm)
  • Re: ADO Recordset Update & SQL Server 2005 ??
    ... I've tried explicit .Update inside the loop and outside the loop as a batch ... There's no 500 error (asp and iis 6) posted on the page. ... This error shows up ONLY when connected to a sql server 2005 server. ... the same database schema/data in sql server 2005, ...
    (microsoft.public.data.ado)
  • Re: getting a threads state and CPU utilization
    ... a thread -- that is, running, suspended, or waiting on a synchronization ... deadlock by waiting on objects that form a loop, ... You couldn't just look for a long blocking wait since it might be ...
    (microsoft.public.win32.programmer.kernel)
  • Re: DTS Workflow Question
    ... >I am using SQL Server 2000 and I am writing a DTS package that loops ... >Script handling the loop routine. ... You could duplicate the ActiveX Script to restart the loop, ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: Vlookup nightmare
    ... >excel can _NOT_ do loops like SQL can. ... >SQL Server can loop through using a cursor. ... Procedural code can loop through a cursor into a dbms table. ... But for those not so few who have MSFT Office ...
    (microsoft.public.excel)