Query Execution at Blazing Speed

Dramatically accelerate database queries with advanced Informix Warehouse Accelerator

IBM® Informix® Warehouse Accelerator (IWA) software integrates seamlessly into IBM Informix server and can make database queries faster by many orders of magnitude than ever before. Although IWA has been available for a couple of years, the latest release is designed to improve on previous versions by helping speed up a greater variety of SQL statements than was possible previously1 and to make it easy to set up and administer. The ease of setup and administration is achieved through integration with the IBM OpenAdmin Tool (OAT) for Informix.

Compressing and optimizing data for quick retrieval

Mike Walker, a senior database consultant with Advanced DataTools, performed benchmark testing of the latest release in August 2013 and first presented these results to the Colorado Informix User Group meeting on September 2, 2013. The following very simple query provides an example from the benchmark testing that demonstrates performing a sequential scan of a one billion–record fact table to show total sales year by year:

SET ENVIRONMENT use_dwa 'accelerate on';
select transaction_year, sum(total_price_paid)
from sales_fact
group by 1
order by 1;

The SET ENVIRONMENT line tells the database to try and accelerate the statements that follow it. In the testing, and with the sales_fact table loaded into IWA, this query completes in just 7 seconds. Without deploying IWA, this query can take over 14 minutes to complete.2 Results returned in seconds instead of minutes or hours can transform the way businesses work. They would no longer need to rely on summary tables and static reports that had to be run overnight; instead, they can obtain answers to new questions in almost real time.

IWA is designed to achieve such incredible performance improvements by reading selected tables into memory and using different technologies to compress and optimize the data. This in-memory compression and optimization process can make retrieval extremely fast. Queries submitted to the database are checked to see if they can be satisfied by IWA, and if so, the results are returned very quickly to the database. Queries that cannot be satisfied by IWA are processed as usual. All the queries are still submitted to the database engine, so IWA will work with all current applications such as the Informix DB-Access utility, Java, Open Database Connectivity (ODBC), business intelligence (BI) tools, and so on.

The use_dwa statement can be included in individual queries that are to be passed to the accelerator, or it can be included in the sysdbopen stored procedure so that it will take effect for all connections to the database. Plus, IWA can be implemented with no changes to source applications.

Analyzing workloads for acceleration

The objective of this testing was to compare the performance of the accelerator against a well-tuned database. During this process, Informix performance was impressive when tuning the amount of decision support memory and increasing the number of central processing unit (CPU) virtual processors (VPs). Tuning the engine and 13 sample queries that typically require 5 hours to execute enabled their completion in approximately 1.5 hours. And that result was accomplished by allocating all of the processor and memory resources to executing the 13 test queries. Using the accelerator, these same queries were executed in just over 17 minutes and used less than the total resources (see figure).3
Query Execution_at Blazing Speed

* In this query execution test, the query used all indexed reads to return a small data set from very large tables, which in this instance offered a more expedient execution than running the query with IWA.

Comparing query execution performance using IWA against a well-tuned database

Specifying which tables and columns are to be accelerated by creating a data mart is a required step for setting up the accelerator. This setup can be done with the supplied IBM Smart Analytics Optimizer Studio, which provides a graphical method to select the tables and columns to accelerate. A simple way to create an initial data mart is to use Workload Analysis, which examines a sample set of SQL queries provided to determine which tables and columns should be accelerated. With Informix 12.10, Workload Analysis has been integrated into OAT and allows a data mart to be created in just a few minutes and with only a few clicks of the mouse.

The data within IWA is populated by a load process that reads the source data and compresses it in memory. The initial release of IWA required a reload of all the data to reflect any changes in the source tables, which was suitable for static databases or data warehouses that do not change often. The new release of IWA opens up the software to a lot more applications than previous versions by introducing different methods to refresh the data without performing a full reload into IWA. For example, a partition refresh feature is now available to refresh a single partition within a fragmented table, or to refresh a single table. For source data that changes more frequently, an advanced trickle feed feature allows new records inserted into the source database to be applied to the IWA copy of the data.

The performance improvements that can be gained by implementing the Informix Warehouse Accelerator are amazing, and the recent release of IWA helps simplify its implementation and use, while expanding it suitably to a greater range of database applications than ever before. For more information on IWA and the benchmark testing performed by Advanced DataTools, attend the webcast, New Informix Warehouse Accelerator 12.10 Benchmarks, on October 1, 2013 at 2 p.m. EDT.

Please share any thoughts or questions in the comments.

1Informix Warehouse Accelerator,” by Lester Knutsen, IBM Data magazine, July 2011.

2,3 New Informix Warehouse Accelerator 12.10 Benchmarks, Advanced DataTools webcast, October 2013.

[followbutton username='lesterknutsen' count='false' lang='en' theme='light']
[followbutton username='IBMdatamag' count='false' lang='en' theme='light']