By increasing the amount of data as well as the need for faster processing, database providers also have capabilities in their products. The In-Memory feature is one of these, and in the first and second parts of this series, we compared In-Memory to SQL Server and Oracle. In this article, which is the third part (final), we will examine this technology in IBM DB2 10.5 and compare it with SQL Server.
Data Analysis with BLU Acceleration in IBM DB2 10.5
The IBM DB2 BLU Acceleration feature is available as an integrated In-Memory integration solution with IBM DB2 10.5 . This solution has several optimization processes, but firstly created by a concept called Table Shadow that holds a copy of the data in the column storage format. It should be noted that both tables are automatically synced. OLTP transactions are executed directly on the relational tables, but the analytic queries available on these tables are sent to columnar columns of Tables Shadow, which results in faster analytical processing.
IBM DB2 BLU Acceleration includes seven key features and functionality:
- Simple application:
IBM claims that this feature will be available as soon as activation, and users only have to upload the data and create the desired Query. Meanwhile, there is no need for any specific indicators or settings. Related operations, such as loading, backing up and restoring, have also been streamlined.
- Operational Compression or Actionable Compression :
In addition to the benefit of an optimized compression mechanism, IBM also allows direct operation of compressed data. In fact, BLU Acceleration can execute join or aggregate processes and apply predicates directly to compressed data without requiring extraction of data from this state.
- Use multiple CPUs simultaneously:
IBM uses a new concept called Single Instruction Multiple Datasets (SIMD) to be able to apply a single instruction on different parts of the data. By using SIMD, it is possible to run many common functions simultaneously, which ultimately leads to a faster query execution.
- Parallel and core processing:
If workloads are run on a multi-core machine, IBM uses all the processing power to perform this process in parallel. It should be noted that the feasibility of this is due to the fact that the program was originally designed to allow multiple cores to be used.
- Column storage:
The data is organized in a pillar, which offers the benefits of more efficient storage, faster execution of queries, and fine-tuning the exact settings.
- Cache and Scan process compatibility :
IBM announces that it uses optimized memory and Cache management techniques that are individually available for OLTP workloads and workloads in data warehouses. IBM can minimize the impact of I / O performance by using the Scan-compatible Cache process.
- The Data Skipping :
The Data Skipping large portions of data in any of the Query usable not be ignored and thus this is the CPU, RAM and I / O savings, leading to Query faster and without having to do the exact settings used. Data Skipping can be a concept similar to the Segment Elimination in SQL Server.
Compare IBM DB2 BLU Acceleration and SQL Server In-Memory
Below is a comparison of two types of database servers:
|Property||DB2 BLU Acceleration||SQL Server 2014||SQL Server 2016|
|Seven Key Features||Yes||Yes (there are a few differences)||Yes (there are a few differences)|
|Query function||Yes||Yes (Batch Mode)||Yes (Queries like Batch Mode, Aggregate, Pushdown, Search, and Short-Range)|
|Concurrent DML||No||Yes||Yes (Enhanced synchronization with Row-Level Locking and Non-Blocking Reads)|
|Index maintenance automatically||Yes||No||Yes|
|Functional analysis||Yes (using the Shadow table)||No (acquisition through manual transfer to CCI)||Yes (fully integrated)|
Below is a presentation of the concept of each of the above table items:
Seven Key Features
IBM DB2 BLU Acceleration has been developed around seven key features that include concepts and technologies such as Columnstore tables, data compression, hardware-related processing optimizations, and memory management and cache. Microsoft also supports these technologies and concepts, but their implementation levels are different. It should be noted that these functions will also be developed in 2016 for SQL Server.
IBM DB2 BLU Acceleration has implemented a mechanism to improve queries performance in analytical processes. Microsoft also supports this mechanism with a special mode called Batch Mode, which has better performance, but DB2 does not provide Batch Mode. In version 2016 of SQL Server, Batch Mode will be implemented for more operators. For example, it is not currently possible to execute Order-By queries on Mode Batch in SQL Server 2014, but in SQL Server 2016, this feature is provided. Microsoft is seeking to increase the speed of data warehouse queries by investing more in Mode Batch.
Workloads in IBM DB2 BLU Acceleration are not well suited to Concurrent DML due to problems with the Blocking process. 2016 SQL Server includes the implementation of the Concurrent DML in Columnstore and Row-Level Locking, which is not available in the 2014 version.
Index maintenance automatically
IBM DB2 BLU Acceleration is capable of automatic indexing. Generally, at the time of data deletion, the rows associated with them do not quickly remove the Columnstore clustered index. These rows are marked with a Delete token or label indicating that the rows are deleted. As time goes on and many rows are removed, they still occupy Space Columnstore. One way to clear deleted rows is to rebuild the indices after a period of time. DB2 provides Automatic index maintenance, which removes deleted rows automatically from the indexes. This feature is not currently available in SQL Server 2014, but will be available in 2016 for SQL Server.
DB2 uses the concept of Table Shadow , which creates new Columnstore tables based on an existing relationship table. From the application perspective, the user has an OLTP table and a Shadow table that is associated with it. The user can execute their workloads on the OLTP table and analytic queries are automatically redirected to the Shadow table. In SQL Server 2014, this process should be performed manually, but in the upcoming version of SQL Server, users can create a non-clustered Columnstore indicator that can run OLTP queries and analytics on a single table. So, instead of using a Table Shadow, there will be an upgradeable indicator.