[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


19 - Use UNION ALL

Figure 19 shows that adding the ALL keyword to a UNION makes it go quite a bit faster. That's because SQL Anywhere isn't required to remove duplicate rows and therefore doesn't have to create a temporary table. If you don't care that result set has duplicate rows or if you know there won't be any duplicates then use UNION ALL instead of UNION.

Figure 19 - UNION ALL Is Faster Than UNION 
CREATE TABLE part1

   ( primary1 INTEGER NOT NULL,

     search1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE TABLE part2

   ( primary2 INTEGER NOT NULL,

     search2 INTEGER NOT NULL,

     PRIMARY KEY ( primary2 ) );


SELECT * FROM part1

UNION

SELECT * FROM part2;

   PLAN> TEMPORARY TABLE part1 (seq): part2 (seq)

   Estimated - rows 2000, I/O 2695

   Actual    - rows 2000, I/O 401, time 1.80 seconds


SELECT * FROM part1

UNION ALL

SELECT * FROM part2;

   PLAN> part1 (seq): part2 (seq)

   Estimated - rows 2000, I/O 94

   Actual    - rows 2000, I/O 50, time 1.10 seconds

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