I have a view, that includes a SELECT-Statement with a union. If I declare
the union with UNION ALL, the view works very more performant than if I
declare the union only with UNION. The where-clauses of the two union-parts
are so, that every record can only belong to the result of one of the two
union-parts, so that the result of the view is the same with UNION and UNION
ALL.

Is it generally recommended to use UNION ALL instead of UNION, if a distinct
in the select-statement is not necessary?

Thanks, Alex Wittler

For performance UNION ALL will usually outperform
UNION since the latter does a distinct operate and that
will required a ordering to be imposed across the
entire (union all) result set. [and that will often require
a work table and a sort phase to be executed]

Other than performance you use a UNION if you
require a distinct, but using UNION as a convention
is as bad as always using a DISTINCT as a convention.
[a number of older product just assumed you always
wanted a 'distinct' result set and those would always
generate SQL to do that ... causing unanticipated slowness]

Since you have handled the distinctness in your 'where' clauses
then the result sets from UNION and UNION ALL should
be the same, execute for the ordering. So unless you need
the ordering as well, the UNION ALL would be recommend;
for performance reasons only.

"Alex Wittler" <wittler(at)(no-spam)sf-datentechnik(dot)de> wrote

In most cases the server will perform duplicate elimination using
hashing, as opposed to sort-then-merge. However, the arguments you make
are largely true regardless.

Glenn

Nick Elson [Sybase iAnywhere] wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport