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.


Breck Carter
Last modified: May 23, 1996
mail to: bcarter@bcarter.com



Exists Versus Count(*)

A glib suggestion is made in Optimizing SQL to "Use Exists instead of Count". The obvious question is "How?".

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:

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 Watcom if-then-else-endif construction can be used together with exists to return a simple yes/no answer. Timing tests show that the SQL optimizer does a pretty good job because the following select ran 20 times faster on one machine:

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 Watcom SQL 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.


Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.