Support teams sometimes overlook the requirement to tune SQL statements. I have met support teams who are surprised when I advise them to develop this essential skill.

Maximo builds a lot of its SQL statements on-the-fly and that can lull administrators into a false sense of security where they believe that they don’t need to tune SQL statements. This can also apply to developers who are delivering solutions.

Basic SQL optimisation skills support staff should have

Even basic SQL optimisation techniques can help improve your user’s productivity and increase user satisfaction *. Ultimately it could reduce the number of support calls and allow the support team to work on new features that could significant value.

Support staff should understand:

• The role of the database cache/buffers when executing queries
• How Maximo writes warnings about slow SQL statements
• The process of executing queries on the database including the role of cursors
• How to identify optimisation opportunities
• What a full table scan is and its affect on the database’s cache
• The role of indexes
• The role of statistics

 * I’ll explain how in a later article

The next sections give a very brief overview of the points. Vetasi are able to provide specific training and discuss these points in greater detail.

The role of the database cache/buffers when executing queries

Databases use caches/buffers to store information including query results. A cache could contain rows of data that the user is likely to view in a minute’s time and having the rows in the cache can mean that a user doesn’t have to wait for the data to be retrieved from the comparatively slow hard disk.

Badly tuned queries can lead to the cache being filled with inappropriate rows and forcing other users to wait for their data to be loaded into the cache.

It is important for support staff to be able to understand what SQL is filling the cache and if it is appropriate.

How Maximo writes warnings about slow SQL statements

Maximo logs warnings about SQL statements that Maximo executes. These warnings can indicate a number of problems:

• Badly tuned SQL statements that could be optimised
• Queries affected by non-Maximo SQL statements

These are often very useful and provide important indicators about where to look for the cause of the problems.

The process of executing queries on the database including the role of cursors

There is a series of processes that occur when a query is executed and it is important to understand the factors that influence it.

Each SQL query will be linked to a cursor on the database. If cursors aren’t closed then they can leak memory and eventually lead to serious problems e.g. Maximo JVMs are unable to connect to the database.

 How to identify optimisation opportunities

Staff need to be able to look at a SQL statement and understand how it can be optimised.

Optimisations could be a case of changing the SQL statement, changing the DB configuration or other optimisations.

The role of indexes

Database indexes are similar to the indexes in a book. If data isn’t indexed then the database has to query all the rows in a table in a full table scan.

SQL queries can be modified to use indexes and avoid full table scans.

What a full table scan is and its affect on the database’s cache

A full table scan occurs when the database has to check all the rows in a table to see if it meets part of the where clause. This can dramatically affect the database cache and slow down other queries.

Queries can be tuned to use indexes to avoid the full table scans.

The role of statistics

Statistics are used by database engines to help ensure the database identifies the best way to retrieve the data