Why to avoid SELECT COUNT(*) in your SQL query

SQL Tuning - Part-1
[SQL Tuning – Part-1]
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.

Related – 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 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
END-IF

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

Related topics:
SQL tuning refresher guide

 

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.