Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


34 - Interrupt Processing With SET ROWCOUNT

The Transact SQL SET ROWCOUNT statement limits the number of rows affected by set oriented UPDATE and DELETE statements. You can use this feature to interrupt long running updates to execute COMMIT statements at regular intervals. This works as long as interrupted statement will skip previously affected rows when it is re-executed after a commit. The code in Figure 34 shows just such a statement: "Change a column from one specific value to another."

Figure 34 - Interrupt UPDATE With SET ROWCOUNT

BEGIN

DECLARE update_count INTEGER;

DECLARE not_done INTEGER;

SET ROWCOUNT 100;

IF SQLCODE <> 0 THEN

PRINT String ( 'Error - SET ROWCOUNT 100 SQLCODE = ', SQLCODE );

END IF;

SET not_done = 1;

WHILE not_done = 1 LOOP

UPDATE large1

SET code1 = 15

WHERE code1 = 5;

IF ( SQLCODE <> 0 )

AND ( SQLCODE <> 100 ) THEN

PRINT String ( 'Error - UPDATE SQLCODE = ', SQLCODE );

END IF;

COMMIT;

IF SQLCODE <> 0 THEN

PRINT String ( 'Error - COMMIT SQLCODE = ', SQLCODE );

END IF;

SELECT @@ROWCOUNT INTO update_count;

IF update_count = 0 THEN

SET not_done = 0;

END IF;

PRINT String ( 'Updated ', update_count, ' rows' );

END LOOP;

SET ROWCOUNT 0;

IF SQLCODE <> 0 THEN

PRINT String ( 'Error - SET ROWCOUNT 0 SQLCODE = ', SQLCODE );

END IF;

END

More complex problems can be solved by the addition of a "last updated datetime" column which is changed with each update and is tested as part of the WHERE clause to make sure recently updated rows are skipped.


[Home] [Table of Contents] [Previous Section] [Next Section]