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.
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
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
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
Comments are closed.