update query

[unknown]

Hi I am doing a simple test,

Updating all rows in tbl1 which has 1 million rows.
1) When no index
Table: tbl1 scan count 1, logical reads: (regular=18001
apf=0 total=18001), physical reads: (regular=8 apf=9028
total=9036), apf IOs used=9028
Total writes for this command: 15543

Execution Time 58.
Adaptive Server cpu time: 5800 ms. Adaptive Server elapsed
time: 32043 ms.
(1000000 rows affected)

<<

2) When 1 index on col1
Table: tbl1 scan count 1, logical reads: (regular=18001
apf=0 total=18001), physical reads: (regular=8 apf=9028
total=9036), apf IOs used=9028
Total writes for this command: 20214

Execution Time 53.
Adaptive Server cpu time: 5300 ms. Adaptive Server elapsed
time: 30400 ms.
(1000000 rows affected)




I see the difference in "Total writes for this command:".
When there is an index and if the index column is being
changed then I can understand there would be additional
writes, but in my case I am not updating the index column,
why there are additional writes when updating non index
columns!

[Sherlock, Kevin [TeamSybase]]

First off, the "Total writes for this command" reports how many pages moved
past the wash marker and were written because of your command. Even a large
SELECT command can cause this as it may move dirty pages past the wash
marker as the MRU chain moves because of cache activity. In other words, I
wouldn't use this to measure total writes to the table you updated. Rather,
you should look at using MDA tables to measure this more precisely.

Secondly, if your update changed the row size of your base table, it's
possible that page splits, row relocations, etc could have happened to your
base table as a result of your update statement, and therefore some index
maintenance had to take place. So much depends on your table and index
definitions, locking schemes, cache utilization, actual SQL statement used
to update, update mode chosen (deferred, in place, etc)....

Post by unknown
Hi I am doing a simple test,
Updating all rows in tbl1 which has 1 million rows.
1) When no index
Table: tbl1 scan count 1, logical reads: (regular=18001
apf=0 total=18001), physical reads: (regular=8 apf=9028
total=9036), apf IOs used=9028
Total writes for this command: 15543
Execution Time 58.
Adaptive Server cpu time: 5800 ms. Adaptive Server elapsed
time: 32043 ms.
(1000000 rows affected)
<<
2) When 1 index on col1
Table: tbl1 scan count 1, logical reads: (regular=18001
apf=0 total=18001), physical reads: (regular=8 apf=9028
total=9036), apf IOs used=9028
Total writes for this command: 20214
Execution Time 53.
Adaptive Server cpu time: 5300 ms. Adaptive Server elapsed
time: 30400 ms.
(1000000 rows affected)
I see the difference in "Total writes for this command:".
When there is an index and if the index column is being
changed then I can understand there would be additional
writes, but in my case I am not updating the index column,
why there are additional writes when updating non index
columns!

[unknown]

Post by Sherlock, Kevin [TeamSybase]
First off, the "Total writes for this command" reports how
many pages moved past the wash marker and were written
because of your command. Even a large SELECT command can
cause this as it may move dirty pages past the wash
marker as the MRU chain moves because of cache activity.
In other words, I wouldn't use this to measure total
writes to the table you updated. Rather, you should look
at using MDA tables to measure this more precisely.

I had checked the manual which says:

Total writes for this command:

This is the total number of writes Adaptive Server did for
the query. This count includes inserts, updates and deletes
on user tables, temporary tables and work tables. Even
queries that do not include data manipulation statements may
require writes to work tables or temporary tables, which are
counted here.

Post by Sherlock, Kevin [TeamSybase]
Secondly, if your update changed the row size of your base
table, it's possible that page splits, row relocations,
etc could have happened to your base table as a result of
your update statement, and therefore some index
maintenance had to take place. So much depends on your
table and index definitions, locking schemes, cache
utilization, actual SQL statement used to update, update
mode chosen (deferred, in place, etc)....

The data is same i.e. i did the test by
- creating table
- inserting 1 m rows
- shutdown server
- wait 5 min (any internal os activity to finish... )
- start server
- wait 5 min (any post start internal ase activity to
finish..)
- run update

Repeat the above by by dropping and creating table with non
clustered non unique index. Results should have been close,
even the second test gave similar high # for writes when tbl
had index.

Post by Sherlock, Kevin [TeamSybase]
doing a simple test, >

Post by unknown
Updating all rows in tbl1 which has 1 million rows.
1) When no index
Table: tbl1 scan count 1, logical reads: (regular=18001
apf=0 total=18001), physical reads: (regular=8 apf=9028
total=9036), apf IOs used=9028
Total writes for this command: 15543
Execution Time 58.
Adaptive Server cpu time: 5800 ms. Adaptive Server
elapsed time: 32043 ms.
(1000000 rows affected)
<<
2) When 1 index on col1
Table: tbl1 scan count 1, logical reads: (regular=18001
apf=0 total=18001), physical reads: (regular=8 apf=9028
total=9036), apf IOs used=9028
Total writes for this command: 20214
Execution Time 53.
Adaptive Server cpu time: 5300 ms. Adaptive Server
elapsed time: 30400 ms.
(1000000 rows affected)
I see the difference in "Total writes for this
command:". When there is an index and if the index
column is being changed then I can understand there
would be additional writes, but in my case I am not
updating the index column, why there are additional
writes when updating non index columns!