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.
ASE 15 introduces some data engine enhancements that will probably be invisible to end users but will make admins very happy. One of my personal favorites is the new unsigned integer support, which gives you the ability to store all integers as positive numbers instead of a range of negative to positive.
This support can be quite significant if you have an application that uses an auto-increment column that’s running out of range values. Previously, the only solution was to increase the size of the datatype, but that also increases the storage requirement. ASE’s unsigned support doesn’t affect the storage requirement at all.
ASE 15 also exponentially increases the maximum size of your data. Before, the total size of the data on your server could only be 8TB, but the new architecture allows you to create up to 2,147,483,647 disk devices, each of which can be as large as 4TB.
Leading or following?
So where does this put ASE globally? Well, in some places it’s slightly ahead of other vendors, and in others it’s finally catching up -- but there are a few spots where it’s still behind the curve.
The new parallelism and function-based indexes put ASE only slightly ahead of its competitors because not every type of query will benefit from the index. It’s highly possible that you’ll implement a function-based query and see little to no improvement. (Of course, it’s also possible that you’ll get a sizeable improvement.)
Sybase’s marketing machine touts ASE 15’s built-in encryption, a feature none of the other vendors have yet. The built-in encryption will allow you to encrypt column-level data without writing any code; all you do is define the column as encrypted. This is the same way third-party products such as DbEncrypt work.
Although Sybase is correct in saying that none of their competitors have this functionality yet, technically, neither does Sybase. The underlying framework for the built-in encryption is the only thing that made it to ASE 15; the actual functionality won’t be available until 2006.
ASE does, however, include “big int” support in this release. Big ints are exact numbers that range from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807 (or 0 to 18,446,744,073,709,551,615 if using the new unsigned support). They’re important for storing very large number representations, and are often used as auto-increment columns to keep ordered surrogate keys. Other databases such as SQL Server 2000 have had this capability for years, so it’s good to see Sybase catching up.
ASE has made some improvements to its Interactive SQL development tool, but its capabilities still barely exceed those of Notepad. I also feel Sybase remains a bit behind in security: ASE 15 still installs with its main admin account “sa” with a null password, so any ASE server is exposed on initial install until this is changed.
Nevertheless, ASE 15 is a good release. Sybase put in a lot of work on the query processor, and the partitioning, parallelism, increased data support, and unsigned integer support are nice features. ASE 15 is a good prospect for the Sybase world, but it won’t be making many waves in any other pools.
Overall Score (100%)
|Sybase ASE 15 Enterprise Edition||9.0||8.0||9.0||8.0||8.0|
You may still be better off sticking with Win7 or Win8.1, given the wide range of ongoing Win10...
An unlikely combination of two Windows updates can reduce scan times from hours to minutes
No-code and low-code mobile programming tools give business users and developers a fast track to mobile...
Sponsored by Hewlett Packard Enterprise
These 13 tools and techniques prove that, when it comes to coding, laziness is a virtue
We'll help you find the best wireless speakers for pairing with your smartphone or tablet—whatever your...
When developers and suppliers carefully list the tools used to build an application and what...
Microsoft's Insider Program has fallen off the rails, but a few simple fixes would go a long way