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.

Cursor for Update

7 posts in General Discussion Last posting was on 2011-03-16 19:40:18.0Z
RGS Posted on 2011-03-15 22:56:31.0Z
Sender: 4924.4d7e2eb9.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Cursor for Update
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d7fee9f.1308.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Mar 2011 14:56:31 -0800
X-Trace: forums-1-dub 1300229791 10.22.241.41 (15 Mar 2011 14:56:31 -0800)
X-Original-Trace: 15 Mar 2011 14:56:31 -0800, 10.22.241.41
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30044
Article PK: 79273

Hi!

I have a stored procedure with sentences like that:

declare cursor c1 for select .. from table1 for read only
....
update table1
set c1 = @w1
where c2 = @w2 -- @w2 is reading from the cursor and c2
is the key of the table
.....
close cursor c1

I know this program is wrong, because the cursor has to be
declare "for update of c1" and I have to update the record
with "update of current c1"

The question is, what is the negative effect with the
original program? The program will be slower? There will be
more locks?


Thanks!


Rob V [ Sybase ] Posted on 2011-03-16 09:20:02.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cursor for Update
References: <4d7fee9f.1308.1681692777@sybase.com>
In-Reply-To: <4d7fee9f.1308.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: <4d8080c2@forums-1-dub>
Date: 16 Mar 2011 01:20:02 -0800
X-Trace: forums-1-dub 1300267202 10.22.241.152 (16 Mar 2011 01:20:02 -0800)
X-Original-Trace: 16 Mar 2011 01:20:02 -0800, vip152.sybase.com
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30045
Article PK: 79274


On 15-Mar-2011 23:56, RGS wrote:
> Hi!
>
> I have a stored procedure with sentences like that:
>
> declare cursor c1 for select .. from table1 for read only
> ....
> update table1
> set c1 = @w1
> where c2 = @w2 -- @w2 is reading from the cursor and c2
> is the key of the table
> .....
> close cursor c1
>
> I know this program is wrong, because the cursor has to be
> declare "for update of c1" and I have to update the record
> with "update of current c1"
>
> The question is, what is the negative effect with the
> original program? The program will be slower? There will be
> more locks?
>
>
> Thanks!

I would not say this code is 'wrong'. It could be a perfectly valid way
of doing your processing.There wouldn't be much locking overhead to
speak of.
You *can* also use cursor update with 'update of current', but you do
not have to do that.
In fact, your code above may be faster than doing a cursor update,
because an updatable cursor has restrictions w.r.t. the indexes it can
use. As a result, doing the update through the cursor could use a less
efficient query plan than the direct update above.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


RGS Posted on 2011-03-16 16:59:45.0Z
Sender: 4924.4d7e2eb9.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Cursor for Update
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d80ec81.3bfc.1681692777@sybase.com>
References: <4d8080c2@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Mar 2011 08:59:45 -0800
X-Trace: forums-1-dub 1300294785 10.22.241.41 (16 Mar 2011 08:59:45 -0800)
X-Original-Trace: 16 Mar 2011 08:59:45 -0800, 10.22.241.41
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30051
Article PK: 79280

Thanks a lot,

But is so bad that Sybase create a CURSOR FOR UPDATE but in
real word it doesn't have clear advantages, in fact, you
said my original code is better!!

Think about a company that are doing hundred of changes in
the cursors thinking the CURSOR FOR UPDATE has important
advantages!

Do you sell your book in Amazon?

Thanks!

> On 15-Mar-2011 23:56, RGS wrote:
> > Hi!
> >
> > I have a stored procedure with sentences like that:
> >
> > declare cursor c1 for select .. from table1 for read
> > only ....
> > update table1
> > set c1 = @w1
> > where c2 = @w2 -- @w2 is reading from the cursor
> > and c2 is the key of the table
> > .....
> > close cursor c1
> >
> > I know this program is wrong, because the cursor has to
> > be declare "for update of c1" and I have to update the
> > record with "update of current c1"
> >
> > The question is, what is the negative effect with the
> > original program? The program will be slower? There will
> > be more locks?
> >
> >
> > Thanks!
>
> I would not say this code is 'wrong'. It could be a
> perfectly valid way of doing your processing.There
> wouldn't be much locking overhead to speak of.
> You *can* also use cursor update with 'update of current',
> but you do not have to do that.
> In fact, your code above may be faster than doing a cursor
> update, because an updatable cursor has restrictions
> w.r.t. the indexes it can use. As a result, doing the
> update through the cursor could use a less efficient
> query plan than the direct update above.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>


