Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to update 600,000 rows in batches

4 posts in General Discussion Last posting was on 2013-01-23 22:09:28.0Z
Kiran Chalapaka Posted on 2013-01-23 16:03:59.0Z
Sender: 2e72.510008ab.1804289383@sybase.com
From: Kiran Chalapaka
Newsgroups: sybase.public.ase.general
Subject: How to update 600,000 rows in batches
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <510009ef.2eed.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 23 Jan 2013 08:03:59 -0800
X-Trace: forums-1-dub 1358957039 172.20.134.41 (23 Jan 2013 08:03:59 -0800)
X-Original-Trace: 23 Jan 2013 08:03:59 -0800, 172.20.134.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31671
Article PK: 1159264

Hi,

I need to update more than 600K rows in a table. I know I
should not do this with a single update as it may exhaust
all the available locks. I also know that this should be
done in batches but I dont know the process.

Can anyone please share me a sample sql block which does
this. My update query is a fairly simple one.

Thanks in advance.

Regards,
Kiran


Bret Halford Posted on 2013-01-23 16:19:55.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to update 600,000 rows in batches
References: <510009ef.2eed.1681692777@sybase.com>
In-Reply-To: <510009ef.2eed.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <51000dab@forums-1-dub>
Date: 23 Jan 2013 08:19:55 -0800
X-Trace: forums-1-dub 1358957995 172.20.134.152 (23 Jan 2013 08:19:55 -0800)
X-Original-Trace: 23 Jan 2013 08:19:55 -0800, vip152.sybase.com
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31672
Article PK: 1159265


On 1/23/2013 9:03 AM, Kiran Chalapaka wrote:
> Hi,
>
> I need to update more than 600K rows in a table. I know I
> should not do this with a single update as it may exhaust
> all the available locks. I also know that this should be
> done in batches but I dont know the process.
>
> Can anyone please share me a sample sql block which does
> this. My update query is a fairly simple one.
>
> Thanks in advance.
>
> Regards,
> Kiran
>

Hi Kiran,

There are two main reasons to do this - limiting the
number of locks as you mention, and also running out of
space in syslogs. If transaction log space is not
an issue, you could simply use the LOCK TABLE command
prior to the UPDATE to ensure only a single table-level
lock was used.

Is it possible to tell from the row contents whether
the row has been updated yet or not (ideally based on
indexed keys)? If so, you can do something like this:

set rowcount(10000)
select 1 -- to give @@rowcount a non-zero value
while (@@rowcount > 0)
begin
update mytable
set [...]
where [status when row not yet updated]
end

If not, do you have an identity column or other unique
keys? What are they?

-bret

--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021


Kiran Chalapaka Posted on 2013-01-23 16:50:46.0Z
Sender: 2e72.510008ab.1804289383@sybase.com
From: Kiran Chalapaka
Newsgroups: sybase.public.ase.general
Subject: Re: How to update 600,000 rows in batches
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <510014e6.3376.1681692777@sybase.com>
References: <51000dab@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 23 Jan 2013 08:50:46 -0800
X-Trace: forums-1-dub 1358959846 172.20.134.41 (23 Jan 2013 08:50:46 -0800)
X-Original-Trace: 23 Jan 2013 08:50:46 -0800, 172.20.134.41
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31673
Article PK: 1159266

Hi Bret,

Thanks a lot for your quick response.

Yes, it is possible to tell from the contents of the row if
it updated or not. So I think your approach would work.

At the same time, I would also like to know how I should
proceed if it is not the case. My table has unique index.

Thanks again.

Best Regards,

Kiran

