FETCH and Data Changes

   

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.

   


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net