ASE 15 addresses the need for database speed

If I had to state an overall theme for the newest version of Sybase’s flagship database server, it would be one word: performance. The recently released ASE (Adaptive Server Enterprise) 15 has significant changes, from partitioning to function-based indexes, all of which are aimed at making the database speedier, and it shows.

Free IT resource Virtualization Insights from Top Experts - Learn how virtualization gets real! Sponsored by Dell Free IT resource TechNet: More ways to know it, share it, and keep it running. Sponsored by Microsoft Sybase ASE 15 Enterprise Edition Sybase, sybase.com Very Good  8.4 criteria score weight Manageability 8 25% Performance 8 25% Availability 9 20% Scalability 9 20% Value 8 10% Cost: Starts at $2,995 per server, $595 per seat, or $24,995 per CPU Platforms: Solaris/Sparc, IBM AIX, HP-UX/HPPA, Windows/x86, Linux/x86 Bottom Line: Sybase ASE 15 offers significant performance improvements over earlier versions. With partitioning, materialized computer columns and function-based indexes, ASE shops will definitely notice the difference in speed. Advances made to the data engine improve ASE’s support, but the much-touted native encryption hasn’t actually been implemented in this release, so users will have to wait for that feature. About our Reviews and Scoring Methodology

Data anywhere

One of ASE 15’s most significant new features is partitioning. Partitioning allows you to horizontally divide up the storage of a specific table by placing it in different locations; the database then automatically places data in the appropriate partition based on your criteria.

ASE 15 comes with four partition settings: Hash, Round-Robin, List, and Range. Round-Robin is the default setting and offers the biggest performance increase by writing data to the table in a RAID fashion. If you have four partitions defined on four separate disks, the first transaction would be written to the first partition, the second transaction written to the second partition, and so on.

This method is built for speed, and it delivers. I tested Round-Robin partitioning with a 400GB table partitioned onto separate RAID volumes and saw a maximum performance increase of about 35 percent. The other partitioning options also do well; they partition based on criteria other than pure performance, such as date ranges or zip codes.

Faster queries

Without the ability to return data quickly, a database is nothing more than an electronic safe deposit box. Sybase has done a lot of work to avoid that, almost completely rewriting the query processor in ASE 15

For ASE users, that means much improved index selection, as well as both horizontal and vertical parallelism. Parallelism is a database’s ability to use multiple CPUs for an operation and perform the different parts in parallel, increasing the speed with which you return large or complicated data sets.

Some of the more noticeable query processor enhancements (and there are many) can increase performance as much as the parallelism features. Materialized, computed columns, for example, significantly speed up queries by returning data based on a calculated formula. Until now, this data existed in the database only as a formula calculated at run time. ASE 15 allows you to materialize this data so that it’s calculated and the result is written to disk when the row is initially inserted. The data can also be indexed as part of the write -- a process that has an incredible effect on the return time of a query.

Function-based indexes are another important enhancement. A function-based index contains an expression as part of its definition; instead of simply indexing a column or set of columns, you index them based on an expression that contains functions, global variables, constants, etc., separated by one or more operators.

Creating a function-based index will really speed up processes with clauses that contain nondeterministic functions and mathematic expressions -- it’s an excellent solution for a reporting system where ad hoc write operations won’t be a factor. Depending on the size and complexity of the expression contained in the index, however, maintaining it in a production environment could prove costly, so be sure to thoroughly test your performance.