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


20 - Replace OR With UNION

A WHERE clause with an OR operator may cause SQL Anywhere to use only one index when two are available, or worse, to scan the whole table. Figure 20 shows that breaking a query into two SELECTs with a UNION operator can make it run ten times faster. In this case a UNION ALL offers no further benefits but because it does eliminate the temporary table it might speed things up if the result set was larger.

Figure 20 - UNION Is Faster Than OR 
CREATE TABLE indexed1

   ( primary1 INTEGER NOT NULL,

     search1 INTEGER NOT NULL,

     search2 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE INDEX isearch1 ON indexed1 ( search1 );

CREATE INDEX isearch2 ON indexed1 ( search2 );


Test 1 - SELECT With OR does a slow sequential table scan...


   SELECT * FROM indexed1

    WHERE search1 = 4444

       OR search2 = 5555;


   PLAN> indexed1 (seq)

   Estimated: I/O 626

   Actual:    I/O 358, time 2.95 seconds


Test 2 - UNION is much faster because it uses both indexes...


   SELECT * FROM indexed1

    WHERE search1 = 4444

   UNION

   SELECT * FROM indexed1

    WHERE search2 = 5555;


   PLAN> TEMPORARY TABLE indexed1 (isearch1): indexed1 (isearch2)

   Estimated: I/O 9

   Actual:    I/O 10, time 0.21 seconds


Test 3 - UNION ALL eliminates the temporary table...


   SELECT * FROM indexed1

    WHERE search1 = 4444

   UNION ALL

   SELECT * FROM indexed1

    WHERE search2 = 5555;


   PLAN> indexed1 (isearch1): indexed1 (isearch2)

   Estimated: I/O 4

   Actual:    I/O 10, time 0.20 seconds

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