Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


18 - Use EXISTS Instead Of COUNT(*)

One of the most common uses of COUNT(*) is to answer the question "Does this exist?" or "Are there any rows that satisfy this condition?" The COUNT(*) function does more than just answer the question, it actually counts the number of rows that satisfy the condition as shown by the PowerBuilder code in Figure 18A.

Figure 18A - SELECT COUNT(*) Is Slow 
long ll_count

SELECT COUNT(*)

  INTO :ll_count

  FROM xref_info

 WHERE short_event LIKE '%open%'

 USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

   MessageBox ( "Error", "Count failed" )

   RETURN

END IF

If no index exists on the columns in the WHERE clause, or if a particularly nasty expression such as LIKE '%open%' is used, such a query can take a long time to execute.

The SQL Anywhere IF-THEN-ELSE-ENDIF construction can be used together with EXISTS to return a simple yes/no answer. Timing tests show that the optimizer does a pretty good job because the PowerScript SELECT in Figure 18B select ran 20 times faster on one machine.

Figure 18B - IF EXISTS Is Fast 
long ll_yes_no

SELECT IF EXISTS (

          SELECT short_event

            FROM xref_info

           WHERE short_event like '%open%' )

       THEN 1

       ELSE 0

       ENDIF

  INTO :ll_yes_no

  FROM SYS.DUMMY

 USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN

   MessageBox ( "Error", "Exists failed" )

   RETURN

END IF

The SYS.DUMMY table is provided by SQL Anywhere for use when the SELECT isn't referring to any other table. In this case it's the inner select that's doing all the work and the outer select is just returning a literal 1 or 0.

Figure 18C shows that even the simple question "Are there any rows at all in this table?" is better answered with an IF EXISTS expression than by calling COUNT(*).

Figure 18C - Are There Any Rows At All? 
CREATE TABLE test1

   ( primary1 INTEGER NOT NULL,

     search1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


Test 1...

   SELECT COUNT(*) FROM test1;

   PLAN> test1 (seq)

   I/O 256, time 0.87 seconds


Test 2...

   SELECT IF EXISTS ( SELECT * FROM test1 )

          THEN 1

          ELSE 0

          ENDIF;

   PLAN> DUMMY (seq): test1 (seq)

   I/O 1, time 0.5 seconds

[Home] [Table of Contents] [Previous Section] [Next Section]