First Look at Advantage Database Server 10

With the release of Advantage 10, Sybase continues the tradition of consistent improvements to this high-performance, low maintenance database server. In addition to a rich array of additional features and feature enhancements, this release also includes a large number of internal optimizations that will improve the performance of most Advantage client applications simply by upgrading the server to Advantage 10. These improvements add significant value to the already impressive collection of features that make Advantage Database Server a perfect match for small to medium size database applications.

Overview

The Advantage Database Server (ADS) is a high-performance, low maintenance database server by Sybase. ADS supports an impressive set of features often found in high-end databases. These features, in combination with its ease of installation and nearly maintenance free operation, make it a favorite database for vertical market applications.

With each new release of Advantage, the Sybase team has consistently created added value by introducing new and enhanced features, as well as improving the already impressive performance of the server. This tradition continues with this release of Advantage 10.

This post is designed to provide you with a brief overview of the Advantage Database Server, followed by a look at the new and enhanced features found in Advantage 10. If you are already familiar with Advantage, you may want to go directly to the section New Features and Enhancements in Advantage 10.

Overview of Advantage Database Server

The Advantage Database Server provides a unique set of features that make it an ideal database for small to medium size applications. The major features that make Advantage special are described in the following sections.

High Performance

To put it simply, Advantage is fast. Much of its speed comes from its architecture, which is based on ISAM (indexed sequential access method) technology. ISAM makes extensive use of indexes to provide high-speed table searches, filters, and table joins.

Unlike other ISAM technologies, such as dBase and Clipper, Advantage Database Server is a transaction processing, remote database server. As a result, it provides application developers with a reliable, distributed solution for managing data using client/server technology.

Low Maintenance

The Advantage Database Server installs in minutes, and rarely needs attention after that. Indeed, unlike high-end database servers, most Advantage installations do not have a database administrator. This makes Advantage an ideal server for vertical market applications where the server may be installed in many facilities that do not have their own IT department.

Navigational and Set Based Orientation

While Advantage is based on the navigational ISAM architecture, it also supports industry standard SQL (structured query language), with most of the SQL operations optimized for lighting fast execution. As a result, Advantage is one of the rare remote database servers to support both the navigational model of data access as well as set-based SQL, giving you a wealth of options for presenting and managing your data.

Advanced Feature Set

The Advantage Database Server sports an impressive collection of features often only found in high-end database servers. These include security provided by users and groups, table encryption, and support for encrypted client/server communication. Additional high-end features supported by Advantage include stored procedures, SQL PSMs (persistent stored modules), views, user defined functions, table- and field-level constraints, referential integrity, online backup, triggers, notifications, and replication.

Scalable

Advantage comes in two basic flavors: the Advantage Database Server (ADS) and the Advantage Local Server (ALS). ALS is a free, file-server based technology whose API (application programming interface) is identical to ADS. ALS permits developers to deploy their Advantage applications royalty free to clients who do not need the stability and power of a separate database server.

Importantly, as the needs of those applications deployed with ALS grow over time, those applications can be almost effortlessly scaled to client/server technology, in many cases simply by deploying ADS. So long as the client applications are designed correctly, those applications will begin using ADS the next time they execute.

New Features and Enhancements in Advantage 10

Rather than reciting a laundry list of updates, I have organized the enhancements into the following sections: Major performance improvements, enhanced notifications, additions to Advantage SQL, nested transactions, Unicode support, additional 64-bit clients, added design-time support for Delphi, and side-by-side installation. For a detailed listing of all of the updates found in Advantage 10, see the white paper at the following URL:

http://www.sybase.com/files/White_Papers/Advantage_WhatsNewADS10_WP.pdf

Major Performance Improvements

The Advantage Database Server has always been recognized for its superior performance, being able to handle very large amounts of data with blinding speed. That makes it all the more remarkable that one of the most enticing aspects of upgrading to Advantage 10 involves performance. Specifically, the performance of database operations in client applications will improve simply by upgrading the server to Advantage 10. In some cases, these performance gains will be significant.

Many of the internal systems that contribute to Advantage's already impressive performance were evaluated by Advantage's R&D engineers. Where possible, improved algorithms were introduced, caching was implemented or enhanced, and resources were pooled. These changes resulted in more efficient indexes, improved transaction handling, and more intelligent management of resources such threads, record locks, and file writes.

The effects of these improvements range from nice to stunning. During Advantage 10's Beta cycle, one of the Beta testers reported the results of his performance tests on some of his larger queries involving, in some cases, millions of records. He found that some Advantage 10 queries executed 40 percent faster than the same queries in Advantage 9. In other cases, the Advantage 10 queries were exponentially faster (one query that ran in 2.7 seconds in Advantage 9 took about 1 millisecond in Advantage 10). The R&D team has found similar improvements during testing.

