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.

begin tran - commit tran

4 posts in General Discussion Last posting was on 2013-02-14 23:23:12.0Z
RGS Posted on 2013-02-14 17:20:05.0Z
Sender: 67e9.511d1ad0.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: begin tran - commit tran
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <511d1cc5.68bc.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 14 Feb 2013 09:20:05 -0800
X-Trace: forums-1-dub 1360862405 172.20.134.41 (14 Feb 2013 09:20:05 -0800)
X-Original-Trace: 14 Feb 2013 09:20:05 -0800, 172.20.134.41
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31700
Article PK: 1307654

Hi!

I have a batch program like this:

cursor

select account...

begin tran

exec sp1
exec sp2
...
exec spN

commit tran

end cursor

E.d. I am processing each account with begin tran - commit
tran

(assume sp's are big and complex)

If I change the program, for processing the transaction
(begin tran - commit tran) in groups (for example 1,0000
accounts per transaction) the time that I could win is high?
Is possible to reduce the time 2 or 4 times? Remember, sp's
are complex and have a lot of ASE sentences


Thanks!


Bret Halford Posted on 2013-02-14 21:12:24.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: begin tran - commit tran
References: <511d1cc5.68bc.1681692777@sybase.com>
In-Reply-To: <511d1cc5.68bc.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: <511d5338$1@forums-1-dub>
Date: 14 Feb 2013 13:12:24 -0800
X-Trace: forums-1-dub 1360876344 172.20.134.152 (14 Feb 2013 13:12:24 -0800)
X-Original-Trace: 14 Feb 2013 13:12:24 -0800, vip152.sybase.com
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31701
Article PK: 1307667


On 2/14/2013 10:20 AM, RGS wrote:
> Hi!
>
> I have a batch program like this:
>
> cursor
>
> select account...
>
> begin tran
>
> exec sp1
> exec sp2
> ...
> exec spN
>
> commit tran
>
> end cursor
>
> E.d. I am processing each account with begin tran - commit
> tran
>
> (assume sp's are big and complex)
>
> If I change the program, for processing the transaction
> (begin tran - commit tran) in groups (for example 1,0000
> accounts per transaction) the time that I could win is high?
> Is possible to reduce the time 2 or 4 times? Remember, sp's
> are complex and have a lot of ASE sentences
>
>
> Thanks!
>

This shouldn't be a difficult coding change to make, I recommend
you just go ahead and try it and benchmark the performance.

Every time you commit a transaction, ASE does have to flush the
log records that are currently in the user log cache (ULC / PLC)
to disk, so you will save a bit of time waiting for the physical
i/o to complete.

However, you will end up holding locks for a longer time,
potentially blocking other users for longer periods of time,
reducing their performance. By committing your transactions more
frequently, you can improve concurrency.

-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


RGS Posted on 2013-02-14 22:21:01.0Z
Sender: 67e9.511d1ad0.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: begin tran - commit tran
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <511d634d.7fab.1681692777@sybase.com>
References: <511d5338$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 14 Feb 2013 14:21:01 -0800
X-Trace: forums-1-dub 1360880461 172.20.134.41 (14 Feb 2013 14:21:01 -0800)
X-Original-Trace: 14 Feb 2013 14:21:01 -0800, 172.20.134.41
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31702
Article PK: 1307668

Thank you very mutch, but I have two questions:

1)I assume that user log cache is in memory, then it would
be very fast?

2) If I don't have more concurrent users (e.d. I won't have
block problems) the I will win a lot of time with this
change?


Thanks again!!!

> On 2/14/2013 10:20 AM, RGS wrote:
> > Hi!
> >
> > I have a batch program like this:
> >
> > cursor
> >
> > select account...
> >
> > begin tran
> >
> > exec sp1
> > exec sp2
> > ...
> > exec spN
> >
> > commit tran
> >
> > end cursor
> >
> > E.d. I am processing each account with begin tran -
> > commit tran
> >
> > (assume sp's are big and complex)
> >
> > If I change the program, for processing the transaction
> > (begin tran - commit tran) in groups (for example 1,0000
> > accounts per transaction) the time that I could win is
> > high? Is possible to reduce the time 2 or 4 times?
> > Remember, sp's are complex and have a lot of ASE
> sentences >
> >
> > Thanks!
> >
>
> This shouldn't be a difficult coding change to make, I
> recommend you just go ahead and try it and benchmark the
> performance.
>
> Every time you commit a transaction, ASE does have to
> flush the log records that are currently in the user log
> cache (ULC / PLC) to disk, so you will save a bit of time
> waiting for the physical i/o to complete.
>
> However, you will end up holding locks for a longer time,
> potentially blocking other users for longer periods of
> time, reducing their performance. By committing your
> transactions more frequently, you can improve concurrency.
>
> -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-02-14 23:23:12.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: begin tran - commit tran
References: <511d5338$1@forums-1-dub> <511d634d.7fab.1681692777@sybase.com>
In-Reply-To: <511d634d.7fab.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: <511d71e0$1@forums-1-dub>
Date: 14 Feb 2013 15:23:12 -0800
X-Trace: forums-1-dub 1360884192 172.20.134.152 (14 Feb 2013 15:23:12 -0800)
X-Original-Trace: 14 Feb 2013 15:23:12 -0800, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31703
Article PK: 1307670


On 2/14/2013 3:21 PM, RGS wrote:
> Thank you very mutch, but I have two questions:
>
> 1)I assume that user log cache is in memory, then it would
> be very fast?

User log cache is in memory. Every connection has one. It
accumulates transaction log records as they are generated
until it fills or the transaction ends. The log records
are then flushed to a syslogs page in data cache, and if the
transaction ended, the syslogs page is then flushed to disk.
The main purpose (and performance improvement) from the ULC
is reducing contention on the last page of syslogs (the insertion
point for new log records) among many concurrent processes,
as the processes are now inserting batches of records
rather than individual records.

The slow part comes when the buffers in cache have to be
flushed to disk.

> 2) If I don't have more concurrent users (e.d. I won't have
> block problems) the I will win a lot of time with this
> change?

I would expect some improvement, but have no basis to give
you an estimate from.


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