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.

While Loop VS Long running transaction

4 posts in Performance and Tuning Last posting was on 2008-01-16 23:29:28.0Z
Luis Porras Posted on 2008-01-16 13:57:24.0Z
From: "Luis Porras" <luis.porras@gmail.com>
Newsgroups: sybase.public.ase.performance+tuning
Subject: While Loop VS Long running transaction
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 200.91.79.163
X-Original-NNTP-Posting-Host: 200.91.79.163
Message-ID: <478e0d44@forums-1-dub>
Date: 16 Jan 2008 05:57:24 -0800
X-Trace: forums-1-dub 1200491844 200.91.79.163 (16 Jan 2008 05:57:24 -0800)
X-Original-Trace: 16 Jan 2008 05:57:24 -0800, 200.91.79.163
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10612
Article PK: 89228

Hi,

I have a stored procedure that has been modified to use a loop (a while
loop, not a cursor) and for every iteration a transaction; initially the
stored procedure ran within only large transaction but because of the high
volume of the operation the database log went full.

My problem now is that I'm not getting a good performance within the loop:
I'm not using set rowcount 1 to get the next item to process, according to
the showplan all of indexes are being used correctly... I even changed one
of the locking scheme table from allpages to datapages with no results.

Any idea on how to improve performance on the loop?

Thanks in advance,

~Luis


Sherlock, Kevin Posted on 2008-01-16 22:33:16.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <478e0d44@forums-1-dub>
Subject: Re: While Loop VS Long running transaction
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: pakfw.chsomaha.org
X-Original-NNTP-Posting-Host: pakfw.chsomaha.org
Message-ID: <478e862c$1@forums-1-dub>
Date: 16 Jan 2008 14:33:16 -0800
X-Trace: forums-1-dub 1200522796 63.230.32.66 (16 Jan 2008 14:33:16 -0800)
X-Original-Trace: 16 Jan 2008 14:33:16 -0800, pakfw.chsomaha.org
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10615
Article PK: 89230

I wouldn't surprise me if you changed from comiting one single transaction,
to now commiting hundred "single row" transactions.

Instead of commiting a transaction at every iteration, commit after a
"batch" of iterations is done. IE:

set rowcount off
declare @batch_size int
,@batch_counter int
select @batch_size = 5000
,@batch_counter = 0
begin tran
while (some condition)
begin
update ... /* your DML goes here */
select @batch_counter = @batch_counter + 1
if (@batch_counter = @batch_size)
begin
commit tran
select @batch_counter = 0
begin tran
end
end /* end while */
commit tran /* commit any update left over from loop */

"Luis Porras" <luis.porras@gmail.com> wrote in message
news:478e0d44@forums-1-dub...
> Hi,
>
> I have a stored procedure that has been modified to use a loop (a while
> loop, not a cursor) and for every iteration a transaction; initially the
> stored procedure ran within only large transaction but because of the high
> volume of the operation the database log went full.
>
> My problem now is that I'm not getting a good performance within the loop:
> I'm not using set rowcount 1 to get the next item to process, according to
> the showplan all of indexes are being used correctly... I even changed one
> of the locking scheme table from allpages to datapages with no results.
>
> Any idea on how to improve performance on the loop?
>
> Thanks in advance,
>
> ~Luis
>


"Mark A. Parsons" <iron_horse Posted on 2008-01-16 23:29:28.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: While Loop VS Long running transaction
References: <478e0d44@forums-1-dub> <478e862c$1@forums-1-dub>
In-Reply-To: <478e862c$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
X-Original-NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
Message-ID: <478e9358$1@forums-1-dub>
Date: 16 Jan 2008 15:29:28 -0800
X-Trace: forums-1-dub 1200526168 67.87.252.233 (16 Jan 2008 15:29:28 -0800)
X-Original-Trace: 16 Jan 2008 15:29:28 -0800, ool-4357fce9.dyn.optonline.net
Lines: 60
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10616
Article PK: 89232

And if there's a need to maintain the value in @batch_counter for later use:

... snip ...

if @batch_counter % @batch_size = 0
begin
commit tran
begin tran
end

... snip ...

Sherlock, Kevin wrote:
> I wouldn't surprise me if you changed from comiting one single transaction,
> to now commiting hundred "single row" transactions.
>
> Instead of commiting a transaction at every iteration, commit after a
> "batch" of iterations is done. IE:
>
> set rowcount off
> declare @batch_size int
> ,@batch_counter int
> select @batch_size = 5000
> ,@batch_counter = 0
> begin tran
> while (some condition)
> begin
> update ... /* your DML goes here */
> select @batch_counter = @batch_counter + 1
> if (@batch_counter = @batch_size)
> begin
> commit tran
> select @batch_counter = 0
> begin tran
> end
> end /* end while */
> commit tran /* commit any update left over from loop */
>
> "Luis Porras" <luis.porras@gmail.com> wrote in message
> news:478e0d44@forums-1-dub...
>> Hi,
>>
>> I have a stored procedure that has been modified to use a loop (a while
>> loop, not a cursor) and for every iteration a transaction; initially the
>> stored procedure ran within only large transaction but because of the high
>> volume of the operation the database log went full.
>>
>> My problem now is that I'm not getting a good performance within the loop:
>> I'm not using set rowcount 1 to get the next item to process, according to
>> the showplan all of indexes are being used correctly... I even changed one
>> of the locking scheme table from allpages to datapages with no results.
>>
>> Any idea on how to improve performance on the loop?
>>
>> Thanks in advance,
>>
>> ~Luis
>>
>
>


Bret Halford Posted on 2008-01-16 18:49:57.0Z
Message-ID: <478E51D9.C8FCCB2D@sybase.com>
From: Bret Halford <bret@sybase.com>
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: While Loop VS Long running transaction
References: <478e0d44@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bret-xp.sybase.com
X-Original-NNTP-Posting-Host: bret-xp.sybase.com
Date: 16 Jan 2008 10:49:57 -0800
X-Trace: forums-1-dub 1200509397 10.21.37.181 (16 Jan 2008 10:49:57 -0800)
X-Original-Trace: 16 Jan 2008 10:49:57 -0800, bret-xp.sybase.com
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10614
Article PK: 89231


Luis Porras wrote:

> Hi,
>
> I have a stored procedure that has been modified to use a loop (a while
> loop, not a cursor) and for every iteration a transaction; initially the
> stored procedure ran within only large transaction but because of the high
> volume of the operation the database log went full.
>
> My problem now is that I'm not getting a good performance within the loop:
> I'm not using set rowcount 1 to get the next item to process, according to
> the showplan all of indexes are being used correctly... I even changed one
> of the locking scheme table from allpages to datapages with no results.
>
> Any idea on how to improve performance on the loop?
>
> Thanks in advance,
>
> ~Luis

Perhaps if you posted the actual TSQL code?