> On 1/23/2013 9:03 AM, Kiran Chalapaka wrote:
> > Hi,
> >
> > I need to update more than 600K rows in a table. I know
> > I should not do this with a single update as it may
> > exhaust all the available locks. I also know that this
> > should be done in batches but I dont know the process.
> >
> > Can anyone please share me a sample sql block which does
> > this. My update query is a fairly simple one.
> >
> > Thanks in advance.
> >
> > Regards,
> > Kiran
> >
>
> Hi Kiran,
>
> There are two main reasons to do this - limiting the
> number of locks as you mention, and also running out of
> space in syslogs. If transaction log space is not
> an issue, you could simply use the LOCK TABLE command
> prior to the UPDATE to ensure only a single table-level
> lock was used.
>
> Is it possible to tell from the row contents whether
> the row has been updated yet or not (ideally based on
> indexed keys)? If so, you can do something like this:
>
> set rowcount(10000)
> select 1 -- to give @@rowcount a non-zero value
> while (@@rowcount > 0)
> begin
> update mytable
> set [...]
> where [status when row not yet updated]
> end
>
> If not, do you have an identity column or other unique
> keys? What are they?
>
> -bret
>
> --
> Bret Halford
> Support Architect, ASE Tactical Support Team, AGS Primary
> Support Sybase, Inc., an SAP Company
> 385 Interlocken Crescent, Suite 300, Broomfield, Colorado,
> 80021


Bret Halford Posted on 2013-01-23 22:09:28.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to update 600,000 rows in batches
References: <51000dab@forums-1-dub> <510014e6.3376.1681692777@sybase.com>
In-Reply-To: <510014e6.3376.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <51005f98$1@forums-1-dub>
Date: 23 Jan 2013 14:09:28 -0800
X-Trace: forums-1-dub 1358978968 172.20.134.152 (23 Jan 2013 14:09:28 -0800)
X-Original-Trace: 23 Jan 2013 14:09:28 -0800, vip152.sybase.com
Lines: 112
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31676
Article PK: 1253214

The general idea is to break up the table into
subsets that can be be processed efficiently.
So using an indexed key is good.

As an example, if the leading column of the primary
key is a datetime, you might do something like this.
The technique relies on the fact that variables are
assigned the value of the last row returned in the result
set.



declare @range_min datetime
declare @range_max datetime

select @range_max = min(pk_column) from mytable

while (@@rowcount > 0)
begin
select @range_min = @range_max

set rowcount 10000
select @range_max = pk_column
from mytable
where pk_column > @range_min
order by pk_column
set rowcount 0

update mytable
set [...]
where pk_column
between @range_min and @range_max
end

On 1/23/2013 9:50 AM, Kiran Chalapaka wrote:
> Hi Bret,
>
> Thanks a lot for your quick response.
>
> Yes, it is possible to tell from the contents of the row if
> it updated or not. So I think your approach would work.
>
> At the same time, I would also like to know how I should
> proceed if it is not the case. My table has unique index.
>
> Thanks again.
>
> Best Regards,
>
> Kiran
>
>> On 1/23/2013 9:03 AM, Kiran Chalapaka wrote:
>>> Hi,
>>>
>>> I need to update more than 600K rows in a table. I know
>>> I should not do this with a single update as it may
>>> exhaust all the available locks. I also know that this
>>> should be done in batches but I dont know the process.
>>>
>>> Can anyone please share me a sample sql block which does
>>> this. My update query is a fairly simple one.
>>>
>>> Thanks in advance.
>>>
>>> Regards,
>>> Kiran
>>>
>>
>> Hi Kiran,
>>
>> There are two main reasons to do this - limiting the
>> number of locks as you mention, and also running out of
>> space in syslogs. If transaction log space is not
>> an issue, you could simply use the LOCK TABLE command
>> prior to the UPDATE to ensure only a single table-level
>> lock was used.
>>
>> Is it possible to tell from the row contents whether
>> the row has been updated yet or not (ideally based on
>> indexed keys)? If so, you can do something like this:
>>
>> set rowcount(10000)
>> select 1 -- to give @@rowcount a non-zero value
>> while (@@rowcount > 0)
>> begin
>> update mytable
>> set [...]
>> where [status when row not yet updated]
>> end
>>
>> If not, do you have an identity column or other unique
>> keys? What are they?
>>
>> -bret
>>
>> --
>> Bret Halford
>> Support Architect, ASE Tactical Support Team, AGS Primary
>> Support Sybase, Inc., an SAP Company
>> 385 Interlocken Crescent, Suite 300, Broomfield, Colorado,
>> 80021

--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021