But SQL queries are not the only area of Advantage to benefit from these internal improvements. Operations that benefit from Advantage's support for navigational operations have also improved. In fact, the Help files for Advantage 10 list no less than 20 specific improvements or optimizations introduced in Advantage 10. And these updates affect everything from cascading referential integrity updates to record insertion, from memo file header updates to table creation, from low-level index operations to worker thread management. Simply put, the performance enhancements introduced in Advantage 10 alone make a solid business case for upgrading from an earlier version of Advantage.

Enhanced Notifications

Notifications are a feature originally introduced in Advantage 9, and they provide you with a mechanism by which Advantage can notify interested client applications that some change has occurred on the server. For example, a client application can subscribe to a notification in order to be informed when the contents of a specific table have changed. The client application can then use this information to update the end user's view of that data.

A small change to notifications in Advantage 10 has resulted in a very significant improvement in their utility: Advantage 10 notifications now support a data packet. This data packet, in the form of a memo field, permits you to include any data you like along with the notification. This data may include the record ID of the record that was affected in the table of interest, the connection id of the user who made the change, the type of change, or any other data you like.

This data permits you to implement advanced features in your notification-subscribing clients. For example, you can now distinguish between changes made by your client application's user and those made by other users. This information can be used to automatically update a user's view of data when someone else has made changes, ignoring those changes posted by that user.

Additions to Advantage SQL

There are many updates and additions to Advantage's support for the structured query language (SQL). Of these, my favorite update is the new ability to use a stored procedure in the FROM clause of a SELECT query.

If you have a stored procedure that returns a result set, you can treat that result set like a table in a SQL SELECT statement, permitting you to select specific fields or expressions from the result set, link the stored procedure result to other tables (or other stored procedure result sets), and define WHERE clause conditions to select just those records in which you are interested. You can even use the predefined Advantage system stored procedures in the FROM clause.

Another enhancement is the ability to use Boolean expressions in your SQL statements. For example, if you have a table named CUSTOMER in which a Boolean (logical) field named Active appears, the following query will select all records where the Active field contains True.

SELECT * FROM CUSTOMER WHERE Active;

In previous versions of Advantage, you would have to form your query like the following:

SELECT * FROM CUSTOMER WHERE Active = True;

Also, TOP queries now support a START AT clause, which permits you to select a specific number of records beginning from some position in the result set other than the top. For example, the following query will return records 11 through 15 from the CUSTOMER table, ordered by last name.

SELECT TOP 5 START AT 11 FROM CUSTOMER ORDER BY LastName;

A collection of bitwise SQL operators have also been introduced. These include AND, OR, and XOR, as well as >> (right-shift) and << (left-shift).

There is also a new SQL scalar function: ISOWEEK, which returns the ISO 8601 week number for a given date (it is also a new expression engine function). And, some of the SQL scalar functions that were previously not expression engine function are now. These include DAY, DAYOFYEAR, DAYNAME, and MONTHNAME, to name a few. These are in addition to CHAR2HEX and HEX2CHAR, which are newly added expression engine functions. Support in the expression engine means indexes can now be created using these functions, which in turn allows the Advantage query engine to fully optimize restrictions that use these scalars.

Finally, there are a number of new system stored procedures and system variables. The following are just a few of the new system stored procedures available in Advantage 10: sp_SetRequestPriority, sp_GetForeignKeyColumns, and sp_IgnoreTableTransactions.

As far as system variables go, among the new variables are ::conn.OperationCount (number of operations performed on this connection), ::stmt.TrigEventType (the event type of the executing trigger), ::stmt.TrigType (the type of trigger executing), and ::conn.TransactionCount (the current nesting depth of nested transactions).

Nested Transactions

Speaking of nested transactions, Advantage 10 now supports them. In previous versions of Advantage, code executing in an active transaction could not attempt to start a transaction without raising an exception. This is no longer the case. As a result, if you write a stored procedure whose operations should be performed in a transaction, you can safely call BEGIN TRANSACTION, even if that stored procedure is called by code where a transaction is already active.

New Table Features

Several interesting new table-specific features have been introduced in Advantage 10. Several of these are related to transactions and table caching. Let's consider table caching first.

To begin with, so long as memory resources permit, temporary tables are now kept entirely in cache. As a result, operations that rely on temporary tables are usually very fast.

There is also a new table property called Table Caching. Most tables are created with Table Caching set to None. These tables are not cached, and any changes to these tables are written to the underlying file immediately.

