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 "SYNONYMDOMAIN".
Take the following not-so-efficient query as an example:
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 "SYNONYMDOMAIN".
select * from sr
(status in (select value from synonymdomain where
domainid = 'SRSTATUS' and maxvalue not in 'CLOSED')
and historyflag =0)
((affectedperson=(select personid from maxuser where userid=:L0)
reportedby=(select personid from maxuser where userid=:L0) )
and pmcomtype is null
and status not in (select value from synonymdomain where
maxvalue='DRAFT' and domainid='SRSTATUS'))
for read only
Lets count the number of joins in this query.
create view SR as
... (irrelevant parts omitted here)
where class in (
where domainid='TKCLASS' and maxvalue='SR');
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:
class in (select value from synonymdomain where domainid='TKCLASS' and maxvalue='SR')
This revision produces exactly the same result, except the performance difference from the original:
|new SR view||28.6|
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.