Re: Ideas on running a large UPDATE causing Locks...
From: Roz (anonymous_at_discussions.microsoft.com)
Date: 06/25/04
- Next message: Sunanda: "login security question"
- Previous message: Geoff N. Hiten: "Re: server component is not supported on this operating system"
- In reply to: Tom Moreau: "Re: Ideas on running a large UPDATE causing Locks..."
- Next in thread: Dan Guzman: "Re: Ideas on running a large UPDATE causing Locks..."
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Jun 2004 11:03:57 -0700
Beautiful. Will give it a shot.
Thanks again to all who replied.
Roz
>-----Original Message-----
>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
>>
>>.
>>
>
>.
>
- Next message: Sunanda: "login security question"
- Previous message: Geoff N. Hiten: "Re: server component is not supported on this operating system"
- In reply to: Tom Moreau: "Re: Ideas on running a large UPDATE causing Locks..."
- Next in thread: Dan Guzman: "Re: Ideas on running a large UPDATE causing Locks..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
