by Ursego » 22 Feb 2013, 09:41
Use EXISTS instead of COUNT to check if a record, satisfying the search criteria, exists in a table.
You should use COUNT only when you really want to count rows, i.e. need to answer the question: "How many rows match the criteria?". If you use COUNT to answer the question "Is there at least one row matching the criteria?", you are asking the RDBMS to do lots of unnecessary work.
Elegant programming means not only producing a code which is easy to read. For example, inefficient code doesn't look elegant to me even when it's perfect cosmetically... I don't feel comfortable seeing the following construction which, potentially, scans a huge amount of records:
*** BAD code: ***
Transact-SQL:
- Code: Select all
SELECT @count = COUNT(1) FROM ... WHERE ...
IF @count > 0...
PL/SQL:
- Code: Select all
SELECT COUNT(1) INTO v_count FROM...;
IF v_count > 0 THEN...
There is another, much nicer way to define a record existence - the scan will stop immediately after finding the first occurrence:
*** GOOD code: ***
Transact-SQL:
- Code: Select all
SET @exists = 0
IF EXISTS (SELECT 'x' FROM ... WHERE ...)
SET @exists = 1
IF @exists = 1...
PL/SQL:
- Code: Select all
BEGIN
SELECT 1
INTO v_exists
FROM DUAL
WHERE EXISTS (SELECT 'x' FROM... WHERE ...);
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_exists := 0;
END;
IF v_exists = 1 THEN...