Slow Queries with SYNONYMDOMAIN Joins, Part I
bryantsai 2000003CP9 Comment (1) Visits (4430)
Maximo uses synonym domains for the purpose of "constant lookup" or translation, hence it is very common to see queries in Maximo containing multiple joins with table "SYNONYMDOMAIN" (or in the form of "in" or "exist" predicates). Interestingly, it is also common to see expensive queries in Maximo having one or multiple joins between large tables and table "SYN
5! Yes, there are 5 joins in this query, of which 3 are with "SYNOYMDOMAIN" table! It might not be obvious where the 5th join is. Remember "SR" is a view, within which there is one more join between table "TICKET" and "SYN
Lets count the number of joins in this query.
Relational database servers sometimes have performance problems with queries having multiple join operations, especially when table data is not quite evenly distributed. A simple strategy for addressing join performance problems is to reduce the number of joins. In this case, lets attack view "SR" first, since it is relatively easy to modify view definition in a deployed environment without changing any code or any configuration.
Essentially the join with "SYNONYMDOMAIN" here is used to lookup the "value" corresponding to internal maxvalue "SR" of synonym domain "TKCLASS". We could replace the sub-select of "IN" clause with the actual "value" queried from table "SYNONYMDOMAIN" (which is 'SR' in this case). Go to "Database Configuration" and modify object "SR" view where clause from:
This revision produces exactly the same result, except the performance difference from the original:
A 95% reduction in CPU time spent! Just from a simple view rewrite, how easy it is!
So here is a quick solution to try when you have slow queries joining large tables with "SYNONYMDOMAIN". The basic principle here is easy: the fewer joins you have, the more likely database servers are able to find the most optimized access plan. Of course, there are some caveats here. As we know, though you cannot add or delete synonym domains, Maximo does allow adding new "value" for existent synonym domains. When you take this performance tip to manually replace "SYNONYMDOMAIN" joins in views or queries with fixed value, do remember to refresh your changes (in views or queries) whenever you modify (add or remove values) those synonym domain. Otherwise, you might get incorrect results. Fortunately, that rarely happens and is usually only during system development phase.