Existence checking


Link to this posting

Postby 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...
User avatar
Ursego
Site Admin
 
Posts: 140
Joined: 19 Feb 2013, 20:33



IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




cron
Traffic Counter

free counters

eXTReMe Tracker