The Query Processing Engine (QPE) of Sybase ASE 15 provides high performance for applications in hybrid workloads (1)

Summary

ASE has become a high-performance leader in OLTP (on-line transaction processing, online transaction processing) systems. However, users now need not only high transaction processing performance, but also Analysis of the transaction data of the current activity, and such analysis must be performed in real-time on the original platform.

In fact, very large databases are often used for data mining and analysis to obtain business intelligence. This analysis is vital to the company-helping the company identify trends and provide systematic support for the company's long-term decision-making. As data analysis and query become more complex, the design of the data server has changed from a simple transactional OLTP server to a platform for the decision-making support system (DSS)-or, operational Decision Support System (ODSS.

In recent years, the data volume has experienced explosive growth. The data volume in the production and trading system is growing rapidly at a rate of 125% per year. As we all know, the larger the data volume in the database, the greater the impact on the performance of applications running on it. When running a query operation similar to Data Mining and analysis, it is easy to bring the performance of the production transaction system almost to a halt. In general, we can dump historical data to an independent data warehouse for analysis to reduce this impact on performance. This will effectively reduce the amount of data in the production and transaction databases and relieve the pressure on the overall performance of the database.

Currently, the data warehouse system usually runs on the basis of a large amount of static data. Most data warehouse systems are independent of the daily production and transaction systems and focus on query and analysis operations. These systems are generally used to configure database servers and hardware facilities for the DSS work environment. The historical data managed by it is usually loaded to the system in bulk mode. Most of the queries used in the decision support system contain complex aggregation operations, a large number of data classifications, and multi-table join operations. Compared with queries that are often run in OLTP systems, the complexity is quite different.

Therefore, the database server must be able to ensure high performance in the ODSS environment with mixed workloads. OLTP operations and DSS can run simultaneously on the same platform. This flexibility is required by the user to control total cost of ownership TOC, total cost of ownership.

One of the important factors affecting TCO is performance tuning, which is usually undertaken by the database administrator DBA. The enhanced performance and stability of ASE15 mean that the database administrator does not need to adjust the system as often as before, which is the key factor to reduce TCO. In addition, during the use of ASE15, the Database Administrator does not need to go down and restart frequently.

Sybase ASE 15 can provide new features and functions to meet the huge performance and economic needs of users. This article will discuss how ASE15 can provide excellent ODSS performance on various data platforms, from small data volumes to massive data storage, while controlling your overhead.

How to implement operational decision analysis and processing in ASE 15

The query statement used in DSS analysis is generally much more complex than the query in OLTP. Query processing engine (QPE) determines how to query the required data in the most effective way. The query processing engine optimizer will generate a query plan containing a series of commands to determine how to run the query. The running engine executes the query plan and returns the result set.

The query processing engine performance in ASE15 has been greatly improved, which includes a very efficient optimization technology. The optimization components built in the query processing engine of ASE15 use very mature and advanced technologies. Compared with earlier versions, ASE15 provides more options and methods for automatic analysis and selection of high-performance query plans, thus improving performance efficiently.

Target-Based Optimization 

ASE15 performs high performance when executing all the queries in the application. If you want ASE15 to have the best running performance in the ODSS environment, you do not need to adjust the Query Processing Engine in advance. When the system needs to be upgraded to ASE15, this will help you save a lot of system management time.

Applications often have different requirements for Query Processing engines. These requirements are related to the functions and running Modes of these applications. For example, for a stock trading system, the system needs to provide the best performance in a standard OLTP environment where transaction queries are simple or moderately complex. Others. For example, the online order system may need to return the first few rows of data in the result set very quickly. Or, another example is that the sales system can quickly respond to simple or moderately complex transaction queries in the ODSS environment, and can also handle highly complex DSS-type queries.

The Optimization goal Optimization Goals is provided in ASE15 so that the query Optimization can fully meet the application requirements. The optimization goal is a set of built-in optimization conditions, which will fully affect the running mode of the optimizer component in the query processing engine. Each target Goal is designed as a guiding optimizer to use various features and functions to find the optimal query plan.

There are three optimization objectives. The first goal was designed to allow the query processing engine to use all possible technologies, including new features and features, to find and implement optimal query plans. This goal is to optimize the entire result set and balance different queries of OLTP and DSS types. This is also the default setting.

The second optimization goal will enable the query processing engine to obtain the optimal query plan using the most appropriate technology for pure OLTP queries. The query processing engine uses not only the original Optimization Technology for OLTP operations, but also some new performance-related features and functions provided in ASE15. For example, in this method, only nested-loop join is used, because this technology is the most effective for OLTP queries. At the same time, the sort operation will be minimized. This optimization goal can be used when your application only runs transactions without any ODSS operation requirements. This optimization goal will greatly improve the performance of simple queries by ASE in the OLTP environment.

The third optimization goal is to generate a query plan that can return the first few rows of data in the query result set as soon as possible. This optimization aims to provide extremely high performance for applications based on cursors or Web.

These optimization goals can be set at the server level. You can also set it at the session or query level for testing purposes. Once set, you do not need to adjust the running mode of the optimization target in the selected running environment.

Improve Index utilization in queries

ASE15 can be used to access data based on multiple indexes. The incompatibility between data types and indexes will no longer exist.

The new hashing Technology of ASE15 provides more indexing methods. Multiple indexes of the same table can be used in one query. This is especially important for queries that contain OR star join commands. In the complex Join operations that contain OR clauses, the index utilization is greatly improved.

Using as many indexes as possible is important to the performance improvement of any application. However, when you require that ODSS operations on new transaction data be completed as soon as possible, this is especially important.

Significantly improved sorting performance

In ASE15, do not use worktables on the worksheet. This is a major improvement in performance. Worksheet inTempdb. Worksheet-derived from a user table and imported data-results in performance bottlenecks. When you read a worksheet to obtain the result set, other bottlenecks will occur. In general, using a worksheet to complete sorting will affect performance.

However, in ASE15, the new Hash technology is used to complete sorting and grouping operations in the memory, thus avoiding the use of worksheets. The memory buffer is used for sorting operations, so the Stored procedure Buffer procedure cache of ASE will no longer be used.

Queries that contain order by and group by are very common in applications. They are often used to generate analysis reports. Commercial applications often return information in a specific order. In ASE15, you will see a significant improvement in the performance of such complex queries.