SQL Performance Tuning

It's common practice to fetch a row, then UPDATE it with an UPDATE ... WHERE CURRENT OF <cursor> statement, or to fetch a row, then DELETE it with a DELETE ... WHERE CURRENT OF <cursor> statement. Such techniques are unfriendly in multiuser environments. If it's predictable that a data change will happen after a fetch, then:

• You can incorporate the prediction in the WHERE clause so that the selection and data change take place together.

• You can incorporate the prediction in a trigger so that the data change becomes situationally dependent.

In short, you don't want to follow this procedure:

SELECT ... OPEN <cursor> FETCH ... IF <search condition> THEN UPDATE ...

Instead, you want to just do this:

UPDATE ... WHERE <search condition>

To avoid the WHERE CURRENT OF clause and cursor trouble, use a ROWID or serial (auto_increment) column.

The Bottom Line: FETCH and Data Changes

Don't SELECT/fetch/test/<data change> . Do UPDATE...WHERE <condition>/test and DELETE...WHERE <condition>/test .

To avoid WHERE CURRENT OF and cursor trouble, use a ROWID or serial column.