If you need to find ways to improve query database retrieval performance, consider the following relatively simple methods. Note that the term "query" is generic in this article unless otherwise stated and can cover any database retrieval tool whether based on SQL or not.

The first three methods are based on ensuring the query uses the best processing engine. There are two query processing engines, the Structured Query Language Query Engine (SQE) and the Classic Query Engine (CQE). The SQE is the engine that IBM is developing strategically and improving with each new release of i OS. The CQE is not being developed by IBM and each new release of i OS since V5R2 has seen more and more queries that used to be processed by the CQE now processed by the SQE.

In most cases, queries run by the SQE will perform better than those run by CQE. While you cannot specifically choose which engine to use (the system does that for you), the ways in which you construct your database and retrieve information from it will influence the system's decision.

The first consideration is to stop using Query/400. Query/400 is not an SQL language-based tool. All queries run using this tool are processed by the CQE. Start using an SQL-based tool such as interactive SQL, Web Query or Query Manager. These SQL-based tools will (barring other impediments such as Select/Omit Logical files; see further on) process the query using the SQE route. Converting from Query/400 to Query Manager is a viable option as there is a prompted interface similar to Query/400 (as well as an SQL interface). There is also a conversion path to help convert existing Query/400 objects.

A second piece of advice is not to reference logical files in SQL statements. RPG programmers beware; don't code the name of a logical file in an SQL statement as you would in an RPG program. Always use the physical file name and let the query optimiser work out the best method (which may or may not be your logical file) to access the data. When you code a logical file name, the SQL statement will always be processed by the CQE even when using an SQL-based tool. Ignore derived indexes.

Okay, you've written an SQL language-based query on a physical file and the CQE is still processing the query. What else do you have to consider to ensure the best engine is processing the query?

A physical file that has logical files with select/omit criteria or derived fields will be processed by the CQE unless the query options file QAQQINI has the 'IGNORE-DERIVED-INDEX' option set to *YES. The job running the query uses the relevant QAQQINI file, either picked up globally in library QUSRSYS or job-specific by identifying it in the CHGQRYA command. With this change, queries will now use the enhanced SQE route rather than the CQE. Without it, queries use the old CQE.

Besides the limitations of a query being optimised by the CQE, initial jobs that get routed to the CQE because of Select/Omit logical files can also see a 10-15% overhead. This is because the jobs have to re-route from SQE to CQE when it finds Select/Omit logical files.

Be aware of your optimisation goal. The easiest way to explain the optimisation goal is to consider the following example. If you had a bicycle and an aeroplane, which method of transport would you use to travel a distance? The best mode of transport is unclear without knowing how far you need to travel (100 yards or 100 miles means a different mode of transport). And so it is with queries. When you query your database, tell the optimiser your optimisation goal, i.e. how many records you will probably want to examine upon retrieval. Is it all records or just a couple of screens' worth? The optimisation goal can be implemented with the SQL clause 'optimise for nn rows'.

You should stop unnecessary IPLs of the system. The database environment relies on many temporary objects and structures to provide good database performance. These are deleted from the system when it is IPLed resulting in them having to be created again (performed automatically by the system as queries are run) following an IPL. This can mean that queries are slow following IPL and speed up as the working week progresses as these temporary objects are created.

Examine your IPL schedule and reduce it, if possible. If not, examine the introduction of a function running within the start-up program that runs the necessary queries that will lead to the creation of the temporary objects.

Glynn Jones is a senior technical consultant for UK-based Chilli IT. He has over 20 years experience on the Power i platform and can be contacted on +44 (0)845 862 3444 or glynn.jones@chilli-it.co.uk for further details on query optimisation.