MBO Performance Tip N.7 – Use efficient SQL

This entry is part of the Java MBO performance optimization golden rules series.

A not well tuned SQL query is often the biggest contributor to elapsed time taken by Maximo Business Objects (MBO) logic.

A general example of this is the use of IN or NOT IN in SQL where clauses. Avoid IN or NOT IN but use EXISTS instead in order to increase SQL performance.

The two examples below will look for employees that are not managers.

SELECT name FROM employee
WHERE id NOT IN (SELECT id FROM managers)

SELECT name FROM employee
WHERE NOT EXISTS
(SELECT 1 FROM managers WHERE managers.id=employee.id)

The second one typically runs much faster than the first.

The use of  the EXISTS statement should generally preferred both in terms of performance and expected functional behavior.

Generally speaking a badly designed SQL query, especially when it runs on large tables, can generate a lot of performance issues in Maximo.