Many queries in programs execute SQL Select Count(*) statements just to see if any rows exist for particular data.
- 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.
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 in in-efficient way:
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
Best practice is:
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
SQL tuning refresher guide