Rob V [ Sybase ] Posted on 2011-03-16 18:36:54.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cursor for Update
References: <4d8080c2@forums-1-dub> <4d80ec81.3bfc.1681692777@sybase.com>
In-Reply-To: <4d80ec81.3bfc.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: <4d810346@forums-1-dub>
Date: 16 Mar 2011 10:36:54 -0800
X-Trace: forums-1-dub 1300300614 10.22.241.152 (16 Mar 2011 10:36:54 -0800)
X-Original-Trace: 16 Mar 2011 10:36:54 -0800, vip152.sybase.com
Lines: 140
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30054
Article PK: 79282

I did *not* say your code was better. I said it *may* be faster,
depending on circumstances.
I have also seen cases where only a cursor-based algorithm was able to
achieve the required performance (though in general, I think it's best
to avoid cursors, for various reasons).
The question should really be: how does performance work out in your
specific cases with a cursor-based update and with a direct update as in
your specific example. That is ultimately what matters the most. Until
that is tested (with real-life data) all discussions are somewhat
theorical in nature, and different arguments can be made under equally
different assumptions.

HTH,

Rob V.

On 16-Mar-2011 17:59, RGS wrote:
> Thanks a lot,
>
> But is so bad that Sybase create a CURSOR FOR UPDATE but in
> real word it doesn't have clear advantages, in fact, you
> said my original code is better!!
>
> Think about a company that are doing hundred of changes in
> the cursors thinking the CURSOR FOR UPDATE has important
> advantages!
>
> Do you sell your book in Amazon?
>
> Thanks!
>
>
>> On 15-Mar-2011 23:56, RGS wrote:
>>> Hi!
>>>
>>> I have a stored procedure with sentences like that:
>>>
>>> declare cursor c1 for select .. from table1 for read
>>> only ....
>>> update table1
>>> set c1 = @w1
>>> where c2 = @w2 -- @w2 is reading from the cursor
>>> and c2 is the key of the table
>>> .....
>>> close cursor c1
>>>
>>> I know this program is wrong, because the cursor has to
>>> be declare "for update of c1" and I have to update the
>>> record with "update of current c1"
>>>
>>> The question is, what is the negative effect with the
>>> original program? The program will be slower? There will
>>> be more locks?
>>>
>>>
>>> Thanks!
>> I would not say this code is 'wrong'. It could be a
>> perfectly valid way of doing your processing.There
>> wouldn't be much locking overhead to speak of.
>> You *can* also use cursor update with 'update of current',
>> but you do not have to do that.
>> In fact, your code above may be faster than doing a cursor
>> update, because an updatable cursor has restrictions
>> w.r.t. the indexes it can use. As a result, doing the
>> update through the cursor could use a less efficient
>> query plan than the direct update above.
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>

--
Dear Sybase user,

I am currently unable to respond to the specific question(s) which you have sent me. Due to the ever increasing number of questions I am receiving, I currently simply don't have the time to answer detailed individual questions.
Please post your question to one of the ASE-related newsgroups so that the ASE community can participate/benefit.
These newsgoups are comp.databases.sybase (Usenet), as well as various newsgroups on Sybase's own news server (forums.sybase.com).
Also, please provide sufficient technical details (error messages, actual queries, query plans, etc.) about your problem.
Try the Sybase FAQ at http://www.isug.com/Sybase_FAQ/, which contains answers to many questions about ASE.

Kind regards,

Rob Verschoor

If you don't want to use X Windows, you could try "sybinit4ever", a free tool which uses only an ASCII interface to create a new ASE server. It can be downloaded from http://www.sypron.nl/si4evr.html

See the
ASE reference manual / System Administration Guide / Transact-SQL user's guide
This / These books can be viewed or downloaded as PDF files from the Sybase website http://www.sybase.com/support/manuals/ . For more details how to get there, see http://www.sypron.nl/sybbooks.html .

For more information on the background of this problem, as well as a solution, see http://www.sypron.nl/idgaps.html .

... syntax and description are in the ASE Quick Reference Supplement, which you can download from http://www.sypron.nl/ase_qref.html (for a better version, see my book -- www.sypron.nl/qr).


To use dynamic SQL, you need at least ASE version 12.0, which has the "execute immediate" feature for this. In earlier versions of ASE you can simulate some types of dynamic SQL though; for more information, see http://www.sypron.nl/dynsql.html and http://www.sypron.nl/dynsqlcis.html .

