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]