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.
Last modified: May 23, 1996
mail to: email@example.com
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 ifIf 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 ifThe 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.