For more information about Sybase Certification Exams, see http://www.sypron.nl/certtips.html .

The ASE Performance and Tuning Guide contains a lot fo info about this topic. You can download this manual from http://sybooks.sybase.com/as.html .

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2011-03-16 15:52:17.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cursor for Update
References: <4d7fee9f.1308.1681692777@sybase.com>
In-Reply-To: <4d7fee9f.1308.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: <4d80dcb1@forums-1-dub>
Date: 16 Mar 2011 07:52:17 -0800
X-Trace: forums-1-dub 1300290737 10.22.241.152 (16 Mar 2011 07:52:17 -0800)
X-Original-Trace: 16 Mar 2011 07:52:17 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30049
Article PK: 79278


On 3/15/2011 4:56 PM, RGS wrote:
> Hi!
>
> I have a stored procedure with sentences like that:
>
> declare cursor c1 for select .. from table1 for read only
> ....
> update table1
> set c1 = @w1
> where c2 = @w2 -- @w2 is reading from the cursor and c2
> is the key of the table
> .....
> close cursor c1
>
> I know this program is wrong, because the cursor has to be
> declare "for update of c1" and I have to update the record
> with "update of current c1"
>
> The question is, what is the negative effect with the
> original program? The program will be slower? There will be
> more locks?
>
>
> Thanks!

Depending on the specifics of the cursors SELECT statement
and the values involved, you could encounter the "Halloween" problem.
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00743.1502/html/queryprocessing/queryprocessing200.htm


-bret


Bret Halford Posted on 2011-03-16 18:35:56.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cursor for Update
References: <4d7fee9f.1308.1681692777@sybase.com>
In-Reply-To: <4d7fee9f.1308.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: <4d81030c$1@forums-1-dub>
Date: 16 Mar 2011 10:35:56 -0800
X-Trace: forums-1-dub 1300300556 10.22.241.152 (16 Mar 2011 10:35:56 -0800)
X-Original-Trace: 16 Mar 2011 10:35:56 -0800, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30053
Article PK: 79281


On 3/15/2011 4:56 PM, RGS wrote:
> Hi!
>
> I have a stored procedure with sentences like that:
>
> declare cursor c1 for select .. from table1 for read only
> ....
> update table1
> set c1 = @w1
> where c2 = @w2 -- @w2 is reading from the cursor and c2
> is the key of the table
> .....
> close cursor c1
>
> I know this program is wrong, because the cursor has to be
> declare "for update of c1" and I have to update the record
> with "update of current c1"
>
> The question is, what is the negative effect with the
> original program? The program will be slower? There will be
> more locks?
>
>
> Thanks!

Unexpected results may be another issue. Note that the
code above updates the set of all rows where c2 = @w2 rather
than just the currently fetched row of the cursor.


RGS Posted on 2011-03-16 19:40:18.0Z
Sender: 4037.4d810ebb.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Cursor for Update
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d811222.409f.1681692777@sybase.com>
References: <4d81030c$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Mar 2011 11:40:18 -0800
X-Trace: forums-1-dub 1300304418 10.22.241.41 (16 Mar 2011 11:40:18 -0800)
X-Original-Trace: 16 Mar 2011 11:40:18 -0800, 10.22.241.41
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30055
Article PK: 79285

Thanks for your comments

In my program, inside the cursor, we are executing UPDATE on
a field that was used in the declare of the cursor for
criteria of the SELECT statement

Then, I would fall in Halloween bug? In this case, is a
good idea to change a CURSOR FOR UPDATE?

Thanks!

> On 3/15/2011 4:56 PM, RGS wrote:
> > Hi!
> >
> > I have a stored procedure with sentences like that:
> >
> > declare cursor c1 for select .. from table1 for read
> > only ....
> > update table1
> > set c1 = @w1
> > where c2 = @w2 -- @w2 is reading from the cursor
> > and c2 is the key of the table
> > .....
> > close cursor c1
> >
> > I know this program is wrong, because the cursor has to
> > be declare "for update of c1" and I have to update the
> > record with "update of current c1"
> >
> > The question is, what is the negative effect with the
> > original program? The program will be slower? There will
> > be more locks?
> >
> >
> > Thanks!
>
> Unexpected results may be another issue. Note that the
> code above updates the set of all rows where c2 = @w2
> rather than just the currently fetched row of the cursor.
>