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.

Newbie: cursor for update

6 posts in General Discussion Last posting was on 2009-09-21 19:01:12.0Z
Jose Luis Posted on 2009-09-18 11:57:43.0Z
From: Jose Luis <jose.luis.fdez.diaz@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Newbie: cursor for update
Date: Fri, 18 Sep 2009 04:57:43 -0700 (PDT)
Organization: http://groups.google.com
Lines: 98
Message-ID: <42c617a5-138d-46cc-8642-4e4c24cf7c4d@j9g2000vbp.googlegroups.com>
NNTP-Posting-Host: 213.170.46.90
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1253275064 15818 127.0.0.1 (18 Sep 2009 11:57:44 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 18 Sep 2009 11:57:44 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: j9g2000vbp.googlegroups.com; posting-host=213.170.46.90; posting-account=1HfkcQoAAAC3iXf8_jGLZLQ9yRZZ5bhF
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!j9g2000vbp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28323
Article PK: 77567

Hi,

Given the table below:

<<begin snip>>

2> select * from FOO
3> go
1 1
2 1
3 0
4 0
5 0

(5 rows affected)
1> sp_help FOO
2> go
FOO guest user
table

(1 row affected)
default Sep 18 2009 12:56PM
col1 int 4 NULL NULL 0
NULL NULL 0
col2 int 4 NULL NULL 0
NULL NULL 0
idx1 nonclustered, unique located on
default
col1
0 0 0

(1 row affected)
No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages
lock scheme.

1 0 0 0

(1 row affected)
(return status = 0)

<<end snip>>



And given the program below:


<<begin snip>>

declare c1 cursor for select * from FOO where col1 > 2 for update
go

declare @col1 int, @col2 int

open c1
fetch c1 into @col1, @col2

while (@@sqlstatus = 0)
begin

update FOO set col2=0 where current of c1

fetch c1 into @col1, @col2
end

go
<<end snip>>



Is this program equivalent to?

<<begin snip>>
update FOO set col2=0 where col1=3
go
update FOO set col2=0 where col1=4
go
update FOO set col2=0 where col1=5
go
<<end snip>>


I mean, every update statement in the loop is a transaction?

BTW: What is the difference between clustered and nonclustered
indexes?


Thanks in advance,
Jose Luis


J Posted on 2009-09-18 16:52:37.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Newbie: cursor for update
Reply-To: J@bogusemailAddress.com
Message-ID: <4ab3b877.6190531@forums.sybase.com>
References: <42c617a5-138d-46cc-8642-4e4c24cf7c4d@j9g2000vbp.googlegroups.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Sep 2009 09:52:37 -0700
X-Trace: forums-1-dub 1253292757 10.22.241.152 (18 Sep 2009 09:52:37 -0700)
X-Original-Trace: 18 Sep 2009 09:52:37 -0700, vip152.sybase.com
Lines: 129
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28329
Article PK: 77572

On Fri, 18 Sep 2009 04:57:43 -0700 (PDT), Jose Luis
<jose.luis.fdez.diaz@gmail.com> wrote:

I would say no as I understand it. But this could well be good enough
for your purposes. I like to image that instead of the updates being
almost immediate in the non-cursor case, what if there was a very long
sleep between the updates (could be other application blocking).

With the cursor example you are taking Sh-intent locks on the > 2 rows
and holding them from the time the cursor is open until it is closed
(with exclusive and update locks on pages and rows after the updates).

As you have shown the non-cursor example I understand you to say that
each update is standalone.

I think to get something much closer non-cursor you would be :

begin tran
select * from FOO holdlock where col1 > 2
update...
update ...
update ...
commit

I could guess that you could insert "waitfor delay ... " in both
scenarios and experiment with updates/selects or look at the locks at
the various points.

J

>Hi,
>
>Given the table below:
>
><<begin snip>>
>
>2> select * from FOO
>3> go
> 1 1
> 2 1
> 3 0
> 4 0
> 5 0
>
>(5 rows affected)
>1> sp_help FOO
>2> go
> FOO guest user
>table
>
>(1 row affected)
> default Sep 18 2009 12:56PM
> col1 int 4 NULL NULL 0
>NULL NULL 0
> col2 int 4 NULL NULL 0
>NULL NULL 0
> idx1 nonclustered, unique located on
>default
>col1
>0 0 0
>
>(1 row affected)
>No defined keys for this object.
>Object is not partitioned.
>Lock scheme Allpages
>The attribute 'exp_row_size' is not applicable to tables with allpages
>lock scheme.
>
> 1 0 0 0
>
>(1 row affected)
>(return status = 0)
>
><<end snip>>
>
>
>
>And given the program below:
>
>
><<begin snip>>
>
>declare c1 cursor for select * from FOO where col1 > 2 for update
>go
>
>declare @col1 int, @col2 int
>
>open c1
>fetch c1 into @col1, @col2
>
>while (@@sqlstatus = 0)
>begin
>
> update FOO set col2=0 where current of c1
>
> fetch c1 into @col1, @col2
>end
>
>go
><<end snip>>
>
>
>
>Is this program equivalent to?
>
><<begin snip>>
>update FOO set col2=0 where col1=3
>go
>update FOO set col2=0 where col1=4
>go
>update FOO set col2=0 where col1=5
>go
><<end snip>>
>
>
>I mean, every update statement in the loop is a transaction?
>
>BTW: What is the difference between clustered and nonclustered
>indexes?
>
>
>Thanks in advance,
>Jose Luis
>
>
>
>
>


Jose Luis Posted on 2009-09-21 07:40:56.0Z
From: Jose Luis <jose.luis.fdez.diaz@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Newbie: cursor for update
Date: Mon, 21 Sep 2009 00:40:56 -0700 (PDT)
Organization: http://groups.google.com
Lines: 165
Message-ID: <d1bd0e2a-332f-4cd7-8dc1-1b092817f748@d23g2000vbm.googlegroups.com>
References: <42c617a5-138d-46cc-8642-4e4c24cf7c4d@j9g2000vbp.googlegroups.com> <4ab3b877.6190531@forums.sybase.com>
NNTP-Posting-Host: 213.170.46.90
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1253518856 23835 127.0.0.1 (21 Sep 2009 07:40:56 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 21 Sep 2009 07:40:56 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: d23g2000vbm.googlegroups.com; posting-host=213.170.46.90; posting-account=1HfkcQoAAAC3iXf8_jGLZLQ9yRZZ5bhF
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!d23g2000vbm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28335
Article PK: 77579


On Sep 18, 6:52 pm, jtotally_bo...@sbcglobal.net (J) wrote:
> On Fri, 18 Sep 2009 04:57:43 -0700 (PDT), Jose Luis
>
> <jose.luis.fdez.d...@gmail.com> wrote:
>
> I would say no as I understand it.  But this could well be good enough
> for your purposes.  I like to image that instead of the updates being
> almost immediate in the non-cursor case, what if there was a very long
> sleep between the updates (could be other application blocking).
>
> With the cursor example you are taking Sh-intent locks on the > 2 rows
> and holding them from the time the cursor is open until it is closed
> (with exclusive and update locks on pages and rows after the updates).
>
> As you have shown the non-cursor example I understand you to say that
> each update is standalone.
>
> I think to get something much closer non-cursor you would be :
>
> begin tran
> select * from FOO holdlock where col1 > 2
> update...
> update ...
> update ...
> commit
>
> I could guess that you could insert "waitfor delay ... " in both
> scenarios and experiment with updates/selects or look at the locks at
> the various points.
>
> J
>
> >Hi,
>
> >Given the table below:
>
> ><<begin snip>>
>
> >2> select * from  FOO
> >3> go
> >           1           1
> >           2           1
> >           3           0
> >           4           0
> >           5           0
>
> >(5 rows affected)
> >1> sp_help FOO
> >2> go
> > FOO                            guest                          user
> >table
>
> >(1 row affected)
> > default                               Sep 18 2009 12:56PM
> > col1            int                  4 NULL  NULL     0
> >NULL            NULL                   0
> > col2            int                  4 NULL  NULL     0
> >NULL            NULL                   0
> > idx1                 nonclustered, unique located on
> >default
> >col1
> >0                0                    0
>
> >(1 row affected)
> >No defined keys for this object.
> >Object is not partitioned.
> >Lock scheme Allpages
> >The attribute 'exp_row_size' is not applicable to tables with allpages
> >lock scheme.
>
> >            1              0          0                 0
>
> >(1 row affected)
> >(return status = 0)
>
> ><<end snip>>
>
> >And given the program below:
>
> ><<begin snip>>
>
> >declare c1 cursor for select * from FOO where col1 > 2 for update
> >go
>
> >declare @col1 int, @col2 int
>
> >open c1
> >fetch c1 into @col1, @col2
>
> >while (@@sqlstatus = 0)
> >begin
>
> >   update FOO set col2=0 where current of c1
>
> >   fetch c1 into @col1, @col2
> >end
>
> >go
> ><<end snip>>
>
> >Is this program equivalent to?
>
> ><<begin snip>>
> >update FOO set col2=0 where col1=3
> >go
> >update FOO set col2=0 where col1=4
> >go
> >update FOO set col2=0 where col1=5
> >go
> ><<end snip>>
>
> >I mean, every update statement in the loop is a transaction?
>
> >BTW: What is the difference between clustered and nonclustered
> >indexes?
>
> >Thanks in advance,
> >Jose Luis

Thanks for your help.

Is there a big difference in running time between cursor and no-cursor
examples? Is sh-lock row-level or field-level?


how many times is the FOO update trigger run with this sentence?

update tempdb..FOO set col2=0 where col1>2 and col2=1



Are the code below equivalent to the no-cursor update sentences?


<<end begin>>
declare @rows int, @err int

set rowcount 1

while (1=1)
begin
update FOO set col2=0 where col1>2 and col2=1

select @rows=@@rowcount, @err=@@error
if (@rows != 1 and @err=0)
break

end
go

<<end snip>>




Regards,
Jose Luis


Jeff Tallman [Sybase] Posted on 2009-09-21 13:15:17.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Newbie: cursor for update
References: <42c617a5-138d-46cc-8642-4e4c24cf7c4d@j9g2000vbp.googlegroups.com>
In-Reply-To: <42c617a5-138d-46cc-8642-4e4c24cf7c4d@j9g2000vbp.googlegroups.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: <4ab77c65$1@forums-1-dub>
Date: 21 Sep 2009 06:15:17 -0700
X-Trace: forums-1-dub 1253538917 10.22.241.152 (21 Sep 2009 06:15:17 -0700)
X-Original-Trace: 21 Sep 2009 06:15:17 -0700, vip152.sybase.com
Lines: 144
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28336
Article PK: 77582

As to the question on transactions, from an ASE default behavior, yes,
each of the updates would be a separate transaction...unless the code is
called from the context of an explicit transaction or someone running
in ISO 3. You also need to be a bit careful of updateable cursors as
the ANSI behavior specifications for the cursor used to be a bit weird
(for example, I *think* one of the behaviors was that it did a close on
the cursor if an explicit transaction was committed inside the cursor).
This was a big problem in one of the earlier releases of ASE and if I
remember correctly we did something specific to deal with it. However,
you might want to read up on it in the docs (try Transact SQL Guide first).

The main difference between a clustered and nonclustered index is that a
clustered index is considered a "placement" index - which means that
data is physically arranged in the table based on the index key. For an
APL table, essentially, the table will be in index key sorted order
while for DOL (datapages and datarows) every attempt is made to maintain
the sorted order - but when not possible without escalating the locks,
the row is simply located "near" where it should be - so not quite
sorted....keep in mind that even if the data is maintained in sorted
order, there is no guarantee that querying the table without an order by
clause will give you the data in sorted order. For example, parallel
queries, use of in-memory hashing, etc. can return data in any order.
So, if the data has to be in a particular order always use an 'order by'
clause.

Another difference is that for APL tables only, the clustered index leaf
pages are the datapages. This fact has often been exploited by some
trying to reduce IOs, but I have seen it misfire just as often as the
row width can negatively impact this strategy - and the result is often
you will see a nonclustered index with nearly the same keys created just
for index covering on a query.


Is this for a batch process (i.e. an archive or other nightly process
that updates rows) that you want to avoid having it cause contention
with other concurrent users???



Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Jose Luis wrote:
> Hi,
>
> Given the table below:
>
> <<begin snip>>
>
> 2> select * from FOO
> 3> go
> 1 1
> 2 1
> 3 0
> 4 0
> 5 0
>
> (5 rows affected)
> 1> sp_help FOO
> 2> go
> FOO guest user
> table
>
> (1 row affected)
> default Sep 18 2009 12:56PM
> col1 int 4 NULL NULL 0
> NULL NULL 0
> col2 int 4 NULL NULL 0
> NULL NULL 0
> idx1 nonclustered, unique located on
> default
> col1
> 0 0 0
>
> (1 row affected)
> No defined keys for this object.
> Object is not partitioned.
> Lock scheme Allpages
> The attribute 'exp_row_size' is not applicable to tables with allpages
> lock scheme.
>
> 1 0 0 0
>
> (1 row affected)
> (return status = 0)
>
> <<end snip>>
>
>
>
> And given the program below:
>
>
> <<begin snip>>
>
> declare c1 cursor for select * from FOO where col1 > 2 for update
> go
>
> declare @col1 int, @col2 int
>
> open c1
> fetch c1 into @col1, @col2
>
> while (@@sqlstatus = 0)
> begin
>
> update FOO set col2=0 where current of c1
>
> fetch c1 into @col1, @col2
> end
>
> go
> <<end snip>>
>
>
>
> Is this program equivalent to?
>
> <<begin snip>>
> update FOO set col2=0 where col1=3
> go
> update FOO set col2=0 where col1=4
> go
> update FOO set col2=0 where col1=5
> go
> <<end snip>>
>
>
> I mean, every update statement in the loop is a transaction?
>
> BTW: What is the difference between clustered and nonclustered
> indexes?
>
>
> Thanks in advance,
> Jose Luis
>
>
>
>
>


Jose Luis Posted on 2009-09-21 15:45:52.0Z
From: Jose Luis <jose.luis.fdez.diaz@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Newbie: cursor for update
Date: Mon, 21 Sep 2009 08:45:52 -0700 (PDT)
Organization: http://groups.google.com
Lines: 159
Message-ID: <510f1e40-19e9-4f78-9b6f-68fb19904171@f10g2000vbf.googlegroups.com>
References: <42c617a5-138d-46cc-8642-4e4c24cf7c4d@j9g2000vbp.googlegroups.com> <4ab77c65$1@forums-1-dub>
NNTP-Posting-Host: 213.170.46.90
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1253547953 28509 127.0.0.1 (21 Sep 2009 15:45:53 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 21 Sep 2009 15:45:53 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: f10g2000vbf.googlegroups.com; posting-host=213.170.46.90; posting-account=1HfkcQoAAAC3iXf8_jGLZLQ9yRZZ5bhF
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!transit4.readnews.com!postnews.google.com!f10g2000vbf.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28337
Article PK: 77581

On Sep 21, 3:15 pm, "Jeff Tallman [Sybase]" <jeff.tall...@sybase.com>

wrote:
> As to the question on transactions, from an ASE default behavior, yes,
> each of the updates would be a separate transaction...unless the code is
>   called from the context of an explicit transaction or someone running
> in ISO 3.  You also need to be a bit careful of updateable cursors as
> the ANSI behavior specifications for the cursor used to be a bit weird
> (for example, I *think* one of the behaviors was that it did a close on
> the cursor if an explicit transaction was committed inside the cursor).
>    This was a big problem in one of the earlier releases of ASE and if I
> remember correctly we did something specific to deal with it.  However,
> you might want to read up on it in the docs (try Transact SQL Guide first).
>
> The main difference between a clustered and nonclustered index is that a
> clustered index is considered a "placement" index - which means that
> data is physically arranged in the table based on the index key.  For an
> APL table, essentially, the table will be in index key sorted order
> while for DOL (datapages and datarows) every attempt is made to maintain
> the sorted order - but when not possible without escalating the locks,
> the row is simply located "near" where it should be - so not quite
> sorted....keep in mind that even if the data is maintained in sorted
> order, there is no guarantee that querying the table without an order by
> clause will give you the data in sorted order.   For example, parallel
> queries, use of in-memory hashing, etc. can return data in any order.
> So, if the data has to be in a particular order always use an 'order by'
> clause.
>
> Another difference is that for APL tables only, the clustered index leaf
> pages are the datapages.  This fact has often been exploited by some
> trying to reduce IOs, but I have seen it misfire just as often as the
> row width can negatively impact this strategy - and the result is often
> you will see a nonclustered index with nearly the same keys created just
> for index covering on a query.
>
> Is this for a batch process (i.e. an archive or other nightly process
> that updates rows) that you want to avoid having it cause contention
> with other concurrent users???
>
> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tall...@sybase.comhttp://blogs.sybase.com/database
>
> Jose Luis wrote:
> > Hi,
>
> > Given the table below:
>
> > <<begin snip>>
>
> > 2> select * from  FOO
> > 3> go
> >            1           1
> >            2           1
> >            3           0
> >            4           0
> >            5           0
>
> > (5 rows affected)
> > 1> sp_help FOO
> > 2> go
> >  FOO                            guest                          user
> > table
>
> > (1 row affected)
> >  default                               Sep 18 2009 12:56PM
> >  col1            int                  4 NULL  NULL     0
> > NULL            NULL                   0
> >  col2            int                  4 NULL  NULL     0
> > NULL            NULL                   0
> >  idx1                 nonclustered, unique located on
> > default
> > col1
> > 0                0                    0
>
> > (1 row affected)
> > No defined keys for this object.
> > Object is not partitioned.
> > Lock scheme Allpages
> > The attribute 'exp_row_size' is not applicable to tables with allpages
> > lock scheme.
>
> >             1              0          0                 0
>
> > (1 row affected)
> > (return status = 0)
>
> > <<end snip>>
>
> > And given the program below:
>
> > <<begin snip>>
>
> > declare c1 cursor for select * from FOO where col1 > 2 for update
> > go
>
> > declare @col1 int, @col2 int
>
> > open c1
> > fetch c1 into @col1, @col2
>
> > while (@@sqlstatus = 0)
> > begin
>
> >    update FOO set col2=0 where current of c1
>
> >    fetch c1 into @col1, @col2
> > end
>
> > go
> > <<end snip>>
>
> > Is this program equivalent to?
>
> > <<begin snip>>
> > update FOO set col2=0 where col1=3
> > go
> > update FOO set col2=0 where col1=4
> > go
> > update FOO set col2=0 where col1=5
> > go
> > <<end snip>>
>
> > I mean, every update statement in the loop is a transaction?
>
> > BTW: What is the difference between clustered and nonclustered
> > indexes?
>
> > Thanks in advance,
> > Jose Luis

Is this for a batch process (i.e. an archive or other nightly process
that updates rows) that you want to avoid having it cause contention
with other concurrent users???


No. That is the point. This is for a batch process that will run
alone, so no locks are needed. I guess that a cursor for update is a
waste of time a resources, isn't it?


Thanks for your help.

Regards,
Jose Luis


Jeff Tallman [Sybase] Posted on 2009-09-21 19:01:12.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Newbie: cursor for update
References: <42c617a5-138d-46cc-8642-4e4c24cf7c4d@j9g2000vbp.googlegroups.com> <4ab77c65$1@forums-1-dub> <510f1e40-19e9-4f78-9b6f-68fb19904171@f10g2000vbf.googlegroups.com>
In-Reply-To: <510f1e40-19e9-4f78-9b6f-68fb19904171@f10g2000vbf.googlegroups.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: <4ab7cd78$1@forums-1-dub>
Date: 21 Sep 2009 12:01:12 -0700
X-Trace: forums-1-dub 1253559672 10.22.241.152 (21 Sep 2009 12:01:12 -0700)
X-Original-Trace: 21 Sep 2009 12:01:12 -0700, vip152.sybase.com
Lines: 179
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28338
Article PK: 77580

In that case yes, a cursor is a complete waste. However, an alternative
strategy to consider:


begin tran
lock table <tablename> in exclusive mode
<set based update statement>
<..additional set based update statements as necessary..>
commit tran

This could improve performance - especially on DOL tables - where the
constant lock chain search/acquisition is done up front in a single grab
(since process runs as single user). However, depending on the number
of rows modified, you still may need to be careful of tran log space.

Keep in mind that a faster alternative may be:

select * into newtablename
from oldtable where....(condition)
union all
select * from oldtable where (!condition)

create index on newtablename

sp_rename oldtable, table_old
sp_rename newtablename, oldtable

...eventually....

drop table table_old

This may actually be faster - especially if the update is impacting the
primary key (yours did not seem to be doing so) - but may require
sp_recompile on all procs/triggers (so a downside)....

Best thing to do is evaluate what the update is doing - if updating an
unindexed column, the best approach likely is the first one if running
as a single user. If updating an indexed column, dropping the index
first may be more effective if updating a substantial percentage of the
rows ("substantial" depends a lot on device speed, etc. - but I would
say anything north of 10% is time to possibly think about it and above
40%, I would likely recommend dropping the index). If the update is
affecting the primary key, I prefer the latter as I have a backout
strategy if something goes wrong with a different part of the batch process.

Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Jose Luis wrote:
> On Sep 21, 3:15 pm, "Jeff Tallman [Sybase]" <jeff.tall...@sybase.com>
> wrote:
>> As to the question on transactions, from an ASE default behavior, yes,
>> each of the updates would be a separate transaction...unless the code is
>> called from the context of an explicit transaction or someone running
>> in ISO 3. You also need to be a bit careful of updateable cursors as
>> the ANSI behavior specifications for the cursor used to be a bit weird
>> (for example, I *think* one of the behaviors was that it did a close on
>> the cursor if an explicit transaction was committed inside the cursor).
>> This was a big problem in one of the earlier releases of ASE and if I
>> remember correctly we did something specific to deal with it. However,
>> you might want to read up on it in the docs (try Transact SQL Guide first).
>>
>> The main difference between a clustered and nonclustered index is that a
>> clustered index is considered a "placement" index - which means that
>> data is physically arranged in the table based on the index key. For an
>> APL table, essentially, the table will be in index key sorted order
>> while for DOL (datapages and datarows) every attempt is made to maintain
>> the sorted order - but when not possible without escalating the locks,
>> the row is simply located "near" where it should be - so not quite
>> sorted....keep in mind that even if the data is maintained in sorted
>> order, there is no guarantee that querying the table without an order by
>> clause will give you the data in sorted order. For example, parallel
>> queries, use of in-memory hashing, etc. can return data in any order.
>> So, if the data has to be in a particular order always use an 'order by'
>> clause.
>>
>> Another difference is that for APL tables only, the clustered index leaf
>> pages are the datapages. This fact has often been exploited by some
>> trying to reduce IOs, but I have seen it misfire just as often as the
>> row width can negatively impact this strategy - and the result is often
>> you will see a nonclustered index with nearly the same keys created just
>> for index covering on a query.
>>
>> Is this for a batch process (i.e. an archive or other nightly process
>> that updates rows) that you want to avoid having it cause contention
>> with other concurrent users???
>>
>> Jeff Tallman
>> Enterprise Data Management Products Technical Evangelism
>> jeff.tall...@sybase.comhttp://blogs.sybase.com/database
>>
>> Jose Luis wrote:
>>> Hi,
>>> Given the table below:
>>> <<begin snip>>
>>> 2> select * from FOO
>>> 3> go
>>> 1 1
>>> 2 1
>>> 3 0
>>> 4 0
>>> 5 0
>>> (5 rows affected)
>>> 1> sp_help FOO
>>> 2> go
>>> FOO guest user
>>> table
>>> (1 row affected)
>>> default Sep 18 2009 12:56PM
>>> col1 int 4 NULL NULL 0
>>> NULL NULL 0
>>> col2 int 4 NULL NULL 0
>>> NULL NULL 0
>>> idx1 nonclustered, unique located on
>>> default
>>> col1
>>> 0 0 0
>>> (1 row affected)
>>> No defined keys for this object.
>>> Object is not partitioned.
>>> Lock scheme Allpages
>>> The attribute 'exp_row_size' is not applicable to tables with allpages
>>> lock scheme.
>>> 1 0 0 0
>>> (1 row affected)
>>> (return status = 0)
>>> <<end snip>>
>>> And given the program below:
>>> <<begin snip>>
>>> declare c1 cursor for select * from FOO where col1 > 2 for update
>>> go
>>> declare @col1 int, @col2 int
>>> open c1
>>> fetch c1 into @col1, @col2
>>> while (@@sqlstatus = 0)
>>> begin
>>> update FOO set col2=0 where current of c1
>>> fetch c1 into @col1, @col2
>>> end
>>> go
>>> <<end snip>>
>>> Is this program equivalent to?
>>> <<begin snip>>
>>> update FOO set col2=0 where col1=3
>>> go
>>> update FOO set col2=0 where col1=4
>>> go
>>> update FOO set col2=0 where col1=5
>>> go
>>> <<end snip>>
>>> I mean, every update statement in the loop is a transaction?
>>> BTW: What is the difference between clustered and nonclustered
>>> indexes?
>>> Thanks in advance,
>>> Jose Luis
>
>
>
> Is this for a batch process (i.e. an archive or other nightly process
> that updates rows) that you want to avoid having it cause contention
> with other concurrent users???
>
>
> No. That is the point. This is for a batch process that will run
> alone, so no locks are needed. I guess that a cursor for update is a
> waste of time a resources, isn't it?
>
>
> Thanks for your help.
>
> Regards,
> Jose Luis
>
>
>
>