Section 2.11. Doing Only What Is Required

2.11. Doing Only What Is Required

Developers often use count(*) for no purpose other than to implement an existence test. This usually happens as a result of a specification such as:

     If there are rows meeting a certain condition     Then do something to them

which immediately becomes:

     select count(*)     into counter     from table_name     where <certain_condition>     if (counter > 0) then

Of course in 90% of the cases the count(*) is totally unnecessary and superfluous, as in the above example. If an action is required to operate on a number of rows, just do it. If no row is affected, so what? No harm is done. Moreover, if the process to be applied to those hypothetical rows is complex, the very first operation will tell you how many of them were affected, either in a system variable (@@ROWCOUNT with Transact-SQL, SQL%ROWCOUNT with PL/SQL, and so forth), in a special field of the SQL Communication Area (SQLCA) when using embedded SQL, or through special APIs such as mysql_affected_rows( ) in PHP. The number of processed rows is also sometimes directly returned by the function, which interacts with the database, such as the JDBC executeUpdate( ) method. Counting rows very often achieves nothing other than doubling your total search effort, because it applies a process twice to the same data.

Further, do not forget that if your purpose is to update or insert rows (a frequent case when rows are counted first to check whether the key already exists), some database systems provide dedicated statements (for instance, Oracle 9i Database's MERGE statement) that operate far more efficiently than you can ever achieve by executing redundant counts.

There is no need to code explicitly what the database performs implicitly.