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.

Product Description
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.

New Features
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 faster.

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 interactive SQL:

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 8.0.2.

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.

Figure 1

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 other statements.

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 altogether.

Figure 2

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.

Figure 3

Summary
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.

iAnywhere Solutions
Web: www.ianywhere.com
Phone: 800 801-2069

Test Platforms
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 Windows 2000

Pricing
SQL Anywhere Studio 8.0 (Developer Edition) $399