ASE 15 addresses the need for database speed
Partitioning, function-based indexes give newest version of Sybase database a boost
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.
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.
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.