Why Not to Use COUNT(*) in Production Top Reasons

Many queries in programs execute SQL Select Count(*) statements just to see if any rows exist for particular data.

COUNT(*)

Many times it doesn’t matter whether there is one row or one million rows; it just matters if any rows exist. When this is the case, using Select Count(*) is the most expensive way to check because it will count up all rows.

You May Also Like: Complex SQL Queries for project and interviews

Queries should be coded so that they ensure stopping after getting a hit on the first row (unless of course they actually need the total number of rows).

Writing SQL query

Bad practice

MOVE ZEROS TO HOST-VARIABLE1.
EXEC SQL
 SELECT COUNT(*)
 INTO :HOST-VARIABLE1
 FROM TABLE1
 WHERE COLUMN1 = :HOST-VARIABLE-X
 AND COLUMN2 = :HOST-VARIABLE-Y
END-EXEC

IF HOST-VARIABLE1 > ZERO
SET ROWS-FOUND TO TRUE
END-IF

Good Practice

MOVE ZEROS TO HOST-VARIABLE1
SELECT 1
INTO :HOST-VARIABLE1
FROM TABLE1
WHERE COLUMN1 = :HOST-VARIABLE-X
 AND COLUMN2 = :HOST-VARIABLE-Y
FETCH FIRST 1 ROW ONLY
EVALUATE SQLCODE …‥

IF SQLCODE = 0
 SET ROWS-FOUND TO TRUE
END-IF

Reference

• Click for SQL tuning refresher guide

Related