When Table Caching is set to either Read or Write, the corresponding table is kept in cache while it is open, making its data highly available. These settings are normally used for data that is largely static, and which can be reconstructed if the table becomes corrupt. Specifically, tables held in cache are not written to disk except when the table is closed. As a result, changes to their data will be lost if Advantage unexpectedly shuts down without being able to persist those tables' contents (for instance, if there is a sudden failure of your server's power supply). However, this functionality can be very useful for static data (zip codes, part numbers, and so forth).

The transaction free tables feature is also a table property, called Trans Free Table. When set to True, the associated table does not participate in transactions.

There are two implications of a table not participating in an active transaction. First, changes made to a Trans Free Table during a transaction are not rolled back even if the transaction itself is rolled back. Second, changes to data in a Trans Free Table are not isolated during a transaction, being immediately visible to all other client applications, even though the transaction has not yet been committed.

Just like when a table's Table Caching property is set to Read or Write, Trans Free Table is set to True only for special tables in most applications. For example, you may use a table to log a user's actions in an application. In those cases, you may want to log that a user tried to perform some task, even though the action may fail and the user's changes may be rolled back.

Similarly, you may have a table used for generating unique key field values. This table may have a single record and single field that holds an integer value. A client needing a key would lock this table, read the key, increment the integer, and then release the lock.

With such a table, the incremented key needs to be visible to all client applications, even if individual clients increment the key from within a transaction. If such a table were not a Trans Free Table, other clients would not be able to access the incremented key until the transaction was committed, rendering the table useless for its intended purpose.

Unicode support

Although Unicode support is arguably a table feature, its significance warrants separate consideration. In short, Advantage 10 introduces three new field types. These types, nchar, nvarchar, and nmemo, are UTF-16 Unicode field types. The nchar type is a fixed length Unicode string field and nvarchar is a variable length Unicode string field. The data for these two field types are stored entirely in the table file.

The nmemo field, by comparison, is a variable length Unicode field that is stored in the memo file. Together, these three fields provide you with a number of options for storing Unicode symbols and characters in Advantage tables.

More 64-bit Clients

Advantage 9 introduced 64-bit versions of both the Windows and Linux Advantage servers, as well as 64-bit clients for the Advantage Client Engine (ACE) and the Advantage .NET Data Provider. A number of additional 64-bit clients have been added in Advantage 10, including 64-bit versions of the OLE DB Provider, the ODBC driver, as well as the Linux PHP driver. The Advantage Data Provider for .NET has also been enhanced to use the appropriate 64-bit or 32-bit drivers, depending on the OS on which your managed code is executing.

In addition, 64-bit versions of the Advantage Local Server (ALS) and Advantage backup utility have been introduced in Advantage 10.

Added Design-Time Support in Delphi

The SQL Utility, a comprehensive SQL editor and debugger, is now exposed as a property editor directly within the Delphi IDE (integrated development environment). To use the SQL Utility within Delphi, select the ellipsis button on the SQL property in Delphi's Object Inspector when an AdsQuery component is selected.

Using the SQL Utility, you can check the syntax of your SQL, execute it, and even set breakpoints and debug your SQL scripts. Once you are satisfied with your SQL, click the Save button on the SQL Utility toolbar (or press Ctrl-S) to save your SQL to the SQL property of the AdsQuery.

The Advantage Delphi Components also include a new component, TAdsEvent. This component, which you can use to subscribe to and handle notifications, allows you to easily configure and manage the handling of asynchronous events.

Side-By-Side Installations

With Advantage 10, it is now possible to run two or more instances of the Advantage server on the same physical server, even different versions of Advantage. For example, it is now possible to run Advantage 9 and Advantage 10 on the same server. This feature is particularly useful for vertical market developers whose applications need to support more than one version of the Advantage server.

Conclusion

With the release of Advantage 10, Sybase has once again confirmed its commitment to this unique and valuable database server. In addition to a number of useful additions and enhancements, Advantage 10 also includes a wide range of performance improvements that will improve the performance of most client applications merely by installing this updated server.

Most developers, however, will also want to update their client applications to benefit from the many enhancements found in Advantage 10. From support for Unicode to greatly improved notifications, from updated SQL syntax to enhanced table features, Advantage 10 has something for everybody.

Want to Learn More

Loy Anderson and I have released the latest edition of our Advantage book, Advantage Database Server: A Developer's Guide, 2nd Edition with extensive coverage of ADS 10. For more information and to order this book, please visit:

http://www.jensendatasystems.com/ADSBook10/