Microsoft SQL Server provides an indexed view capability, and they claim similar benefits to Akiban. Are they the same? What’s the difference?
At a high level, indexed views are materialized views – the unique clustered index is persisted to disk in order to improve run-time performance. Indexed views persist a copy of the source data, meaning they introduce another data object into the database that must be maintained when data is inserted, updated or deleted. Conversely, Akiban table-groups are the data. Each row is stored exactly once. Unlike indexed views, table-groups actually consolidate the b-trees for grouped tables into a single, highly-optimized data structure.
There are three major drawbacks to Microsoft’s solution relative to Akiban:
1. OLTP-style workloads are negatively affected by indexed views due to the double-write requirement inherent in a secondary copy of the base table data. This cost becomes even more expensive when considering that many queries may not take advantage of the indexed view, and so the cost of persistence and maintenance is incurred without realizing an associated benefit.
2. In addition, if an indexed view is referred to directly in a FROM clause, the (NOEXPAND) hint must be used, for non-Enterprise edition deployments, to avoid the optimizer expanding the view definition until it reaches the base tables. In the converse case, when you want to ensure the view is expanded so data is read from the base tables, you may have to add the EXPAND VIEWS option. Akiban table-groups do not require any specific syntax or hints to be effective, and because table-groups are the base table data, there is no need to troubleshoot the impact of using hints.
3. Indexed views are primarily designed to support or enhance the performance of analytic / OLAP workloads. Akiban table-groups provide query acceleration and execution efficiency for transanalytics (both transactional and analytic workloads).
History, despite its wrenching pain, cannot be unlived, but if faced with courage, need not be lived again. – Maya Angelou
Historically, views served to restrict users to a certain subset of data, and to customize how data is logically viewed. The major drawback, however, was that any joins or aggregations used to define the view were performed at runtime. Microsoft has extended the utility of views by allowing clustered, unique indexes to precompute and materialize the view. This can improve data access performance, particularly for aggregate views in decision support or data warehouse environments. In fact, the view need not be directly referenced – the query optimizer can use the view to arrive at the lowest-cost query plan.
There are three major query performance benefits that can be realized:
1. Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
2. Tables can be prejoined and the resulting data set stored.
3. Combinations of joins or aggregations can be stored.
Akiban table-grouping is a better way to achieve these benefits across a broader set of use cases and without the write penalties inherent in denormalization and double-writing. We have learned from history – there is a better way to provide these benefits.
It pays to plan ahead: precompute correctly
SQL Server’s indexed views precompute and store aggregations to minimize run-time overhead and performance impact. They also prejoin information and store the results. In both cases, indexed views are materialized views that precompute aggregations and joins – common denormalization techniques – therefore indexed views are simply denormalized tables in a database. Denormalization introduces redundant data that must be maintained – in this case automatically and transactionally by SQL Server – but those redundancies introduce latency, resource utilization overhead, contention for database object access, and an increased rate of error. Akiban table-groups precompute joins, but instead of storing a secondary copy of the data, table-groups are the data. Table-groups maintain the logical level of a database: individual tables. This means that SQL does not change. But at the physical level, data is stored and manipulated differently. The component tables of a table-group are stored in an interleaved data structure called an hKey, which allows Akiban Server to provide object (document) access to relational data; table-grouping and hKeys make multiple table joins equivalent to single table accesses. Because table-groups enable data management for multiple tables, Akiban can also index across tables, and also interleave multiple indexes while processing queries.
Both of these techniques are more robust and more generally applicable for query optimizations as they apply directly to the base data, and not an abstraction thereof. For that reason, Akiban’s optimizer can better optimize query performance without caveats or the need for implementors to evaluate and test various hints.