Section 2.7. Set Processing in SQL

2.7. Set Processing in SQL

SQL processes data in complete sets. For most update or delete operations against a database and assuming one is not operating against the entire table contentsone has to define precisely the set of rows in that table that will be affected by the process. This defines the granularity of the impending process, which may be described as coarse if a large number of rows will be affected or as fine if only few rows will be involved.

Any attempt to process a large amount of data in small chunks is usually a very bad idea and can be massively inefficient. This approach can be defended only where very extensive changes will be made to the database which can, first, consume an enormous amount of space for storing prior values in case of a transaction rollback, and second, take a very long time to rollback if any attempted change should fail. Many people would argue that where very considerable changes are to be made, regular commit statements should be scattered throughout the data manipulation language (DML) code. However, regular commit statements may not help when resuming a file upload that has failed. From a strictly practical standpoint, it is often much easier, simpler, and faster to resume a process from the start rather than try to locate where and when the failure occurred and then to skip over what has already been committed.

Concerning the size of the log required to rollback transactions in case of failure, it can also be argued that the physical database layout has to accommodate processes, and not that processes have to make do with a given physical implementation. If the amount of undo storage that is required is really enormous, perhaps the question should be raised as to the frequency with which changes are applied. It may be that switching from massive monthly updates to not-so-massive weekly ones or even smaller daily ones may provide an effective solution.

Thousands of statements in a cursor loop for endless batch processing, multiple statements applied to the same data for users doomed to wait, one swoop statement to outperform them all.