Abstract
This fourth and final article in the Easy IWA series provides a demonstration of accelerating a selection of TPCH queries, based on the TPCH warehouse queries, by IBM Informix Warehouse Accelerator. Building on previous articles, this provides the ability to accelerate a set of SQL queries within a directory against IWA with sub-second timing, demonstrating the speed of query acceleration that can be attained. Provided with this article are the SQL queries and a driver script where a database (or multiple databases) is specified and the SQL queries are run against the specified database with the request to accelerate by IWA
Content
Here, an approach to running a set of SQL queries, derived from the TPCH Warehouse queries, against the two databases, “jj_tpch_internal” and jj_tpch_external”, and their corresponding data marts, “jj_tpch_mart_int” and “jj_tpch_mart_ext”, created in “Easy IWA Part 2 Creating and Loading Data Marts under IBM Informix 12.10.FC4 and IBM Informix Warehouse Accelerator” is detailed.
These SQL queries are attached in a single tar ball “TPCH_queries.tar”.
In order to direct IBM Informix Dynamic Server to accelerate queries by IBM Informix Warehouse Accelerator, a session environment variable “use_dwa” is required to be set. In this example, the value used is “7”, denoting that queries should be requested to be accelerated and should not be allowed to fall back to IBM Informix Dynamic Server for processing. Queries that can be processed in seconds under IWA may take minutes or hours under IDS and consume a vast amount of resources (e.g. temporary dbspace, scanning, processing etc. etc.); in a production environment this is most likely not desired.
With the following script in the same directory where the queries have been extracted to, all SQL queries can be executed against both databases by simply running
Using the script as a basis, simple modifications can be made:
Changing the for db in … to just a single database
Commenting out the read ans to remove the requirement to Hit enter to continue
In order to obtain sub-second timing, available on some Linux distributions, bc and sed are utilised to provide a presentable output.
The table below details the query durations observed against a Data Mart with the X_comment columns removed on the host with the configurations as detailed in these articles
| Query | SF=1 No X_comment | SF=2 No X_comment | SF=4 No X_comment | SF=8 No X_comment | |
|---|---|---|---|---|---|
| Q1 | 2.8 | 4.3 | 12.6 | 23.7 | |
| Q1_mod | 3.0 | 4.6 | 12.8 | 23.2 | See note 1. |
| Q2 | 0.8 | 1.2 | 2.6 | 5.5 | |
| Q5 | 0.5 | 1.0 | 3.2 | 8.3 | |
| Q8 | 0.4 | 0.7 | 2.3 | 4.0 | |
| Q10_mod | 1.4 | 1.9 | 9.4 | 23.3 | See note 2. |
| Q12 | 1.6 | 2.6 | 9.2 | 24.0 | |
| Q14 | 0.2 | 0.4 | 1.2 | 3.4 | |
| Q19 | 26.5 | 50.0 | 118.2 | 242.1 | |
| Q19_rewrite | 0.5 | 1.8 | 2.9 | 6.6 | See note 3. |
- Note 1. Same as Q1 but for all months
- Note 2. Removal of “X_comment” from query as provided by TPCH
- Note 3. Demonstrates that some queries may need manually optimising, although this is an obvious optimisation
It should also be noted that some of the above queries are retrieving several hundreds of thousands of rows and this activity can take longer than the query itself
Conclusion
This article demonstrates an approach to running a set of SQL files against a specified database or databases with the requesting IWA acceleration, providing sub-second timing for each query. This article on TPCH warehouse queries concludes the “Easy IWA” set of articles, which provide a complete guide to instantiation of an IBM Informix Warehouse Accelerator, Creation of Data Marts using the SmartMart approach loading from either standard or external tables and finally running a set of SQL queries against the Data Marts.
Resources
Disclaimer
The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
Contact us
If you have any questions or would like to find out more about TPCH warehouse queries and IBM Informix Warehouse Accelerator, simply contact us.
