The latest version of Adaptive Server Anywhere (ASA) marks a major turning
point in the history of this product. Prior to version 8 the most important
design goals were ease of use, small footprint, and cross-platform support,
with high speed taking a back seat. This time, improved performance is the
number one new feature. And the results? Mostly good, sometimes uneven,
getting better fast.
ASA is an affordable relational database management system that ships as
part of the SQL Anywhere Studio package from iAnywhere Solutions, a
subsidiary of Sybase. ASA supports all the features you expect from a modern
RDBMS, including ANSI standard SQL, multiuser network connectivity,
multiprocessor support, transaction commit and rollback, row-level locking,
referential integrity, BLOBs, events and triggers, and stored procedures.
Installation and Setup
Installation of SQL Anywhere Studio on a Windows platform uses a
straightforward InstallShield setup. A full developer's installation
requires about 120MB disk space in one place under C:\Program
Files\Sybase\SQL Anywhere 8. Multiple versions (5, 6, 7, 8) can coexist and
even run together on the same machine. In other words, an ASA installation
doesn't take over your machine or get in the way of other products, even
database servers from other vendors.
There's good news and bad news, and it's all part of the same story: the
query optimizer and execution engine have been completely rewritten. These
are the runtime components that analyze your SQL commands and pick from
among the thousands of different possible "plans" of execution. They were
rewritten for three reasons: to support improvements in the database file
structure, to support future SQL enhancements, and to make queries run
The good news is that most queries do run faster, sometimes much faster.
In a series of tests I ran against an old application, the improvements
ranged from a few percentage points to 75% faster. Other people have
reported queries running up to 10 times faster. Full table scans are no
longer the problem they once were, sometimes running faster than index
searches. Queries that need temporary tables are also faster, and indexes
using wide columns such as "last_name, first_name" benefit from a new
storage scheme. The bottom line is you don't have to work so hard to
optimize SQL commands; the server will do it for you.
The bad news is that improvements aren't guaranteed. A rewrite brings
behavior changes, and in some rare cases queries actually run slower on
version 8. These "queries from hell" are being dealt with as they turn up; I
know this for a fact, having reported some of my own nasty SQL.
There's a do-it-yourself fix for most of the performance
disappointments, however. Just change a database option with this command in
SET OPTION PUBLIC.Optimization_goal = 'All-rows';
This tells the server to pick execution plans that favor the retrieval
of entire result sets. This option didn't matter in earlier versions of ASA,
but now it's critically important. The current default value "First-row" is
wrong for most applications and it's going to be changed to "All-rows" in
Sometimes, however, it's up to you to make a SQL statement run faster.
For example, an index on "last_name, first_name" won't help a search on
first name (try finding all the "Susans" in the phone book). In these
situations there's nothing ASA or any other database server can do without
your help, and that's where the new graphical plan display comes in.
Figure 1 shows a SELECT where two tables are being scanned sequentially.
The graphical plan shows how the server handles the query, including
estimated and actual runtime statistics, giving you more than enough
information to help make decisions about indexes and other improvements.
The graphical plan is interactive: it lets you pick the main select and
subqueries for display, and click on individual nodes in the plan to see the
details in the right-hand panel or a popup box. You can even save the
graphical plan in an XML file for later display without connecting to the
database, a real time-saver when you're dealing with distributed databases
or you need someone else's advice about optimization.
Version 8 also comes with a new execution profiler to help you find slow
SQL statements. Experience shows that a few statements take up a lot of the
time in most applications. Not only that, but it's almost impossible to
predict which statements will be the slow ones.
The execution profiler lets you find the troublemakers inside stored
procedures, even inside triggers and user-defined functions called from
Figure 2 shows an example in which 45% of the total time was spent
executing a single DELETE statement. Hundreds of other statements took
almost no time to execute, so no matter how efficient or inefficient they
were, they didn't need attention. Just this DELETE that turned out to be
unnecessary: it removed temporary data stored in a permanent table. A simple
design change to use a temporary table eliminated the need for the DELETE
The speed and usability of the Java-based administrative tools Sybase
Central and Interactive SQL have been greatly improved. The native C version
of ISQL still ships with the product, but only the Java tools have cool
features like the two new editors for queries and result sets. Figure 3
shows a join of two tables where you can edit and save changes to the
database. You can insert, delete, and update rows; copy and paste to and
from columns; and confirm or cancel each change all without writing any
code other than a SELECT.
The performance improvements in version 8 are moving ASA into the world
of enterprise databases. ASA is inexpensive and easy to administer, as well
as being developer-friendly a viable alternative to Oracle, SQL Server,
DB2, and ASE.
Phone: 800 801-2069
Windows 95/98/Me/NT/2000/XP/CE, Netware, Compaq Tru64 Unix, IBM AIX, HP-UX,
Sun Solaris, Caldera, Mandrake, Red Hat, SuSE, TurboLinux, Windows 98, and
SQL Anywhere Studio 8.0 (Developer Edition) $399
Breck Carter [email protected]