Pros/Cons Row Update Strategy 
Author Message
 Pros/Cons Row Update Strategy

What are the advantages, if any, of updating a single table row by
deleting and then re-inserting the row rather than performing a single
update SQL command? And if possible, could you please comment on the
considerations, if any, one should give to the delete/insert strategy
depending on whether the table is a heap table or clustered table, and
whether the locking strategy is "data rows only" or "all pages".

I have been reading through the ASE doc on these issues, but I am not
sure I am catching all of the subtleties.

--
Thanks,
G.Carter


http://www.***.com/ ~greg.carter



Wed, 23 Apr 2003 03:00:00 GMT
 Pros/Cons Row Update Strategy

**** Post for FREE via your newsreader at post.usenet.com ****

Quote:

> What are the advantages, if any, of updating a single table row by
> deleting and then re-inserting the row rather than performing a single
> update SQL command?

        There are no advantages. What you are describing is how a deferred
        update works. These have more overhead than in place updates.

Quote:
> And if possible, could you please comment on the
> considerations, if any, one should give to the delete/insert strategy
> depending on whether the table is a heap table or clustered table, and
> whether the locking strategy is "data rows only" or "all pages".

        All inserts into a help table go into the last data page (which may
        result in "hot spot" contention). A deferred update into a table with
        a clustered index would insert into the same page if the key doesn't
        change. The only bearing that locking has on this is the type of
        lock used.

-am

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
                      http://www.usenet.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=



Fri, 25 Apr 2003 08:00:00 GMT
 Pros/Cons Row Update Strategy
Just a follow-up:
For a clustered table under an "all pages" lock mode, if an update is
performed by delete followed by insert on the same table with the same key
within the same transaction, is ASE smart enough to know not to move all
the rows on the page following the deleted row up only to have to move
them back down again when the row is inserted?

Thanks,
G.Carter

Quote:

> What are the advantages, if any, of updating a single table row by
> deleting and then re-inserting the row rather than performing a single
> update SQL command? And if possible, could you please comment on the
> considerations, if any, one should give to the delete/insert strategy
> depending on whether the table is a heap table or clustered table, and
> whether the locking strategy is "data rows only" or "all pages".

> I have been reading through the ASE doc on these issues, but I am not
> sure I am catching all of the subtleties.

> --
> Thanks,
> G.Carter


> http://www.webaccess.net/~greg.carter



Sat, 17 May 2003 03:00:00 GMT
 Pros/Cons Row Update Strategy
**** Post for FREE via your newsreader at post.usenet.com ****

Quote:

> For a clustered table under an "all pages" lock mode, if an update is
> performed by delete followed by insert on the same table with the same key
> within the same transaction, is ASE smart enough to know not to move all
> the rows on the page following the deleted row up only to have to move
> them back down again when the row is inserted?

        It should be. If its a deferred update that its doing, then it
        should know its doing a delete followed by an insert within the
        same transaction. So it should know its going to fill the same
        row slot if the key hasn't changed (I'm notsure about overflow
        pages but the same logic should apply).

-am

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
                      http://www.usenet.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=



Tue, 20 May 2003 14:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. pros & cons of sql-server

2. SQL7 Automatically Grow, Pros&Cons?????

3. Access Database going to SQL Pros/Cons ??

4. Connection Object in Global ASA, pros and cons

5. Multiple Hierarchy - Pros and Cons ?

6. Microsoft Data Analyzer - Pro's, Con's, when to use, pcumming

7. Storing Huge Data Its Pros & Cons

8. pro's and con's of the nested subsets model for hierarchies

9. Pros / cons with stored procedures

10. pro's and con's of extended stored procedures with mssql7

11. pro's and con's of using cursors

12. Q: Pro and con of using global temp table


 
Powered by phpBB® Forum Software